VARIABLES AGAIN
 

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 doesnt 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 doesnt work.

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

Regards.

VARIABLES AGAIN
 

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

--
Gary''s Student - gsnu200801


"Berj" wrote:

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 doesnt 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 doesnt work.

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

Regards.

VARIABLES AGAIN
 

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.

VARIABLES AGAIN
 

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)

"Berj" wrote in message
...
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.