Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global definitions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global definitions
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 Matthew Balch wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xl* object definitions ?? | Excel Programming | |||
Trouble with 2 range definitions ... | Excel Programming | |||
Definitions | Excel Discussion (Misc queries) | |||
Function Definitions | Excel Programming | |||
Operator definitions | Excel Programming |