Populate a ComboBox in my UserForm - code?

[Mel Smith]

I'm trying to use some code from an inherited workbook but I cannot get the ComboBox to populate with a DropDown list. Where is my code wrong, please?

Private Sub ComboBox1_Change()
Dim ans As String
ans = ComboBox1.Value
Dim dteRow As Variant
Dim i As Long
Dim nn As Long
Dim Del As Variant
Dim sn As Variant
'these are sheet numbers
sn = Array(1, 2, 3)
' these are the column numbers shown below in the array
Del = Array(3, 4, 16, 6, 7, 8, 9, 10, 11, 12, 13, 14, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)
nn = 1
For b = 1 To 3
With Sheets(sn(b - 1))
dteRow = Application.Match(ans, .Columns(1), 0)
If IsNumeric(dteRow) Then

For i = 1 To 34
Me.Controls("TextBox" & nn).Value = .Cells(dteRow, Del(i - 1))
nn = nn + 1
Next
Else
MsgBox ans & vbNewLine & "Not Found"
End If
End With
i = 1
Next

End Sub

Private Sub Userform1_Initialize()
ComboBox1.List = Sheets(1).Range("B4:B63").Value
End Sub


Thanks for your help.

Mel

 

[dmt32]

Looks like you have made the classic error or renaming the Intialize Event

Private Sub Userform1_Initialize()
ComboBox1.List = Sheets(1).Range("B4:B63").Value
End Sub

Regardless of your forms name, it is always Userform_Initialize You must not rename the event

Private Sub Userform_Initialize()
ComboBox1.List = Sheets(1).Range("B4:B63").Value
End Sub

Hope Helpful

Dave

 

[Mel Smith]

hi dave,

will this code be the same for all three userforms within this workbook?

 

[Mel Smith]

Hi Dave,

I seem to have run into another issue now. Whilst the dropdown list is still not populating, when I click on the Commandbutton on the worksheet to open the UserForm I now get a run time error '70' and access denied?

Mel

 

[dmt32]

hi dave,

will this code be the same for all three userforms within this workbook?

It applies to all userforms - no matter what you decide to name them, you do not rename any of the events called Userform.

examples

Private Sub UserForm_Activate()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Deactivate()

End Sub

Private Sub UserForm_Initialize()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

End Sub

Dave

 

[dmt32]

Hi Dave,

I seem to have run into another issue now. Whilst the dropdown list is still not populating, when I click on the Commandbutton on the worksheet to open the UserForm I now get a run time error '70' and access denied?

Mel

For your combobox issue - Check code is pointing at the correct worksheet & range

If still having issues suggest you publish all your forms code or better, place copy of you workbook in a dropbox & provide link to it - plenty here to assist

Dave