VARIABLES AGAIN

[Berj]

I have a problem with string variables:

I have created this button in an Excel sheet to which I have attached a Macro.
When I bring out the Programming code, it shows projects and modules. The
real program is in the module and it is called by an expression “Call Macro1â€
found in the projects section of the spreadsheet.
I am creating about 40 Macro buttons which will have to do the same
procedure but with 2 different variables changing with every button. These
are string variables.
Thus far I have done the following:

In the Project sheet these are my lines:

Private Sub CommandButton1_Click()
S$ = "7A"
N$ = "B3"
Call Macro1
End Sub

In the Macro (in the module – the procedure), I have used the following
lines:
Sheets(S$).Select
Range(N$).Select

But it gives me an error while executing.
I have tried putting the variable assignments in the same Macro1, and it
works. But that doesn’t solve anything for me.
As I guess it, the variable values are not passing from the Project sheet to
the Macro.

I have even tried to use another Macro2 and place my variable assignments
there. Calling Macro2 first and then Macro1. It still doesn’t work.

Any solution to this problem? I am in a hurry guys.
Please Help. I am new to this.

Regards.

 

[Gary''s Student]

Avoid the $.
You need to Dim the variables in the right place, above code in a standard
module:

Public N As String
Public S As String
Sub Macro1()
'your stuff
End Sub

This way your other subs can "see" the variables:

Private Sub CommandButton1_Click()
S = "7A"
N = "B3"
Call Macro1
End Sub

 

[anon]

You'll need to declare them as public variables - at the top of a
module (outside of a normal sub procedure) type

PUBLIC thisvar

Obviously change thisvar to the name of your variable. You can declare
your public variable as a type (string, integer etc) as you would with
declaration within a procedure.

 

[Bob Phillips]

Pass the range as a variable

Private Sub CommandButton1_Click()
Call Macro1(Worksheets("7A").Range("B3"))
End Sub

Sub Macro1 (Target As Range)

Target.Parent.Select
Target.Select

'etc
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)