Passing Parameters from Form Controls

[Jamesm601]

Hi All.

Does anyone know if it's possible to pass a parameter to a macro within the assignment to a form control? (button, checkbox, etc.) When I try, Excel tells me the macro is "too complex to be assigned to an object".

This would sure help me trim off some file-bloat in a project I have. Right now I have 30 checkboxes with a seperate calling procedure for each. If I could pass a simple parameter from the control, I'd need only one macro.

Thanks in advance.

 

[ETID]

Assuming your talking access

Are your check boxes in a frame?

 

[Jamesm601]

I'm actually working with Excel. These are checkboxes from the forms toolbar. I use Excel so much, sometimes I forget that VBA works with other applications. Sorry for the lack of detail.

Thanks.

 

[fumei]

I think ETID was asking about frames under the assumption these check boxes are on a UserForm. When checkboxes are in a frame only one choice can be selected. The frame can only return one boolean result. Suppose it has three checkboxes, if Check1 = True then Check2 and Check3 have to be false.

If you are using the checkboxes from the forms toolbar then each one is an independent object with its own result.

Depending on your logic requirements for your 30 checkboxes, it may be better to use a UserForm rather than the forms toolbar.


Gerry

 

[Jamesm601]

Fumei
Yeah. That's always a better approach. Unfortunately, this particular project won't allow it because I'm refining an existing tool that's based on direct interaction with the spreadsheet. I had to stick to that approach because people are used to it.

So I gather that what I was hoping to do in terms of passing parameters from checkboxes located directly on the spreadsheet is not doable. I'm not surprised. I just hoped maybe someone had worked it out.

Much thanks for the input.

James.

 

[TonyJollans]

Hi James,

Yes, you can do what you want, but not with a parameter.

Assign the same macro to each of your Checkboxes, and in that macro you can check Application.Caller to see which check box has triggered the call.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.