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?

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?

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.

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".

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?

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
================================================