I'm using a class to get the name of the combobox for which the drop down was clicked. I then want to fill the combobox from an SQL query.
Part of that process needs me to pass the combobox name as a combobox and not a string.
This is what I've tried:
Code
Private Sub Combo_DropButtonClick()
Dim Number As String
Dim ColNumb As Long
Dim contName As String
' put whatever code you like in here
'MsgBox "You changed combo " & Combo.Name
'Find ComboRow
Dim rw As Long
Dim CboRw As Long
If VBA.InStr(Combo.Name, "Container") > 0 Then
CboRw = 33
ElseIf VBA.InStr(Combo.Name, "Closure") > 0 Then
CboRw = 34
ElseIf VBA.InStr(Combo.Name, "Formulation") > 0 Then
CboRw = 35
ElseIf VBA.InStr(Combo.Name, "Countries") > 0 Then
CboRw = 47
End If
Number = VBA.Right(Combo.Name, 2)
If VBA.InStr(Number, "_") Then
ColNumb = VBA.Right(Number, VBA.Len(Number) - 1)
Else
ColNumb = Number
End If
'Load combobox list
If (CboRw = 33) Then
'Container
'Will have the row and column numbers now. Address (CboRw, ColNumb)
Call modDataValidation.Container(CboRw, R1, ColNumb)
ElseIf (CboRw = 34) Then
'Closure
ElseIf (CboRw = 35) Then
'Formulation
ElseIf (CboRw = 47) Then
'Countries
End If
End Sub
'this is the code from the modDataValidation.Container section. I'm trying to get the combobox name so I can fill that dropdown box.
Public Sub Container(rw As Long, ws As Worksheet, col As Long)
Dim SQL As String
Dim C As ComboBox
Dim cc As String
cc = ws + "cboComboBox_" + ws + " _ " + rw
C = ws.Controls(cc).Name
SQL = ""
SQL = SQL & "SELECT DISTINCT MATERIAL_COMPONENT, MATERIAL_DESCRIPTION_COMPONENT"
SQL = SQL & " FROM dbo.BomInfo"
SQL = SQL & " WHERE(MATERIAL_GROUP_COMPONENT LIKE 'A%') OR (MATERIAL_GROUP_COMPONENT LIKE 'N%')"
SQL = SQL & " ORDER BY MATERIAL_COMPONENT"
Call modDataValidation.GetDataFromSQL(rw, SQL, 33, ws, C) 'This will run the query and fill the combobox.
End Sub
If this is not possible can you suggest another way to solve filling the correct combobox?
Display More