pass variable to macro sub routine

Is there a way to pass a variable to a macro sub?

Basically, I have 5 rows of 7 checkboxes that each create/copy worksheets and/or delete worksheets. I would assign the same macro to them if I could pass the name of the checkbox with the sub call. Otherwise, I will have to write a sub routine for eash checkbox.

Thanks.

Edit:
Is there a way to capture when a checkbox is changed? Other then assigning a macro?

2nd Edit:
I found this: http://www.dailydoseofexcel.com/archives/2004/06/03/passing-arguments-through-onaction/

Excel 2007 gave me errors on the sytax. So I changed the code to the following:

Dim sCheckBox As String
sCheckBox = "chk_log1"
Sheets("Setup").CheckBoxes(sCheckBox).OnAction = "'" & "Setup_Checkboxs_onclick" & "(" & sCheckBox & ")" & "'"



Which does not work. It does call the sub routine but the variable is null.

Hi sonic,

yes, this can most assuredly be done. But you need to explain a little better:

Are your controls on a form or on a worksheet?
If they're on a worksheet: Are you using Excel (from the "Forms" toolbar) or ActiveX (from the "Controls Toolbox") controls?
Would you mind switching to the other kind if that made things easier?

They are form controls (not activeX) on a worksheet.
I dont care what I have to do or change to make it work. Honestly, I could have already code each checkbox by now. But I want to simplify the code for changes. Using a loop in one function for all the checkboxes will be easier to modify then 40 different functions.

It seems like the OnAction is the way to go. I just cant seem to get teh syntax right.

Thanks

Well, you are about to reply to my thread but I figured out the syntax.

'Name_Of_Function "Variable"'

Single ' quote must be on both ends.

Then a space

The the variable, double qoutes are not required for Integers, just strings.

Finished Code:

Sheets("Setup").CheckBoxes("chk_log1").OnAction = "'Setup_Checkboxs_onclick " & Chr(34) & sCheckBox & Chr(34) & "'"


Site that gave me the answer: http://www.tushar-mehta.com/excel/vba/xl%20objects%20and%20procedures%20with%20arguments.htm


I am still interested in doing it a different way, if it makes sense to do so.

Edit:
One could combine variables into one string then delimit in the function.

Yeah, that's what I just figured out too. Didn't know that one could do that... :cool:
Public Sub test1()
Dim cb As Excel.CheckBox
For Each cb In SheetCB.CheckBoxes
cb.OnAction = "'test2 """ & cb.Name & """'"
Next
End Sub

Public Sub test2(strCB As String)
Dim cb As Excel.CheckBox
Set cb = SheetCB.CheckBoxes(strCB)
'cb now refers to the checkbox object that was clicked on
End Sub

For more convenient coding, I recommend switching on "Show Hidden Members" in the object browser (F2->context menu), that'll expose the Excel.Checkbox type and the Sheet.Checkboxes collection to intellisense.

I'll put together a demonstration of an OO-way of doing this in a minute, since you're interested. :)

Okay, here goes the OO-solution for ActiveX controls:

The basic concept behind all of the below is that of "wrapper classes". What we want to do is give the checkbox the ability to use a particular procedure to handle its click event, and to identify itself to that procedure. It can't do that by itself, and we can't change it directly, but what we can do is wrap it into an object that has this ability. Like, say, a CD by itself isn't going to produce any sound, but if we "wrap" it into a CD player, we can add that functionality to it. Wow, clumsy example... :p

Excel uses wrappers itself, for instance, all ActiveX controls on a worksheet are wrapped into an "OLEObject" layer which we can be accessed through the sheet's OLEObjects collection.

The proper way to handle this, in my view, is to wrap both the control and the sheet, and give the control wrapper the ability to inform the sheet wrapper that an event occured. This is known as "callback". If we can write code for an object, the best way of wrapping is to provide an interface, like so:

Class ISheetCB (needs to be set to "PublicNotCreatable" in the properties window)
Public Sub cbClick(whichCB As MSForms.CheckBox)
End Sub

Worksheet
Implements ISheetCB

Private Sub ISheetCB_cbClick(whichCB As MSForms.CheckBox)
'"callback" function - whichCB is the checkbox that was clicked on
End Sub

This may look a little scary if you haven't used interfaces before, but it's really quite straightforward. All it does is tell the sheet to add to its functionality (to "implement") the wrapper called ISheetCB, which here is nothing more than to provide the callback function.

In the case of the control, we can't do it like this, because we have no code module for the control to put the "Implements" keyword into. So we need to create a separate object to represent the wrapping layer. This is done by a normal class module which contains code like this:

Class CBWrapper
Private WithEvents myCB As MSForms.CheckBox
Private mySheet As ISheetCB

Public Property Set wCB(whichCB As MSForms.CheckBox)
Set myCB = whichCB
End Property
Public Property Set wSheet(whichSheet As ISheetCB)
Set mySheet = whichSheet
End Property

Private Sub myCB_Click()
Call mySheet.cbClick(myCB)
End Sub

The class contains two object references. The first is to the wrapped control and is declared "WithEvents", so that the class can react to user input. The second is to the sheet's wrapper, so that we can forward the click event to its callback function which we've just provided. Both of these can be set from the outside, so that the wrapper can be utilized.

This concludes the framework we need. To recap - the user clicks on the control, the control raises the event in the wrapper class, the wrapper class passes it on to the sheet's wrapper, along with a reference to the control (the last part is the whole point of the exercise).

What's left to do is to contruct the wrappers. That's done by looping over the checkboxes on the sheet, creating a wrapper for each and sticking the wrappers in a collection:

Worksheet
Private myCBs As Collection

Public Sub wrapCBs()

Set myCBs = New Collection

Dim thisOLEWrapper As OLEObject, thisCB As MSForms.CheckBox, thisCBWrapper As CBWrapper
For Each thisOLEWrapper In Me.OLEObjects 'loop over all objects in the sheet
If TypeOf thisOLEWrapper.Object Is MSForms.CheckBox Then 'if it's a checkbox,
Set thisCBWrapper = New CBWrapper 'create a new wrapper,
Set thisCBWrapper.wCB = thisOLEWrapper.Object 'wrap the checkbox in it,
Set thisCBWrapper.wSheet = Me 'give it a reference to the parent for "callback",
Call myCBs.Add(thisCBWrapper) 'and store it in the wrapper collection.
End If
Next

End Sub


Once you run that procedure once, everything is in place. All that's left is to put some code into the callback function. I used this for testing:
whichCB.BackColor = RGB(Int(256 * Rnd), Int(256 * Rnd), Int(256 * Rnd))



This is obviously much more complex than the approach using macros and control names, but it's also much more flexible and powerful.

I hope I explained that allright, let me know if any particular part was unclear. :)

Wow, that's pretty advanced. Although it all seems to make sense. I think what I am gonna do is complete this current workbook and look into rebuilding that part. Mainly becuase I really want to get into using wrappers. I have read about them, in vb books and forums, but have always worked around learning it.
Good thing is that I am very close to gettin this current workbook, working. I will come back to this in a couple days.


Thanks

btw,
extra thanks for the detailed explaination.

Wow, that's pretty advanced.
Yeah, it's about as advanced as object-oriented architecture gets in VB. This is a nice example, I think, because it contains lots of different aspects and naturally combines them.

The nice thing about VB's approach to OO is that, even when the concepts get somewhat advanced, the syntax is still pretty straightforward once one gets used to it. No deciphering pointer de-referencing or template contructs like in C++. :)

There's some very nice work going on here! :cool:

Just to expand on the technique you both alluded to in posts #4 and #5...

If you work with forms controls, instead of activeX controls, you can simplify it even further.

You could have a communal routine such as:

Public Sub Foo(ByVal SomeName As String)
MsgBox SomeName
End Sub


Then when you add each form control to the worksheet you can right click---> assign macro, and in the Macro name box you would put:

'Whatever Workbook.xls'!'Foo "Textbox1"'


So you're hard-typing the argument (in this case the control's name) you pass into the communal procedure for each control. It's not very flexible, but it is extremely simple - the only code you have is the communal procedure itself!

As for working with hidden members - well I think that subject's a bit of a can of worms and there's always going to be a difference of opinion! It's cleaner code for sure and if this is a home project then I think it's fine. It's always worth bearing in mind that they're hidden for a reason - they're only included for backward compatability - so they should be used with a degree of caution. How much caution? Well, nobody really knows what lies ahead and, IMHO, I think these ones are probably safe enough. But, if I'm writing code for work then I do leave hidden members hidden. That's just my decision and you are, of course, totally free to do what you like! :)

'Whatever Workbook.xls'!'Foo "Textbox1"'
Ah, thanks for implicitly correcting the code we used before - there should of course be no brackets around the argument, per usualy VB Sub syntax. :o
It's always worth bearing in mind that they're hidden for a reason - they're only included for backward compatability - so they should be used with a degree of caution.
Yes, good thing you mention that. I actually meant to include a disclaimer when i suggested turning them on above, and to also suggest to turn them back off afterwards, but I seem to have forgotten about that when it came to posting. Thanks! :)