How to show one month later after recent month based on selection from listbox

[Mohammad Ishaq]

Hi,I have a userform with a listbox and textbox.I have also some data on sheet one.My question is that when i click on any item on listbox,the textbox show one month later of recent month.For Example if i select the name AA,then the most recent month in the second column is August-20,but i want to show September-20 in the textbos.I have attached an image and the file for better understandin. Thanks for your nice support

 

[Chihiro]

Something like below in your UserForm1 code.

Private Sub ListBox1_Change()
With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
            TextBox1.Value = StrMonth(.List(i))
        End If
    Next
End With
End Sub

Function StrMonth(lName As String) As String
m = 0
With Sheets("Sheet1")
    tmp = .Range("H8:I" & .Cells(Rows.Count, "H").End(xlUp).Row).Value
    For i = 1 To UBound(tmp)
        If tmp(i, 1) = lName Then
            m = IIf(m < tmp(i, 2), tmp(i, 2), m)
        End If
    Next
End With
StrMonth = Format(DateAdd("m", 1, m), "mmmm-yy")
End Function

 

[Mohammad Ishaq]

Thanks Dear,Its ok now.