Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally enable/disable a custom menu
Please disregard, I am reposting with a more appropriate title
"Stephen Lloyd" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable custom menu item based on visible cells | Excel Programming | |||
Context Menu (Sub-Menu Disable/Enable) | Excel Programming | |||
Disable custom drop-down menu | Excel Programming | |||
Enable/Disable Menu Items | Excel Programming | |||
Menu Items and Disable/Enable in Excel 2003 | Excel Programming |