| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
I am confused, I want to create a global variable called: bSELCTIONCHANGE. I
read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
You need to actually declare the variable eg
Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
I am sorry but can you walk me through this?
Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message ... You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
It should be like this:
Public bSELCTIONCHANGE as Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then etc Regards Rowan Patrick Simonds wrote: I am sorry but can you walk me through this? Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message ... You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
I appreciate your time. I guess I do not know in which module to place
this. I assume that as Public it could go into any module. Should I put it at the top of my Auto_Open Macro? I placed the following code around my WorkSheet Code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show End If End Sub And I have used: bSELCTIONCHANGE = False and bSELCTIONCHANGE = True On my userform which determines if bSELCTIONCHANGE is true or false. When I click on the option button which should change it to True: If OptionButton2.Value = True Then bSELCTIONCHANGE = True Unload UserFormAccess UserFormPassword.Show End If It has does not seem to turn bSELCTIONCHANGE to true, since the macro it is wrapped around does not run. "Rowan Drummond" wrote in message ... It should be like this: Public bSELCTIONCHANGE as Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then etc Regards Rowan Patrick Simonds wrote: I am sorry but can you walk me through this? Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message ... You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
Hi Patrick
You should declare public variables in a standard module not a class module. The code modules behind sheets and userforms are class modules so you should insert a new standard module and use this to declare you public variable: Public bSELCTIONCHANGE as Boolean Hope this helps Rowan Patrick Simonds wrote: I appreciate your time. I guess I do not know in which module to place this. I assume that as Public it could go into any module. Should I put it at the top of my Auto_Open Macro? I placed the following code around my WorkSheet Code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show End If End Sub And I have used: bSELCTIONCHANGE = False and bSELCTIONCHANGE = True On my userform which determines if bSELCTIONCHANGE is true or false. When I click on the option button which should change it to True: If OptionButton2.Value = True Then bSELCTIONCHANGE = True Unload UserFormAccess UserFormPassword.Show End If It has does not seem to turn bSELCTIONCHANGE to true, since the macro it is wrapped around does not run. "Rowan Drummond" wrote in message ... It should be like this: Public bSELCTIONCHANGE as Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then etc Regards Rowan Patrick Simonds wrote: I am sorry but can you walk me through this? Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message . .. You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
You have another reply at your other thread.
Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
Thank you for all your help
"Rowan Drummond" wrote in message ... Hi Patrick You should declare public variables in a standard module not a class module. The code modules behind sheets and userforms are class modules so you should insert a new standard module and use this to declare you public variable: Public bSELCTIONCHANGE as Boolean Hope this helps Rowan Patrick Simonds wrote: I appreciate your time. I guess I do not know in which module to place this. I assume that as Public it could go into any module. Should I put it at the top of my Auto_Open Macro? I placed the following code around my WorkSheet Code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show End If End Sub And I have used: bSELCTIONCHANGE = False and bSELCTIONCHANGE = True On my userform which determines if bSELCTIONCHANGE is true or false. When I click on the option button which should change it to True: If OptionButton2.Value = True Then bSELCTIONCHANGE = True Unload UserFormAccess UserFormPassword.Show End If It has does not seem to turn bSELCTIONCHANGE to true, since the macro it is wrapped around does not run. "Rowan Drummond" wrote in message ... It should be like this: Public bSELCTIONCHANGE as Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then etc Regards Rowan Patrick Simonds wrote: I am sorry but can you walk me through this? Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message .. . You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Global Variable
You're welcome.
Patrick Simonds wrote: Thank you for all your help "Rowan Drummond" wrote in message ... Hi Patrick You should declare public variables in a standard module not a class module. The code modules behind sheets and userforms are class modules so you should insert a new standard module and use this to declare you public variable: Public bSELCTIONCHANGE as Boolean Hope this helps Rowan Patrick Simonds wrote: I appreciate your time. I guess I do not know in which module to place this. I assume that as Public it could go into any module. Should I put it at the top of my Auto_Open Macro? I placed the following code around my WorkSheet Code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show End If End Sub And I have used: bSELCTIONCHANGE = False and bSELCTIONCHANGE = True On my userform which determines if bSELCTIONCHANGE is true or false. When I click on the option button which should change it to True: If OptionButton2.Value = True Then bSELCTIONCHANGE = True Unload UserFormAccess UserFormPassword.Show End If It has does not seem to turn bSELCTIONCHANGE to true, since the macro it is wrapped around does not run. "Rowan Drummond" wrote in message ... It should be like this: Public bSELCTIONCHANGE as Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then etc Regards Rowan Patrick Simonds wrote: I am sorry but can you walk me through this? Should it look like Public Sub bSELCTIONCHANGE() bSELCTIONCHANGE As Boolean End Sub "Rowan Drummond" wrote in message . .. You need to actually declare the variable eg Public bSELCTIONCHANGE As Boolean Hope this helps Rowan Patrick Simonds wrote: I am confused, I want to create a global variable called: bSELCTIONCHANGE. I read through the other posts and thought that I had to place the global variable before the Private Sub. But I get an error saying I have an Invalid Outside Procedure. Where does the global variable belong? I have another macro which sets bSELCTIONCHANGE to false to prevent the Private Sub from running. bSELCTIONCHANGE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If bSELCTIONCHANGE Then 'Backup Board If Not Application.Intersect(Target, Range("D5, D7, D9, D11, D13, D15, D17, D19")) Is Nothing Then Sunday_Route_Selection.Show 'Relief Board If Not Application.Intersect(Target, Range("D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D44, D46")) Is Nothing Then Sunday_Route_Selection.Show 'Part Time Available If Not Application.Intersect(Target, Range("D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70")) Is Nothing Then Sunday_Route_Selection.Show 'Overtime and Miscellaneous Assignments If Not Application.Intersect(Target, Range("D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D112, D114")) Is Nothing Then Sunday_Route_Selection.Show 'Routes To Cover If Not Application.Intersect(Target, Range("D119:D147")) Is Nothing Then Sunday_Routes_to_Cover.Show End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Global variable | Excel Discussion (Misc queries) | |||
| Global Variable | Excel Discussion (Misc queries) | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| userform variable as global | Excel Programming | |||
| Global variable | Excel Programming | |||
Linear Mode
