Snasui Excel Forum

Code: Select all

Private Sub NameMTD_Change()


Set sh = ThisWorkbook.Sheets("DEP")

Dim N As Long, lastrow As Long
lastrow = Sheets("DEP").Range("A" & Rows.Count).End(xlUp).Row
For N = 2 To lastrow
If Sheets("DEP").Cells(N, "A").Value = (Me.NameMTD) Or Sheets("DEP").Cells(N, "A").Value = Val(Me.NameMTD) Then
Me.listmtd2 = Sheets("DEP").Cells(N, "B").Value
Me.listmtd3 = Sheets("DEP").Cells(N, "C").Value
Me.listmtd4 = Sheets("DEP").Cells(N, "D").Value
Me.listmtd5 = Sheets("DEP").Cells(N, "E").Value
Me.listmtd6 = Sheets("DEP").Cells(N, "F").Value
Me.listmtd7 = Sheets("DEP").Cells(N, "G").Value
Me.listmtd8 = Sheets("DEP").Cells(N, "H").Value
Me.listmtd9 = Sheets("DEP").Cells(N, "I").Value
Me.listmtd10 = Sheets("DEP").Cells(N, "J").Value
Me.listmtd11 = Sheets("DEP").Cells(N, "K").Value
Me.listmtd12 = Sheets("DEP").Cells(N, "M").Value
Me.listmtd13 = Sheets("DEP").Cells(N, "P").Value
Me.listmtd14 = Sheets("DEP").Cells(N, "Q").Value
Me.listmtd15 = Sheets("DEP").Cells(N, "R").Value
Me.listmtd16 = Sheets("DEP").Cells(N, "S").Value


End If
Next N


   End Sub

Code: Select all

Private Sub NameMTD_DropButtonClick()

Set sh = ThisWorkbook.Sheets("DEP")
Dim N As Long, lastrow As Long
lastrow = Sheets("DEP").Range("A" & Rows.Count).End(xlUp).Row
If Me.NameMTD.ListCount = 0 Then
For N = 6 To lastrow
Me.NameMTD.AddItem Sheets("DEP").Cells(N, "A").Value
Next N
End If


End Sub