Add identical code to multiple combo boxes

Question

I have a worksheet with 960 combo boxes. I need them to all have the same code attached:

Private Sub ComboBox1_DropButtonClick()
ActiveSheet.Range("a2").Select
End Sub

is there a way to attach this code to each combo box on the sheet automatically without the tedious task of doing it one by one? In case it matters, the reason that I have attached this code is because when the combo box is selected, the hyperlinks and code on the sheet won't work until/unless the user clicks any cell. If there is a Properties setting that takes care of this, then I would rather do that.

If all of the ComboBoxes are `Form Controls` then this is possible. If they are all `ActiveX` controls then this is not possible: http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 So, please update your post and let us know if you used `Form Controls` or `ActiveX` controls. The following post can help you determine if your ComboBoxes are `Form Controls` or `ActiveX` controls: http://stackoverflow.com/questions/38232366/how-to-find-and-select-checkbox-in-excel-using-vba/38234977#38234977 — Ralph, Jul 09 '16 at 13:22
I did use activeX control because of the ability to adjust font size. So I'm out of luck? — Don Desrosiers, Jul 09 '16 at 13:25
How is it done with a form control? Perhaps I will zoom the page and use form controls instead. — Don Desrosiers, Jul 09 '16 at 13:27
Perfect! Just what I'm looking for. I'll try it out in an hour or so. Thank you! — Don Desrosiers, Jul 09 '16 at 13:47
Thanks for the heads up on this Ralph! SOF has been a tremendous help to my and I want this to continue. Pardon my ignorance, but I don't know how to mark answers as correct. I'm sorry. How do I do that? — Don Desrosiers, Jul 09 '16 at 17:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116886/discussion-between-don-desrosiers-and-ralph). — Don Desrosiers, Jul 09 '16 at 18:34

score 1 · Accepted Answer · 2016-11-18T13:57:29.640

1

Create a ComboBox Collection

You'll need

Custom class ComboWrapper to hold a reference to you combobox
- Using WithEvents you'll capture the buttons Click event
Module level collection variable to hold references to the ComboWrapper in memory
- Use the Worksheet_Activate() to instantiate the collection

Insert a class

Rename it ComboWrapper

Insert this code into the ComboWrapper class

Public WithEvents combo As MSForms.ComboBox

Private Sub combo_Change()

    Range("A2").Select

End Sub

Insert this code into the Worksheet Code Module

Public ComboCollection As Collection

Private Sub Worksheet_Activate()
    Dim o As OLEObject
    Dim wrapper As ComboWrapper
    Set ComboCollection = New Collection

    For Each o In ActiveSheet.OLEObjects
        On Error Resume Next

        If o.progID = "Forms.ComboBox.1" Then
            Set wrapper = New ComboWrapper
            Set wrapper.combo = o.Object

            ComboCollection.Add wrapper
        End If

        On Error GoTo 0
    Next

End Sub

edited Nov 18 '16 at 13:57

answered Jul 09 '16 at 15:27

Did you read the requirements and comments? There are 960 *existing* ActiveX ComboBoxes. These are not form controls (MSForms). I don't think that the OP wants to re-create these 960 ComboBoxes just to suit your solution. Furthermore, with form controls your solution wouldn't even be necessary as all these ComboBoxes could share the same sub (no class modules necessary). On a side note: the OP has 960 ComboBoxes and not 960 CommandButtons. – Ralph Jul 09 '16 at 16:04
@Ralph MSForms controls **are** ActiveX. This is a *much* better solution than using code to create hundreds of identical procedures. – Rory Nov 18 '16 at 12:47
@Rory If Form Controls are ActiveX controls then I don't quite understand this post: http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 Furthermore, I specifically posted my solution because Form Controls (on sheets) can share one sub in a module whereas each ActiveX control (on sheets) has their own sub: `Private Sub NameOfActiveXcontrol_Click()`. My solution works with **one** sub for all 960 controls. But surely, I missing something or maybe I don't quite understand what you and Thomas mean. So, please help me understand. – Ralph Nov 18 '16 at 13:02
@Ralph Form controls are not the same as MSForms controls! :) (good old Microsoft) Form controls are part of the Excel application; MSForms controls come from the fm20.dll and are the ones you use on userforms. Thomas' solution puts the actual processing code into a class and then creates an instance of that class for each control on the worksheet. Also, although your solution is one sub, it creates 960 subs, so any maintenance is going to be horrendous. – Rory Nov 18 '16 at 13:06
@Ralph I created a sample file to demonstrate how to use a custom class and a collection to group controls: [Download WrapControls.xlsm](https://www.dropbox.com/s/gviph3pb8ttijih/WrapControls.xlsm?dl=0) – Nov 18 '16 at 15:20
@Rory Thanks. I meant to respond to Ralph's but was busy and forgot about it. I linked him a sample file that will hopefully clear thing up a little. – Nov 18 '16 at 15:21
This works as it is, how would we change the actual combobox that was clicked< like a different interior color for example. – Davesexcel Jul 26 '18 at 12:58

Ralph · Answer 2 · 2016-07-09T18:55:17.537

Since you are using ActiveX controls the name of the sub is preassigned. For example: if you click on the ComboBox1 then the name for the sub must be Private Sub ComboBox1_DropButtonClick() and the sub must be on the sheet where the ComboBox is located. So, if you have 960 ComboBoxes then you need to have 960 subs on the sheet where these ComboBoxes are located.

But here is the good news. You can use VBA to write the VBA code for you. The following sub will go through all sheets and all ActiveX ComboBoxes and write the code for you. Afterwards, the code is dropped into the Immediate window of the VBE.

Option Explicit

Public Sub GenerateComboBoxCode()

Dim ws As Worksheet
Dim obj As OLEObject
Dim strVBA As String

For Each ws In ThisWorkbook.Worksheets
    For Each obj In ws.OLEObjects
        If TypeName(obj.Object) = "ComboBox" Then
            strVBA = strVBA & "Private Sub " & obj.Name & "_DropButtonClick() " & Chr(10)
            strVBA = strVBA & "ActiveSheet.Range(""a2"").Select " & Chr(10)
            strVBA = strVBA & "End Sub " & Chr(10)
        End If
    Next obj
    Debug.Print "------------------------------------------------------"
    Debug.Print "--- Code for sheet " & ws.Name & ":"
    Debug.Print "------------------------------------------------------"
    Debug.Print strVBA
Next ws

End Sub

But in your case (960 ComboBoxes) the Immediate window might not be enough and you might have to store / save the VBA code on a sheet instead.

Update:

Since the Immediate window cannot fit the entire code, here is a little update to the above solution:

Option Explicit

Public Sub GenerateComboBoxCode()

Dim ws As Worksheet
Dim obj As OLEObject
Dim strVBA As String
Dim appWord As Object
Dim docWord As Object

For Each ws In ThisWorkbook.Worksheets
    strVBA = strVBA & "------------------------------------------------------" & Chr(10)
    strVBA = strVBA & "--- Code for sheet " & ws.Name & ":" & Chr(10)
    strVBA = strVBA & "------------------------------------------------------" & Chr(10)
    For Each obj In ws.OLEObjects
        If TypeName(obj.Object) = "ComboBox" Then
            strVBA = strVBA & "Private Sub " & obj.Name & "_DropButtonClick() " & Chr(10)
            strVBA = strVBA & "ActiveSheet.Range(""a2"").Select " & Chr(10)
            strVBA = strVBA & "End Sub " & Chr(10)
        End If
    Next obj
Next ws

Set appWord = CreateObject("Word.Application")
Set docWord = appWord.Documents.Add
docWord.Paragraphs.Add
docWord.Paragraphs(docWord.Paragraphs.Count).Range.Text = strVBA
appWord.Visible = True

End Sub

Now, a new Word Document is created and the entire code is copied into that word document. Afterwards, you can copy all of the document's content and paste it over into the sheet where the ComboBoxes reside.

this worked perfectly and is exactly what I'm looking for. However, 2 important issues remain. why did it only write code for 36 boxes? and why did it make 2 of every SUB? I have deleted all of the ambiguous SUBs but now I need it to complete the job. I know that you referenced this issue when you commented about 960 boxes, but I don't understand the workaround. — Don Desrosiers, Jul 09 '16 at 16:57

Add identical code to multiple combo boxes

2 Answers2

Create a ComboBox Collection

Update:

Linked