Can't use call command

[JohnSearcy]

I know you can't call a module while writing within the sheets (coding for control toolbox command button on sheet) as if you were witing code for a command button on UserForm or within a module itself. How can you do something similar within sheet atmosphere?

 

[Smitty]

Ummm, I'm not quite sure what you're trying to do...Are you thinking of Sheet level events, like Activate, Change, etc?

If you're trying to call code from multiple sheets you can just put the code in question in a general module and you should be able to call it just fine.

Or am I missing something entirely?

 

[JohnSearcy]

I'll try to do a little better.....

I'm in the editor within Sheet1(Sheet1) under command button click event. Here are a few lines of code:

If ILINENUMBER = 5 Then

CUSTOMER = Range("B" & ILINENUMBER).Value

Call FIND_CUSTOMER (module called FIND_CUSTOMER)

WORKORDER = Range("C" & ILINENUMBER).Value
PIECES = Range("D" & ILINENUMBER).Value

It gives a compile error for the call - Expected variable or procedure, not module

 

[Norie]

There is no way that you can 'call' a module, no matter what is called or where it is - it's a container for code.

What's the name of the sub you want to call?

 

[JohnSearcy]

I should have said called a sub named FIND_CUSTOMER (under modules). If this was within a UserForm commandbutton click event which I have done before, it works. Apparently not within sheet environment. A thought I had was to use a forms command button which writes into a module, but you can't change the color of the buttons.

 

[Smitty]

You can call code from sheet objects just fine:

Private Sub CommandButton1_Click()
    Call foo
End Sub

And in a general module:

SUb Foo()
  MsgBox "Foo"
ENd Sub

Where are you having the problem?

 

[RSXchin]

I think you may have more than one sub with the same name.
Try fully qualifying the module and sub

call module1.foo

wait a minute... why do you have to even say "Call" if you just have the sub name, it should run. 2007 anyway.

 

[JohnSearcy]

THANKS to all that responded! Well, after looking at other code in other workbooks I figured it out and the call command now works......but I don't know why. In modules, I originally named that module FIND_CUSTOMER. I went back and named it FIND_CUSTOMER_Module. Like I said the call command now works (as you can see in my second post in this thread), but can one of you briefly explain why just by adding Module back to the name? By the way, I have Excel 2003.

Thanks again,
John

 

[Norie]

John

Did you have a sub called FIND_CUSTOMER in a module also called FIND_CUSTOMER?

If you did that was probably the problem, you shouldn't give a sub and the module it's in the same name.