Assigning a variable to a userform object

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am not all that familiar with Excel VBA, so my apologies in advance if I don't know how to phrase my question properly.

I have a userform with 15 similar comboboxes. Each one has a change event very similar to each other in that it call a procedure. The procedure shares common instructions to reset the value of the combobox which originally called the procedure in the first place.

Right now, I am using 20 if / elseif's to direct the changes to the appropriate combobox.

What I was hoping, was to create a public variable, and in the combobox's change procedure, assign that variable to that combobox.

Code:
Private Sub cb_selection1_Change()
    eg cb_out = me.cb_selection1
    call external procedure in general module
End sub

With that, in my procedure ...

Code:
if blahblahblah = suchand such then
   with cb_out
         .value="this"
         .backcolor = thiscolor
         etc
   end with
end if
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Instead of assigning the combobox to a public variable, I'd suggest you pass the combobox itself to your procedure, as follows...

Code:
Private Sub cb_selection1_Change()
    Call Macro1(Me.cb_selection1)
End Sub

Code:
Sub Macro1(cb_out As ComboBox)
    MsgBox cb_out.Name
End Sub

Alternatively, you can use a class module to handle those 15 comboboxes with one event handler.

Hope this helps!
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Domenic ...thank you.
I will give it a try, play around with it a bit and see what kind of success I have.
Keep an eye on this thread, I may have to come back for some more education, or to give praise.

Or both.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top