Conditionally enable/disable a custom menu

[Stephen Lloyd]

I have a menu added to the main menu bar which I would like to disable if an
active workbook doesn't meet certain criteria. I have a couple of questions.
Currently I'm testing this as a .xls, but I will be converting to a .xla.

Here's what I tried

1 Private Sub Workbook_WindowActivate(ByVal Wn As Window)
2 If Range("A1").Value = "AgentName" And Range("A2").Value = "AvailTime" Then
3 CommandBars(1).Controls("CDR Stats").Enabled = True
4 Else
5 CommandBars(1).Controls("CDR Stats").Enabled = False
6 End If
7 End Sub

It seems to be evaluating when I switch to the workbook the code is held in.
It is hanging up on line 5 and I am receiving Run-time error '91': Object
variable or With block variable not set

Question 1: What am I doing wrong in my code?

Question 2: Once I convert this to a .xla I want this to check every time
the user switches workbooks. Is this the correct Event procedure? I'm
guessing I need something different.

Thanks in advance for helping out a newbie.

 

[Jim Thomlinson]

You need the application level events... Check out this link...

http://www.cpearson.com/excel/AppEvent.aspx

 

[Stephen Lloyd]

Thank you very much. I think this will help a lot once I get it working.

I put the following in a class module and am unfortunately unable to get it
to work. This is directly from Pearson's code.

Private WithEvents XLApp As Application

Private Sub Class_Initialize()
Set XLApp = Application
End Sub

Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "NewWorkbook" & Wb.Name
End Sub

Any ideas what I might need to change. I have tried changing the instancing
value from private to PublicNotCreatable. Is there anything other than
toggling the value that I would need to do?

 

[Stephen Lloyd]

Actually, I was not creating an instance in the workbook_open event for the
file.

However, I'm still having an issue I'm using the following code in an event
in the class module. I've tried windowactivate, sheetactivate, sheetchange
and workbook activate with no success within the class module. I appreciate
any and all help! Thank you so much. My goal is for the code to evaluate
two cells in any active workbook and enable or disable a menu.

Private Sub XLApp_WorkbookActivate(ByVal Wb As Workbook)
Dim strName As String
strName = Wb.ActiveSheet.Name

If Wb.Worksheets(strName).Range("A1").Value = "AgentName" And _
Wb.Worksheets(strName).Range("B1").Value = "AvailTime" Then
CommandBars(1).Controls("CDR Stats").Enabled = True
Else
CommandBars(1).Controls("CDR Stats").Enabled = False
End If
End Sub

I think everything is instantiated correctly because while the preceding
code is not working, the following code is working:
Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "NewWorkbook" & Wb.Name
End Sub

 

[Stephen Lloyd]

Actually, I was not creating an instance in the workbook_open event for the
file.

However, I'm still having an issue I'm using the following code in an event
in the class module. I've tried windowactivate, sheetactivate, sheetchange
and workbook activate with no success within the class module. I appreciate
any and all help! Thank you so much. My goal is for the code to evaluate
two cells in any active workbook and enable or disable a menu.

Private Sub XLApp_WorkbookActivate(ByVal Wb As Workbook)
Dim strName As String
strName = Wb.ActiveSheet.Name

If Wb.Worksheets(strName).Range("A1").Value = "AgentName" And _
Wb.Worksheets(strName).Range("B1").Value = "AvailTime" Then
CommandBars(1).Controls("CDR Stats").Enabled = True
Else
CommandBars(1).Controls("CDR Stats").Enabled = False
End If
End Sub

I think everything is instantiated correctly because while the preceding
code is not working, the following code is working:
Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "NewWorkbook" & Wb.Name
End Sub

 

[Stephen Lloyd]

Please disregard, I am reposting with a more appropriate title