Assigning a variable to a userform object

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,202
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,212
Office Version
  1. 365
Platform
  1. Windows
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
4,202
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,217
Members
430,116
Latest member
d1pakjha

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