Custom button starts new instance of Excel

[Guest]

This has me stumped, so any help will be deeply appreciated!

I built a form that creates a custom toolbar when the file opens to allow
the user to run macros. Recently several new users have reported a series of
errors when they click any of the custom buttons. I can't reproduce it on my
computer but the errors sound like the buttons are trying to run the macros
from a different workbook that doesn't exist. The users are prompted to
"Enable Macros" a second time and then get a message that the macro can't be
found. The users can, however, run the macro directly from the menu options,
just not from the custom buttons.

When I open the Assign Macro box, I see the current file name as a path in
front of the macro name. Could that be causing the problem? I remember,
sometime in the past, seeing only the macro name (no file name) in the Assign
Macro box.

I've used either Excel 2003 or Excel 2002 to edit the file, depending on
which office I'm working in. I've tried setting the macro assignment to
"This Workbook only", but the file name is still there. On my computer(s),
the file name does change when I rename the file, so the buttons still find
the right macro to run.

I haven't pinned down a common attribute for the all users who have this
error, but several work on US Government sites which have many security
restrictions on software. Is it possible that network security is affecting
the connection between the button and the macro?

Any ideas?

Thanks,
EllenR

 

[Guest]

hi,
if the user can run the macro from the menu, that (to me)
zeros in on the form.
the file name appearing in front of the macro name
indicate that the macro is not in the active file. at
least on my pc. if i have 2 files open both with macros,
the macro box shows only the macro name of the macros in
the active file but show both file and macro names of the
inactive file.
i am still on 2k so i not sure how squirlly 2002 and 2003
is.

 

[MarkHG]

Ellen

Are the macro's/the form in an addin?

If not this is probably the way to go - don't use the Macro assign
wizard but but just call a method (the macro) from the button click
event from the form code (as you would in VB6).

If the user has opened more than one file, which have macros of the
same name Excel can get confused.

If you put your macro code in an addin and reference the addin from the
files themselves this may get around your problem. Though I have to
say I've not come across the problem you have described.

Hope you sort it.
Mark

 

[Guest]

I've never worked with addins, so that will be a learning curve. Also, my
users at government sites may not be allowed to install an addin, so having
the macros run from within the file is the easiest method...except when it
doesn't work.

I'm using the Commandbars commands to create the custom toolbar, rather than
the old Toolbarbuttons commands. Would it be worth changing back to the old
code, which worked fine for years?

 

[Dave Peterson]

John Walkenbach has a technique to add items to the worksheet menubar at:
http://j-walk.com/ss/excel/tips/tip53.htm

If you really want a toolbar, here's how I do it:
http://groups.google.co.uk/[email protected]

It builds the toolbar when the workbook opens and deletes it when the workbook
closes.

 

[Guest]

Thanks for the links. If I can't find any other answer, I may have to switch
from buttons to the menubar approach. I like the custom buttons, though,
because I can paste my own icons on the faces, rather than just using
standard faces.

My code is very similar to the create toolbar code in the second link. The
workbook has used the same code to create the toolbar for over a year now,
since I upgraded it from the Excel 5.0 toolbarbuttons commands to use the
Commandbar commands.

So this aberrant behavior is really puzzling and frustrating to solve,
especially since I can't make it happen on any computers I use. It
apparently only affects a small group of the 1000+ employees who use the
form, but they are a very vocal group. My next tactic will be to find some
commonality among the users who get the error.

 

[Guest]

Ellen,

Did you ever figure out your problem? I am having the same issue.

Thanks!

 

[Guest]

Not yet... I added the macros as menu functions and changed the build of the
custom toolbarbuttons as shown below.

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant
Dim face_names As Variant

mac_names = Array("init_rpt", _
"printem", _
"print_draft_copy", _
"print_blank_page")

cap_names = Array("Initialize Form", _
"Audit && Print", _
"Draft Page", _
"Blank Form ONLY")

tip_text = Array("Clear Form or change form number", _
"Audit, then Print activesheet", _
"Draft Page for review - Not audited", _
"Blank Form to be handwritten")

face_names = Array("Init_form", _
"Audit_Print", _
"print_draft", _
"prt_blank")

With Application.CommandBar.Add
.Name = "EXPRPT"
.Protection = msoBarNoProtection

For i = LBound(mac_names) To UBound(mac_names)
Sheets("TRANS").DrawingObjects(face_names(i)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(i)
End With
Next i
End With

and initial reports from users indicated that the buttons were working.
However, just a couple weeks ago, someone else reported that their file was
erroring by trying to open duplicate instance again, so it didn't work for
everyone.

As a workaround, the users can run the macros by using Tools / Macro /
Macros / Run but it would be so much nicer for the buttons to work. I wish I
knew what the government computers are doing to Excel to cause this
behavior...

 

[Dave Peterson]

This line:
With Application.CommandBar.Add
should be:
With Application.CommandBars.Add

And do you throw away the "EXPRPT" toolbar before you start building it? It was
included in that link.

And what's the name of the workbook?

I've seen people do this:
.OnAction = "'" & ThisWorkbook.Name & "'!Macro1"

When there's special stuff in the workbook name.

 

[Guest]

Dave,

Thanks for the tip! I do have code to delete an existing EXPRPT toolbar,
just didn't paste it into the message.

I'll change the With statement and try putting single quotes around the
workbook name. The file name is 3005-2 Expense Forms 6.06.xls, so the blanks
may be causing a problem. Since I can't test it myself, I'll send it to one
of the users to see if it works for them.

 

[Dave Peterson]

I use underscores instead of blanks.

3005-2_Expense_Forms_6.06.xls

It seems to avoid lots of problems.

Dave,

Thanks for the tip! I do have code to delete an existing EXPRPT toolbar,
just didn't paste it into the message.

I'll change the With statement and try putting single quotes around the
workbook name. The file name is 3005-2 Expense Forms 6.06.xls, so the blanks
may be causing a problem. Since I can't test it myself, I'll send it to one
of the users to see if it works for them.

 

[Guest]

Just to let you know, the code that Dave reviewed seems to be working,
finally !! I added the single quotes, as he suggested:

..OnAction = "'" & ThisWorkbook.Name & "'!Macro1"

to insure that the button would find the macro in the current file. Hope
this is helpful to you.

 

[Dave Peterson]

Thanks for posting back.

Just to let you know, the code that Dave reviewed seems to be working,
finally !! I added the single quotes, as he suggested:

.OnAction = "'" & ThisWorkbook.Name & "'!Macro1"

to insure that the button would find the macro in the current file. Hope
this is helpful to you.

 

[Jeff Abbott]

I've seen this before. I assume you users have Office 2007. In that
version, if your code is run from a click event rather than being run
directly by excel based on the value of the OnAction property of the button,
you need to set the Cancel paramter to true even though the button is not a
built-in feature of Excel.