Hi again.

Let me first say that i appreciate everyone who has been kind enough to
respond to my inquiries/questions due to my "ignorance" of VBA/excel.

Another question is this: I have created a form control on a worksheet and
also defined the macro(public sub subName) in the same worksheet module and
subsequently assigned that public macro to the control. However, i don't
want users to be able to select this macro within Excel (via
Tools-Macro-Macros).

If i declare the macro as private (private sub subnam) that prevents user's
from choosing the macro. BUT that also prevents me from assigning the macro
to the form control as well since i used Right-Click on Form Control-Assign
Macro method!

So how can i assign a macro to a control without also letting the user gain
access to the macro via Tools-Macro-Macros?

Hi Bing,


If i declare the macro as private (private sub subnam) that prevents
user's
from choosing the macro. BUT that also prevents me from assigning the
macro
to the form control as well since i used Right-Click on Form
Control-Assign
Macro method!

If you declare the macro as private, although you will not see the macro
when you right-click the control, you can still assign the macro by typing
the name, e.g.:

Book1!Sheet3.AAC

Alternatively, assign the macro to the control in your usual fashion and
then alter the declaration to private.

---
Regards,
Norman



"Bing" wrote in message
...
Hi again.

Let me first say that i appreciate everyone who has been kind enough to
respond to my inquiries/questions due to my "ignorance" of VBA/excel.

Another question is this: I have created a form control on a worksheet and
also defined the macro(public sub subName) in the same worksheet module
and
subsequently assigned that public macro to the control. However, i don't
want users to be able to select this macro within Excel (via
Tools-Macro-Macros).

If i declare the macro as private (private sub subnam) that prevents
user's
from choosing the macro. BUT that also prevents me from assigning the
macro
to the form control as well since i used Right-Click on Form
Control-Assign
Macro method!

So how can i assign a macro to a control without also letting the user
gain
access to the macro via Tools-Macro-Macros?