Global definitions

M

Matthew Balch

Hi,

I am using a userform that usings the same defined variables, i.e.
worksheets for several different command buttons.

How do I set up these definitions as 'global' (if thats the right term) so
that I don't have to repeat the definitions for each command_button_click sub?

Example Code:-
Sub UserForm1_Load()

Dim CostWk As Worksheet
Set CostWk = Worksheets("JCOST-ALL")

Dim Costs As String
Costs = "JCOST-ALL"

Dim Rev As String
Rev = "JREV-ALL"


End Sub

Sub CommandButton1_Click()

Dim WBName As String
WBName = ActiveWorkbook.Name

Dim CostWk As Worksheet
Set CostWk = Worksheets("JCOST-ALL")

Dim Costs As String
Costs = "JCOST-ALL"


Application.Sheets(Costs).Visible = True


CostWk.Range("A2:J2").End(xlDown).ClearContents
Windows("Worksheet in Basis (1)").Activate
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(WBName).Activate
CostWk.Select
Range("A2").Select
ActiveSheet.Paste

ActiveWindow.WindowState = xlMaximized

Sheets("Front").Select

Application.Sheets(Costs).Visible = False

End Sub

Sub CommandButton2_Click()

Dim WBName As String
WBName = ActiveWorkbook.Name

Dim RevWk As Worksheet
Set RevWk = Worksheets("JREV-ALL")

Dim Rev As String
Rev = "JREV-ALL"


Application.Sheets(Rev).Visible = True


RevWk.Range("A2:J2").End(xlDown).ClearContents
Windows("Worksheet in Basis (1)").Activate
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(WBName).Activate
RevWk.Select
Range("A2").Select
ActiveSheet.Paste

ActiveWindow.WindowState = xlMaximized

Sheets("Front").Select

Application.Sheets(Rev).Visible = False

End Sub

Sub CommandButton3_Click()

Application.Sheets(Costs).Visible = True

End Sub

Sub CommandButton4_Click()

Application.Sheets(Rev).Visible = True

End Sub



Also, any tips on the above would be appreciated.


TIA
Matt
 
S

Susan

yes, you can definitely do this.
i have taken to setting up a separate module in any large project
called "GlobalMods".
in it i declare all my variables that i want to use throughout the
project.
you don't "dim" them, though, you "public" them..........

instead of
Dim r as Range
you would write
Public r as Range
for all the variables.......... anything that you would normally "dim".
for command buttons or userform things you use:

Public txtName as Control
Public optChoose as Control
etc.

important - when you write the sub in another module, though, you have
to write

Public Sub copytext()

otherwise it doesn't know where to find the variable, because it's
looking for it in that specific module.
hope this helps!
susan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top