Populate a ComboBox in my UserForm - code?

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
774
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,175
Office Version
  1. 2019
Platform
  1. Windows
Looks like you have made the classic error or renaming the Intialize Event

Rich (BB code):
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

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

Hope Helpful

Dave
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
774
Office Version
  1. 365
Platform
  1. Windows
hi dave,

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

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
774
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,175
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,175
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,490
Members
410,686
Latest member
Fer9us
Top