Passing variables in Macro calls?

[sbozicevic]

Hi there,

I have a number of buttons that I am using to show/hide columns. The macro does the exact same thing every time, so I'd like to call it from every button instead of having each button bound to its own macro.

The challenge I am having is to pass a variable which defines the column I am trying to show/hide in the macro call.

My macro is called: ShowHideColumn

When I assign the macro behavir to the button, I am trying to achieve this: ShowHideColumn('OrderColumn')

So that in the macro, i can use the variable to show / hide the proper column.

When I try to pass the ('0rderColumn') string into the macro, it gives me an error message that says: "Formula is too complex to be assigned to the object"

Any suggestions?

 

[RoryA]

If order column is a string, then in the Assign Macro dialog, type:
'ShowHideColumn "C"'
for example to pass 'C' to the macro.

 

[sbozicevic]

Got it! Nice thanks!

How about this one?

i'd like to change the text on the button when clicked. So it starts off by saying "HIDE COLUMN X" and when clicked it says "SHOW COLUMN X"

I'm thinking i need to do something like: 'ShowHideColumns "columnName" this'

and then my sub would be named: ShowHideColumns (theColumn, buttonObject)


is this doable and how would i access the button object?

 

[Legacy 98055]

I'm fairly certain that you cannot pass an object reference as a parameter. You may however, use Application.Caller to determine which button was clicked relative to forms buttons/shapes...

Dim s As Shape
Set s = ActiveSheet.Shapes(Application.Caller)
MsgBox "You just clicked a button named " & s.Name