How do I make a combobox class and an Optionbutton Class?

jrdnoland · Oct 25th 2020

I need to make it known when a combobox or optionbutton was clicked. This can be in two separate classes. What I have tried is this:

Code

Dim arrEvents As Collection
Private Sub Worksheet_Activate()
Dim objButtonEvents As ButtonEvents
Dim objButtEvents As ButtonEvents
Dim shp As Shape
Set arrEvents = New Collection
Set arr2Events = New Collection
For Each shpCursor In Me.Shapes
If shpCursor.Type = msoOLEControlObject Then
If TypeOf shpCursor.OLEFormat.Object.Object Is MSForms.CommandButton Then
Set objButtonEvents = New ButtonEvents
Set objButtonEvents.cmdButton = shpCursor.OLEFormat.Object.Object
arrEvents.Add objButtonEvents
End If
End If
If shpCursor.Type = msoOLEControlObject Then
If TypeOf shpCursor.OLEFormat.Object.Object Is MSForms.ComboBox Then
Set objButtEvents = New ButtonEvents
'Set objButtEvents.cboButton = shpCursor.OLEFormat.Object.Object
arr2Events.Add objButtEvents
End If
End If
Next
End Sub
The above is in a worksheet change section.
In the class module I have:
CODE
Public WithEvents cmdButton As MSForms.CommandButton
Public WithEvents cboButton As MSForms.ComboBox
Private Sub cmdButton_Click()
MsgBox cmdButton.Caption & " was pressed!"
End Sub
Private Sub cboButton_Click()
MsgBox cboButton.Name & " was pressed!"
End Sub
/CODE
What am I missing? Complete code examples will be appreciated!
Sorry I couldn't figure out how to add two code pieces to my question.

Display More

**royUK** · Oct 25th 2020

I have moved your question to the correct Forum.

See if this example helps.

jrdnoland · Oct 26th 2020

That sort of works. What I'm seeing is that the classes are able to give me the name of the control, but it appears to loose the "Hooks" and I have to rerun the hook before I can click on a combobox that is in a cell. I used activex comboboxes mostly just becasue I could set the font to bold. I can't do the with combo form control.

Code

Option Explicit
Public WithEvents Combo As MSForms.ComboBox
Private Sub Combo_DropButtonClick()
' put whatever code you like in here
'MsgBox "You changed combo " & Combo.Name
'Possible Comboboxes
'cboContainer_R1_row#
'cboClosure_R1_row#
'cboFormulation_R1_row#
'cboCountries_R1_row#
'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
Dim Number As String
Dim ColNumb As Long
Dim contName As String
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
With this I can get the row number and the column number of the combobox that the drop down was clicked on.
I have another issue, with this code, for which I'll make a separate post for.

Display More

**royUK** · Oct 26th 2020

Forms Controls are the most reliable

jrdnoland · Oct 26th 2020

Quote from royUK

Forms Controls are the most reliable

RoyUK - I need an activeX control because of the font and the backcolor.

Any idea what is causing the comboboxes to become "unhooked"?

**royUK** · Oct 26th 2020

I don't have any issues in the sample workbook. I can click multiple combos without any issues.

jrdnoland · Oct 27th 2020

Quote from royUK

I don't have any issues in the sample workbook. I can click multiple combos without any issues.

Thanks for looking. Perhaps this is happening due to interference from my other code. My workbook is using ado and sql server queries.

**royUK** · Oct 27th 2020

Does the example workbook of mine work OK?

jrdnoland · Oct 27th 2020

Yes, it seems to. I could attach my workbook, but I don't want it made public. You wouldn't have the databases but I could make it work with a list page just to show you.

**royUK** · Oct 27th 2020

If my exampe works the in your workbook comment out the SQL code and ADO code then see if it works.

jrdnoland · Oct 27th 2020

I commented out the database calls and the classes did seem not to fail as they did before. Guess I'll try adding back the database calls one step at a time and see what's causing it to fail.

Appreciate you help and insight!

**royUK** · Oct 28th 2020

Hope you get it sorted. Let u know.

How do I make a combobox class and an Optionbutton Class?

royUK Oct 25th 2020

Share

Tags