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
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