Excel buttons change macro file name when run on other computers

[Victor Delta]

I have an Excel spreadsheet which I regularly copy across and use on several
different pcs (each using Excel 2003). The spreadsheet uses macros, some of
which are accessed using toolbar buttons that I have created.

For some years this arrangement has worked well and without any problems,
and on my main pc it still does. However, now when I use the same
spreadsheet on the other pcs, for some reason some of the buttons try to
access macros in another file, the name of which I can see if I go to
Customise..., Assign macro... (Say the main file is spreadsheet.xls, the
file that the macros try to run from is a one off old saved version of the
same file, say, spreadsheet backup May 13.xls - I see this happening because
the file is password protected and so the 'enter password' dialogue box
appears when I hit the macro buttons). On the main pc the macros link to the
main spreadsheet.xls file as they should.

Linking the button to the correct macros sorts the problem temporarily, but
it returns after the file has been copied back to the master pc and then
back again to the second pc.

I'm probably missing something obvious - and I've googled the problem
thoroughly without success - so I wonder if anyone here has any ideas
please?

Many thanks,

V

 

[GS]

Sounds to me that your project is a reusable utility. If so then I'd
put the code in PERSONAL.XLS (or convert to an addin) so the files the
code works on are macro-free! Make the code add a dropdown menuitem on
the Excel Menubar so you can access the features via submenus.

-OR-

If the workbook needs to be stand-alone:
Make the project add the dropdown menuitem to Excel's Menubar on
startup, and assign macros to submenus.

Note that macros should be assigned via the OnAction property when the
submenus are added to your dropdown at startup.

Note also that any menus you add to Excel should be removed at
shutdown.

For clarity...

Startup happens when a file is opened.
Shutdown happens before a file closes.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Example code to be placed in a standard module:
(I usually name it "m_OpenClose")

Option Explicit

Sub Autpen()
CreateMenus
End Sub

Sub Auto_Close()
RemoveMenus
End Sub

Sub CreateMenus()
' Adds a dropdown menuitem (and submenus) to the Excel Menubar
' between 'Tools' and 'Data'.

'Delete the menuitem if it already exists
On Error Resume Next
CommandBars(1).Controls("MyUtilities").Delete
On Error GoTo 0

'Add the new dropdown menu
With CommandBars(1).Controls.Add(Type:=msoControlPopup, _
Before:=CommandBars(1).Controls("Data").Index, _
Temporary:=True)
.Caption = "MyUtilities"

'Add submenus
With .Controls.Add(msoControlButton)
.Caption = "Submenu1"
.OnAction = "Procedure1"
'.FaceId = 123 '//add icon
'.BeginGroup = True '//add separator
End With

With .Controls.Add(msoControlButton)
.Caption = "Submenu2"
.OnAction = "Procedure2"
'.FaceId = 124 '//add icon
'.BeginGroup = True '//add separator
End With
End With 'CommandBars(1).Controls.Add
End Sub

Sub RemoveMenus()
On Error Resume Next
CommandBars(1).Controls("MyUtilities").Delete
End Sub

Just copy/paste the With...End With blocks to add more submenus.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Note that the menu will appear on the Addins tab in Excel 2007 and
later!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Victor Delta]

Garry

Many thanks for your fascinating responses, which I will follow up.

However, I'd still be interested to understand what's causing this problem
and whether it can resolved without abandoning the macros?

V


"GS" wrote in message
Note that the menu will appear on the Addins tab in Excel 2007 and
later!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

However, I'd still be interested to understand what's causing this

problem and whether it can resolved without abandoning the macros?

When you assign macros to worksheet controls, they ref the original
workbook's VBA. This ref persists when using Save/SaveAs (or so I
believe) until you change it. Note that in the macros dialog, macros
are prefixed with the workbook name.

My suggestion obviates any refs/links to original files. The example
code can be used anywhere you wish, whether it be an addin always
loaded, PERSONAL.XLS, or the project workbook itself. The only negative
is if you revise the code then all copies need to be updated/replaced.
I find it best to store 1 copy on a share and have all PCs use that
copy. This makes maintenance a bit easier going forward<g>!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Victor Delta]

A friend has suggested this problem has been caused by changing the file
name, and so by changing it again manually (using Windows Explorer) to
spreadsheet backup May 13.xls, if I then open it I can use 'Save As...' to
resave it as spreadsheet.xls.

Hopefully, this should get everything (including the buttons) back to where
it was before.

---------------------------------------

"Victor Delta" wrote in message

I have an Excel spreadsheet which I regularly copy across and use on several
different pcs (each using Excel 2003). The spreadsheet uses macros, some of
which are accessed using toolbar buttons that I have created.

For some years this arrangement has worked well and without any problems,
and on my main pc it still does. However, now when I use the same
spreadsheet on the other pcs, for some reason some of the buttons try to
access macros in another file, the name of which I can see if I go to
Customise..., Assign macro... (Say the main file is spreadsheet.xls, the
file that the macros try to run from is a one off old saved version of the
same file, say, spreadsheet backup May 13.xls - I see this happening because
the file is password protected and so the 'enter password' dialogue box
appears when I hit the macro buttons). On the main pc the macros link to the
main spreadsheet.xls file as they should.

Linking the button to the correct macros sorts the problem temporarily, but
it returns after the file has been copied back to the master pc and then
back again to the second pc.

I'm probably missing something obvious - and I've googled the problem
thoroughly without success - so I wonder if anyone here has any ideas
please?

Many thanks,

V

 

[GS]

A friend has suggested this problem has been caused by changing the

file name, and so by changing it again manually (using Windows
Explorer) to spreadsheet backup May 13.xls, if I then open it I can
use 'Save As...' to resave it as spreadsheet.xls.

Hopefully, this should get everything (including the buttons) back to
where it was before.

If the file *always* resides on the machine it's used on then this
might suffice. If the file gets used on other machines then I strongly
recommend going with what I suggested because it's a 'no nonsense, get
the job done' approach IMO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion