Global Variable

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

Patrick Simonds

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
 
You need to actually declare the variable eg
Public bSELCTIONCHANGE As Boolean

Hope this helps
Rowan
 
I am sorry but can you walk me through this?

Should it look like

Public Sub bSELCTIONCHANGE()

bSELCTIONCHANGE As Boolean

End Sub
 
It should be like this:

Public bSELCTIONCHANGE as Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If bSELCTIONCHANGE Then
etc

Regards
Rowan
 
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.
 
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
 
You have another reply at your other thread.

Patrick said:
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
Click to expand...
 
Thank you for all your help


Rowan Drummond said:
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
Click to expand...
 
Back
Top