How do I declare 2 "Public" variables in the declaration section of "ThisWorkbook" so they are available across all modules after the worksheet is opened and until it is closed? Tried "Public WarningDate As Date, ExpirationDate As Date", but only the first, WarningDate, is available. Is there only 1 declaration line available? Can I list more than 1? If so, how?
Thank you.
You can use variables in object modules this way:
Public BigTime As Date, LittleTime As Date MsgBox Sheet1.BigTime MsgBox Sheet1.LittleTime It is not possible the way you're trying to do it.
You have to differentiate 'normal' modules (via add module) and object modules (ThisWorkbook, Sheet1, etc.).
If you declare a public variable in a 'normal' module, it will be 'visible' to all procedures of all modules including object modules as it is e.g.:
WarningDate ExpirationDate If you declare a public variable in an object module, it will be 'visible' to all procedures of the object module as it is e.g.:
WarningDate ExpirationDate and it will be 'visible' to all other modules including object modules, but you have to use its object reference to refer to it, e.g.:
ThisWorkbook.WarningDate ThisWorkbook.ExpirationDate Conclusion
You correctly declared the variables, but you didn't know how to refer to them. If you want public variables accessed without object references add (or pick) a module (not object module) and declare them there.
Visualize
' Code in ThisWorkbook Option Explicit Public ThisWorkbookDate As Date Sub MainThisWorkbook() MsgBox ThisWorkbookDate MsgBox Module1Date MsgBox Sheet1.Sheet1Date End Sub ' Code in Module1 Option Explicit Public Module1Date As Date Sub MainModule1() MsgBox ThisWorkbook.ThisWorkbookDate MsgBox Module1Date MsgBox Sheet1.Sheet1Date End Sub ' Code in Sheet1 Option Explicit Public Sheet1Date As Date Sub MainThis() 'OK MsgBox ThisWorkbook.ThisWorkbookDate MsgBox Module1Date MsgBox Sheet1Date End Sub 
Public BigTime As Date in Sheet1, I can call it in any module as Sheet1.BigTime.
Thanks to everyone for the help. I ended up just placing the dates in a locked, hidden cell in the locked first worksheet, then referring to it in various macros and in an open workbook command. Each of these macros re-declares the variable with these cells values. Not the most efficient, but it works and I only need to update 3 cell dates to change the triggers. The code I used is ...
Private Sub Workbook_Open() 'Usage Rights Code Static WarningDate As Date WarningDate = Worksheets("XXX").Cells(1, 1).Value Static ExpirationDate As Date ExpirationDate = Worksheets("XXX").Cells(2, 1).Value Static LockDate As Date LockDate = Worksheets("XXX").Cells(3, 1).Value ' If Date >= WarningDate And Date < ExpirationDate Then MsgBox "Usage rights for this workbook will expire on " _ & ExpirationDate & ". After the expiration date, critical " _ & "calculations will be disabled. Please contact ABC Co." _ & "to avoid de-activation." ElseIf Date >= ExpirationDate And Date < LockDate Then MsgBox "Usage rights for this workbook expired on " _ & ExpirationDate & ". Critical calculations have been " _ & "disabled. The workbook will be locked on " & LockDate _ & ". Please contact ABC Co. for re-activation." ElseIf Date >= LockDate Then MsgBox "Usage rights for this workbook expired on " _ & ExpirationDate & ". The workbook has been disabled and " _ & "locked. Please contact ABC Co. for re-activation." ThisWorkbook.Close savechanges:=False End If End Sub