public variable dropped

[gordsky]

Hi All,
Hoping someone can help me sort this issue or suggest a better way to do it.

I have Public variables declared in a module which are then set through a Workbook_Open event checking which column the userid appears in and setting the access level to either 1, 2 or 3.

When a member of staff makes a change to a sheet a Workbook_SheetChange event checks the access level of the employee and then decides what to do with the change (could be nothing or write to sheets x,y,z etc)
Every now and then Excel will drop the value of the access level (returning it to 0) and the sheet stops functioning.

I thought about placing the check within the Workbook_SheetChange event but the problem with that is there are several other things and other modules that also rely on that access level information so it really needs to be set on workbook open.
I dont really want to have to write the code in every module and have thought about saving the value to a random cell on a sheet which is then cleared on close but is there a better way to do this or stop the excel dropping the value.?

Any help would be appreciated.

 

[RoryA]

Anything that resets state will cause global variables to reset. I'd suggest you use a function for each variable (you can give it the same name as the current variable so the rest of the code shouldn't need to change) which uses a static variable that can simply be reused if it's set or reset if it's 0.

 

[gordsky]

Anything that resets state will cause global variables to reset. I'd suggest you use a function for each variable (you can give it the same name as the current variable so the rest of the code shouldn't need to change) which uses a static variable that can simply be reused if it's set or reset if it's 0.

RoryA thanks for that, I kinda understand functions but how would i go about it.? at the moment my code (shortened version) is this. All other queries the check the 'acclevel' number.
How would I change that to a function and would this be placed in a module or the workbook open event.

Set Acc = AccRng.Find(what:=Environ("username"), lookat:=xlWhole, MatchCase:=False)
If Not Acc Is Nothing Then
    if acc.colum = 1
       acclevel = 1
  
       elseif acc.colum=2
              acclevel = 2
  
  else
       acclevel= 3
 end if

 

[RoryA]

First remove your global variable. Then make that code into a function called AccLevel that looks something like:

Public Function AccLevel() as Long
Static currentLevel as long
if currentLevel > 0 then
   AccLevel = currentLevel
Else
   Dim acc as range
   Set Acc = AccRng.Find(what:=Environ("username"), lookat:=xlWhole, MatchCase:=False)
   If Not Acc Is Nothing Then
       if acc.colum = 1
           currentLevel = 1
       elseif acc.colum=2
           currentLevel =3
       else
           currentLevel = 2
      end if
      AccLevel = currentLevel
    end if
End If
end Function

I'd suggest doing the same for AccRng if that is also a public variable used elsewhere.

 

[gordsky]

RoryA thanks for that, I kinda understand functions but how would i go about it.? at the moment my code (shortened version) is this. How would I change that to a function and would this be placed in a module or the workbook open event.


Set Acc = AccRng.Find(what:=Environ("username"), lookat:=xlWhole, MatchCase:=False)
If Not Acc Is Nothing Then
if acc.colum = 1
acclevel = 1

elseif acc.colum=2
acclevel =

else acclevel= 2
end if

RoryA thankyou so much, can I just check the "currentlevel" though. where acc.column = 2 should currentlevel also = 2 or is there a reason its reversed in the function v sub?
AccRng is only accessed by that piece of code so presumably its ok to set accrng at the start of the function?

 

[RoryA]

I based it on the original code you posted, so it should mirror whatever the logic is in your actual routine.

Yes, I'd set AccRng in there if that's the only place it's needed.

 

[gordsky]

I based it on the original code you posted, so it should mirror whatever the logic is in your actual routine.

Yes, I'd set AccRng in there if that's the only place it's needed.

brilliant and many thanks for your help, your 2 and 3 is reversed to mine but as long as it should mirror the logic that makes perfect sense. Really do appreciate your help.