Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
Hi,
Our product (which we ship as an xla) is controlled via a toolbar, which it creates on first run. In the module "ThisWorkbook" we have the following code: Private Sub Workbook_Open() Dim oCB As CommandBar Dim isthere As Object appMenu = "SavvyC" On Error GoTo create_toolbar Set isthere = Application.CommandBars(appMenu) Exit Sub create_toolbar: On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) [rest of code omitted] On every machine we have tested it, the first time that the xla is opened, since the SavvyC toolbar doesn't exist, there is (by design) an error on the "Set isthere = " line, which causes the error handler (set by the previous "On Error" statement) to jump to the create_toolbar label. It then creates the toolbar. However, we have one customer (Excel 2003, Windows XP SP1) for whom this is not the case. When she opens the xla, instead of the On Error line causing the flow of execution to resume at create_toolbar, the user gets a run-time error, either Error -2147024809 (80070057) [which doesn't make any sense] or Error 5 [which is a little more understandable but still unexpected] with the error "Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus." What does anybody make of this? Is there anything that we can tell the enduser to do? P.S. What's the difference between using Workbook_Open and Auto_Open? |
#2
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
Jonathan,
It appears from what you posted that the "appMenu" variable data type is not declared. Also, "isthere" might be properly declared as a CommandBar instead of as an Object. I don't know whether that could be causing your problem, but it is two straws on the camel's back. Another approach, that I prefer is something like this... '-------------------------------- On Error Resume Next Application.CommandBars("SavvyC").Delete On Error GoTo ErrorHandler Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) '---------------------------------- Regards, Jim Cone San Francisco, USA "Jonathan Rynd" wrote in message ... Hi, Our product (which we ship as an xla) is controlled via a toolbar, which it creates on first run. In the module "ThisWorkbook" we have the following code: Private Sub Workbook_Open() Dim oCB As CommandBar Dim isthere As Object appMenu = "SavvyC" On Error GoTo create_toolbar Set isthere = Application.CommandBars(appMenu) Exit Sub create_toolbar: On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) [rest of code omitted] On every machine we have tested it, the first time that the xla is opened, since the SavvyC toolbar doesn't exist, there is (by design) an error on the "Set isthere = " line, which causes the error handler (set by the previous "On Error" statement) to jump to the create_toolbar label. It then creates the toolbar. However, we have one customer (Excel 2003, Windows XP SP1) for whom this is not the case. When she opens the xla, instead of the On Error line causing the flow of execution to resume at create_toolbar, the user gets a run-time error, either Error -2147024809 (80070057) [which doesn't make any sense] or Error 5 [which is a little more understandable but still unexpected] with the error "Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus." What does anybody make of this? Is there anything that we can tell the enduser to do? P.S. What's the difference between using Workbook_Open and Auto_Open? |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
Jim Cone wrote:
Jonathan, It appears from what you posted that the "appMenu" variable data type is not declared. We didn't have Option Explicit; it was automatically declared. Also, "isthere" might be properly declared as a CommandBar instead of as an Object. This doesn't seem to make a difference for 99% of our customers. Another approach, that I prefer is something like this... '-------------------------------- On Error Resume Next Application.CommandBars("SavvyC").Delete On Error GoTo ErrorHandler Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) '---------------------------------- I prefer it too, but this causes the toolbar's position to get reset each time the script executes. We need the toolbar to stay put. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
Jonathan Rynd wrote:
However, we have one customer (Excel 2003, Windows XP SP1) for whom this is not the case. When she opens the xla, instead of the On Error line causing the flow of execution to resume at create_toolbar, the user gets a run-time error, either Error -2147024809 (80070057) [which doesn't make any sense] or Error 5 [which is a little more understandable but still unexpected] with the error "Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus." This customer had "Break on every error" set in her VBA settings (Tools/Options) which was what was stopping us. In order to use On Error the setting must be "Break on unhandled errors". |
#5
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
An alternative and perhaps less "error prone" way to determine if the appMenu
is there would be to cycle through the toolbars testing whether the toolbar is the Dim bFound as Boolean dim oCB as CommandBar bFound = False For each oCB in Application.CommandBars If oCB.Name = "appMenu" then bFound = True End if Exit for Next oCB if bFound = False then Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) End if Of course there's a few more lines of code but it seems to me its safer because you don't have to worry about a particular machine's VBA settings, and trapping errors as a decision processing strategy risks causing ancillary undesirable effects. We've run into this recently with some code that attempts to access Document Properties in protected documents and if an error occurs when providing a blank password, goes on to do something else -- unfortunately the situation that generates an error also raises repeated unprotect dialogs (because Document Properties can't be changed without unprotecting the document) which is not something we want in this particular case; a more elegant and much less obstructive solution is to test whether the document is protected in the first place and only attempting to access Document Properties if the document is unprotected. So it seems to me that, in general, a best practice principle might be to explicitly test for the condition and then act on the result rather than to assume the condition and clean up the debris if there's an error. Then again, maybe I'm missing something... Chuck "Jonathan Rynd" wrote: Hi, Our product (which we ship as an xla) is controlled via a toolbar, which it creates on first run. In the module "ThisWorkbook" we have the following code: Private Sub Workbook_Open() Dim oCB As CommandBar Dim isthere As Object appMenu = "SavvyC" On Error GoTo create_toolbar Set isthere = Application.CommandBars(appMenu) Exit Sub create_toolbar: On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) [rest of code omitted] On every machine we have tested it, the first time that the xla is opened, since the SavvyC toolbar doesn't exist, there is (by design) an error on the "Set isthere = " line, which causes the error handler (set by the previous "On Error" statement) to jump to the create_toolbar label. It then creates the toolbar. However, we have one customer (Excel 2003, Windows XP SP1) for whom this is not the case. When she opens the xla, instead of the On Error line causing the flow of execution to resume at create_toolbar, the user gets a run-time error, either Error -2147024809 (80070057) [which doesn't make any sense] or Error 5 [which is a little more understandable but still unexpected] with the error "Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus." What does anybody make of this? Is there anything that we can tell the enduser to do? P.S. What's the difference between using Workbook_Open and Auto_Open? |
#6
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
VBA and toolbars
In article , Chuck wrote:
An alternative and perhaps less "error prone" way to determine if the appMenu is there would be to cycle through the toolbars testing whether the toolbar is the Or a bit more speedily perhaps: On Error Resume Next ' Code to create the toolbar If Err.Num = 0 Then ' The toolbar got created, add buttons or whatever else is needed Else ' The toolbar's already there ' Let it be, delete and re-create, whatever you need to do End If Dim bFound as Boolean dim oCB as CommandBar bFound = False For each oCB in Application.CommandBars If oCB.Name = "appMenu" then bFound = True End if Exit for Next oCB if bFound = False then Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) End if Of course there's a few more lines of code but it seems to me its safer because you don't have to worry about a particular machine's VBA settings, and trapping errors as a decision processing strategy risks causing ancillary undesirable effects. We've run into this recently with some code that attempts to access Document Properties in protected documents and if an error occurs when providing a blank password, goes on to do something else -- unfortunately the situation that generates an error also raises repeated unprotect dialogs (because Document Properties can't be changed without unprotecting the document) which is not something we want in this particular case; a more elegant and much less obstructive solution is to test whether the document is protected in the first place and only attempting to access Document Properties if the document is unprotected. So it seems to me that, in general, a best practice principle might be to explicitly test for the condition and then act on the result rather than to assume the condition and clean up the debris if there's an error. Then again, maybe I'm missing something... Chuck "Jonathan Rynd" wrote: Hi, Our product (which we ship as an xla) is controlled via a toolbar, which it creates on first run. In the module "ThisWorkbook" we have the following code: Private Sub Workbook_Open() Dim oCB As CommandBar Dim isthere As Object appMenu = "SavvyC" On Error GoTo create_toolbar Set isthere = Application.CommandBars(appMenu) Exit Sub create_toolbar: On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=False) [rest of code omitted] On every machine we have tested it, the first time that the xla is opened, since the SavvyC toolbar doesn't exist, there is (by design) an error on the "Set isthere = " line, which causes the error handler (set by the previous "On Error" statement) to jump to the create_toolbar label. It then creates the toolbar. However, we have one customer (Excel 2003, Windows XP SP1) for whom this is not the case. When she opens the xla, instead of the On Error line causing the flow of execution to resume at create_toolbar, the user gets a run-time error, either Error -2147024809 (80070057) [which doesn't make any sense] or Error 5 [which is a little more understandable but still unexpected] with the error "Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus." What does anybody make of this? Is there anything that we can tell the enduser to do? P.S. What's the difference between using Workbook_Open and Auto_Open? -- Steve Rindsberg, PPT MVP PPT FAQ: www.pptfaq.com PPTools: www.pptools.com ================================================ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toolbars | Excel Discussion (Misc queries) | |||
ToolBars | New Users to Excel | |||
toolbars | Excel Discussion (Misc queries) | |||
Please Help, No Toolbars in my view-toolbars! | Excel Programming | |||
toolbars | Excel Programming |