Calling a Private sub from another Module 3

[DaleWatson123321]

Hi guys,

Calling a Private sub from another Module is probably covered in VBA "101". Unfortunately, because I've never taken any formal VBA training and Microsoft's help isn't cooperating, I can use your help.

I have a Private routine attached to a ComboBox_Change event (on a particular sheet), and this routine calls another routine in a Module.

What must I do in order to be able to call a Private routine in a separate Module ???

Your help will be appreciated.

...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Does this refer to the sub you are trying to call from the combo_change routine ??

Do you mean by private that the sub you are trying to call is along the lines of
Private Sub CallMeIfYouCan() ????
or that it is just in a different module ??

If it is defined as Private Sub .....() then I don't think you can call it from another module as the Private part specifically specifies that the sub can only be called from another sub in the same module... Rgds
~Geoff~

 

[RobBroekhuis]

You can define userform event handlers as public subs, even though they default to private. I'm not sure if that addresses your issue, though.
Rob

 

[DaleWatson123321]

~Geoff~

Thanks for helping.

To answer your questions...

1) Does this refer to the sub you are trying to call from the combo_change routine ?? ..."Yes"

2) Do you mean by private that the sub you are trying to call is along the lines of Private Sub CallMeIfYouCan() ???? ..."Yes"


3) or that it is just in a different module ??
...and it's in (separate) Module. I actually only have the ONE Module. The "separate" means separate from the ComboBox_change routine on the sheet.

It seems like the combo_change routine "has" to be on the sheet where the ComboBox is positioned ???

The routine I want to call is a rather "lengthy" routine (with numerous subroutines) - AND it's used by other ComboBoxes on other sheets, where those ComboBoxes don't have a change_event attached.

I hope this helps clarify my situation, and that you can offer a "workaround". Or if not, just confirmation that it's not possible is something I might have to accept.

I appreciate I can password protect the code. However, my objective in making the routines &quot;Private&quot; was so that they don't show up when the end-user uses <Alt> <F8>. I have a couple of routines that I &quot;do&quot; want the user to see in the Macro window - because I have assigned keyboard shortcuts to them. I was hoping I would be able to display &quot;only&quot; those two.

Thanks for taking a look at this, Geoff.

Regards, ...Dale

 

[DaleWatson123321]

Hi Rob,

Thanks for jumping in.

I don't have any userforms, so I can't see how your suggestion would help.

Do my responses to Geoff &quot;trigger&quot; more clarity as to my situation, and perhaps other ideas ???

Thanks !!! ...Dale

 

[RobBroekhuis]

There are other ways to prevent the public macros from showing up on the user interface. One that works is

public sub MySub(optional dummy)

which, because it has a parameter, doesn't show up. But you're perfectly allowed to leave off the parameter in your VBA calls.
Rob

 

[rmikesmith]

Dale,

At the beginning of the standard code module that contains your called procedure (the one you wish to be &quot;private&quot, add the line

Option Private Module

but do not declare the called procedure as Private; just use Sub.

Regards,
Mike

 

[RobBroekhuis]

A correction to my previous post: for the sub to not show up on the alt-F8 list, you MUST define the type of the parameter:

public sub MySub(optional dummy as integer)

for some reason, if you leave it as a variant, the sub still shows up.

Mike - that's a useful tip on the option private module feature, which I may have a use for in future projects. However, I'm not sure how it applies to Dale's question?
Rob

 

[rmikesmith]

Rob,

Dale wrote:

I have a Private routine attached to a ComboBox_Change event (on a particular sheet), and this routine calls another routine in a Module.

What must I do in order to be able to call a Private routine in a separate Module ???

I took this to mean he wished to call a procedure from a standard code module while at the same time preventing the called procedure from displaying in the Macros dialog. My suggestion achieves that. The Option Private Module directive prevents contained procedures from being viewed in the Macros dialog while procedures located in other modules of the same project have access.

Regards,
Mike

 

[RobBroekhuis]

Aha. I didn't realize that (it's not clear from VBA's help :-() I'm going to play around with it right now (hey, it's the afternoon before T-day, nothing going on around here...)
Rob

 

[RobBroekhuis]

Works nicely indeed. I'll shoot you a star - I can use this myself!
Rob

 

[rmikesmith]

Rob,

Same here; and the clock seems to be moving slower...

I've always found the VBA help to be hit and miss -- some items covered quite well while others nary a mention. Same for the examples. Reminds me of textbook problem examples; they always show the trivial ones. Happy T-Day.

Regards

 

[DaleWatson123321]

Hi all,

Thanks VERY much to all of you for your help.

There were actually TWO situations I had to deal with.

Situation 1)... Where the ComboBox_change event required the code to be on the sheet where it was used.

In attemping to resolve this, I first tried Rob's suggestion of inserting a parameter - but of course I found that the ComboBox_change event won't permit parameters.

Like you, Rob, I found that Mike's suggestion was EXCELLENT. So I now have the ComboBox_change event calling a (public) routine in Module1, without any problem. AND, all of the MANY routines in Module1 are NOT visible - thanks to the &quot;Option Private Module&quot;.

Situation 2)... Where I wanted to be able to have ONLY two modules visible in the Macro (<Alt> <F8>) window. Here I set up a SEPARATE Module - Module2 - where I moved the two main routines. One of the routines, however, had several subroutines. So in order to prevent these subroutines from showing, I made them &quot;Private&quot;.

I'd like to award a STAR to everyone for helping, but I expect you'll agree that would take some of the &quot;shine&quot; off the STARS. I agree with you Rob that Mike deserves (another) STAR. Thanks to all, but especially to Mike.

Regards, ...Dale

 

[RobBroekhuis]

Dale,
Sorry to contact you this way - I know of no other way. I hadn't noticed your input in these forums for a few days, so just in case you were in fact not listening in, may I ask you for your input on a problem I've been having?
The reason for asking you directly is because my problem occurs when I try to automate an advanced filter operation - something which I know is a favorite topic of yours. I was using an autofilter, and decided I needed the greater flexibility of the advanced filter, so I set up a combobox on my worksheet which I want to work in the same way as the autofilter dropdowns which Excel generates. It all works fine, except I'm running into an odd problem with the combobox events. The problem is described in the following thread:

thread707-412927 I suspect you've done these things before, maybe you can comment on whether I'm taking the wrong approach altogether. At any rate, I'd appreciate your input if you have a few minutes time.
Cheers
Rob

 

[DaleWatson123321]

Hi Rob,

Your preception is indeed accurate. I have been rather busy for the past while.

Naturally, I'll be very pleased to provide you with a couple of files that relate to the situation you've described. I expect they should be helpful.

Please email me so I can send the files via return email.

Regards, ...Dale dwatson@bsi.gov.mb.ca