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" wrote:
> 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?
>
> "Jim Thomlinson" wrote:
>
> > You need the application level events... Check out this link...
> >
> > http://www.cpearson.com/excel/AppEvent.aspx
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Stephen Lloyd" wrote:
> >
> > > 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.
> > >
> > >
> > >
> > >
Similar Threads
