Activecontrol vba

Question

Hello,

i am trying to do a vba project that includes about 20 different comboboxes. I want to set it up where if the box changes then it will format the value to time. But i also would like to try and get the code to be generic as possible so that it is easy to copy and paste for to the comboboxes. I set it up where it would pull the name of the active control and that was working. However if i use a command button to change the comboboxes the activecontrol is the button and not the box. Is there a way to pull what the object an event procedure uses or get the name of the current procedure?

Answer 1

It is the same in a Userform, just the init loop is different, see code below.

Andreas.

Option Explicit

Option Compare Text

Dim MyEvents As New Collection

Private Sub UserForm_Initialize()

  Dim C As MSForms.Control

  Dim MC As MyClass

  'Visit all objects

  For Each C In Me.Controls

    'Is it a combobox?

    If TypeOf C Is MSForms.ComboBox Then

      'Create our class

      Set MC = New MyClass

      'Assign the box

      Set MC.MyComboBox = C

      'Keep it alive

      MyEvents.Add MC

    End If

  Next

End Sub

Answer 2

Thank you for that. Unfortunately that wont work the way im trying to do it. Below is the code for when the checkboxes are changed. This is actually part of the userform. I am trying to get it so if a schedule is needed to change then it fills the right combobox. Everything works except when i use a button to change the value. After clicking the button the activecontrol.name is going to be the button and not the checkbox. The way this is set up is that if i make any changes to one check box it will easily be copied and pasted without trying to change the different variables for each box. Is there a way to get this code to run like it is below but it pulls the checkbox that it is under. 

Private sub checkbox1_change()

dim ctrl as string

ctrl = activecontrol.name *this part doesnt work when using multiple controls to change the one control*

dim db as integer

db = right(ctrl,1)

Dim combo as string

dim cv as boolean

if me.controls(ctrl).value = true then

     Cv = true

     Combo1 = combo & (dn*3-2)

     Me.controls(combo1).enabled = cv

     Combo2 = combo & (dn*3-1)

     Me.controls(combo2).enabled = cv

     Combo3 = combo & (dn*3)

     Me.controls(combo3).enabled = cv

else

     Cv = true

     Combo1 = combo & (dn*3-2)

     Me.controls(combo1).enabled = cv

     Combo2 = combo & (dn*3-1)

     Me.controls(combo2).enabled = cv

     Combo3 = combo & (dn*3)

     Me.controls(combo3).enabled = cv

End if

end sub

Answer 3

Yes, we can take over the events in a class module.

Create a new class module, name it MyClass and paste in this code:

[code]

Option Explicit

Public WithEvents MyComboBox As MSForms.ComboBox

Private Sub MyComboBox_Change()

  MsgBox "Hello World"

End Sub

[/code]

Create a new module and paste in this code:

[code]

Option Explicit

Option Compare Text

Dim MyEvents As New Collection

Sub Test()

  Dim Ws As Worksheet

  Dim MC As MyClass

  Dim OO As OLEObject

  'Visit all sheets

  For Each Ws In Worksheets

    'Visit all objects

    For Each OO In Ws.OLEObjects

      'Is it a combobox?

      If OO.progID = "Forms.ComboBox.1" Then

        'Create our class

        Set MC = New MyClass

        'Assign the box

        Set MC.MyComboBox = OO.Object

        'Keep it alive

        MyEvents.Add MC

      End If

    Next

  Next

End Sub

[/code]

Run sub Test and change one of your combo boxes.

Andreas.