Paths for macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
My spreadsheet seems to have a problem finding macros. I'm not sure how this
happened, so i'll try to explain what i did and hopefully someone can give me
a clue on where i went wrong.
- I created a spreadsheet (called 'Calendar'), added a few macros to it and
saved it on our network.
- I also created a new customised toolbar for that spreadsheet. This toolbar
has a few menu items to which i assigned the macros.
- The purpose of one of these macros is to create a new calendar for the new
year. It first displays the 'Save As' dialogue box so the user can choose a
new name for the new calendar, then the macro clears the contents that were
entered from the previous year.
- When i tested this macro and was prompted to Save As, i saved the file as
'Test' on my desktop.
- Then I deleted the 'Test' file and opened my original file 'Calendar' to
add some more few things.
- But now when i try to use my toolbar i get a message saying that 'Test.xls
is not found'.
- I went to my menu items in the customised toolbar and checked the 'Assign
Macro' option and found that the 'Macro Name' refers to the 'Test' file that
i saved then deleted from the desktop!

I can't understand why this happened. All macros were working fine before i
created the 'Test' file. I need help on how to fix this problem.
I want to be able to 'Save As' my original file and still be able to use the
customised toolbar for all original and saved-as documents. Is this possible?

Thanks in advance
Tendresse
 
Hi William, thank you for your reply.
Well yes i can get the 'Test' file back, but that's not the point. My
concern is that i want each document to be working independently. Why would
deleting one file affect the functioning of another in the first place?
I want the customised toolbar to be usable in any 'Calendar' for any year.
I'm not sure why it's not?
Do you know what i mean?
Cheers
Tendresse
 
Tendresse,

To understand better what happens, try this. Assign your toolbar button to a macro in
Calendar, then save and close Calendar. Now if you click the button, it'll open Calendar,
because that's where the macro lives. When you do the Save As, the button link, like most,
gets updated. Now it wants to open Test.

Presumably, it's Calendar you're going to be saving-as. If so, you could use a keyboard
shortcut key to fire the macros instead. The save-as won't destroy the original assignment
to the macro in Calendar, although the saved-as files will also have the macros, and will
respond to the keyboard shortcuts when open. Also, the saved-as workbooks will get the
macro warning when opened. If the macros should be there (saved from Calendar), then fine.
I don't know if only the original Calendar is supposed to have them, or any saved-as copy.

If the macros were only supposed to be in the original (Calendar) workbook, you might
consider having a separate workbook that has the macros, and saves-as (I'm making up save-as
words as I go along) Calendar, which won't contain the macro. Then you could use any old
way to fire the macros -- toolbar buttons, worksheet buttons, keyboard shortcuts.

If any saved-as workbook is supposed to have all the macros, including the save-as macro,
then consider using buttons from the Autoshapes toolbar, on worksheets, assigned to macros.
They stay with the workbook, unaffected by save-ases. There's another one.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
I see your point.
----------------------------------------------------------
Then I deleted the 'Test' file and opened my original file 'Calendar' to------------------------------------------------------------------------------------
Now does getting that file back make 'Calendar' work?
From what I read it appeared you saved a Macro File in Test? Is that the case?





William<"M"
 
Yes, when i get 'Test' back, the macros in 'Calendar' work. However, the
macros are called from 'Test', not from 'Calendar'. It sounds confusing, i
know. Well what happens (i think) is:

- 'Calendar' was created first and had its own macros and a customised
toolbar to run these macros.
- when i saved it as 'Test', it looks like the buttons in the customised
toolbar started to call the macros from the 'Test' file. (which is fine if
i'm working in the 'Test' workbook).
- However, if I want to open the original file 'Calendar' and use it, the
customised toolbar continues to call the macros from 'Test'. I even get the
security message twice: once to ask me whether to enable macros from
'Calendar', and then another one to ask me whether to enable macros from
'Test'.

Did this clarify the picture a bit?

So my question is, how can i make the customised toolbar work for and from
the active workbook only, regardless of whether it's the original one or not?

Thank you very much for taking the time to answer my questions. Much
appreciated.
 
Hi Earl, thanks for your reply. Much appreciated.
You are right, when i save as, it seems that the buttons in the customised
tool bar get linked to the newly created file.

Well, because i have so many macros included in this workbook (16 macros), i
don't think that using keyboard shortcuts will appeal to the users. They
won't like to memorise what each shortcut does. I liked the idea of having
all the commands in drop down menus in one customised toolbar. More
userfriendly.

As for using control buttons inside the worksheets, i tried this idea at the
very beginning (before i discovered that i could customise my own toolbars).
The problem with using control buttons is that i'll need 16 buttons! Because
the calendar spreads across the worksheet, i can't keep the 16 buttons
visible on the screen at all times while scrolling, even when i have the
freeze panes on.

So this leaves me with the option of saving the macros in a separate
workbook. I'll try this idea and see how i go.

To make sure i got it correctly, what i need to do is to delete the macros
in the original file 'Calendar' and save them in a whole new workbook, say
"Master".
And in this case the customised tool bar will only have one source to get
the macros from no matter how many times i save 'Calendar' as. Right?

i'll give it a go and let you know ..

Thanks a million, Earl. you have been very helpful :)

Tendresse
 
I would give serious thought to writing that program as a Dictator
Application and creating the add-ins
 
Hi again, Earl.
Well, i tried your suggestion and it works. I cut all the macros from
'Calendar' and saved them in a new workbook called 'Master' and i attached
the customised tool bar to 'Master'.

But there are a couple of things i need to clarify

1) Now does this mean that every time the user is working on this project,
they always have to have this Master workbook open in the background?! There
is no any other way around that? somehow to hide it for example?

2) when i run the macro that opens the Save As dialog box, it always opens
this dialog box twice! any idea why? here is the code i'm using:

Sub NewYear()

' Prompt the user to save as
MsgBox "The 'Save As' dialog box will now open." & Chr(13) & Chr(13) & _
"Please give the calendar a new name and" & Chr(13) & _
"save it under the folder of your choice.", vbInformation, "New Calendar"

' display the Save As dialog box
Application.Dialogs(xlDialogSaveAs).Show

If Not Application.Dialogs(xlDialogSaveAs).Show Then
Exit Sub
Else
' the rest of the code goes here

end if
end sub


I get the Save As dialog box once, i put the new name and click 'save', then
i get the same dialog box again! any idea why?!!

thanks a lot of all your help.

tendresse
 
Wow, now you got me :)
Well, I am sort of a newy in macros .. so i'll have to do some readings to
learn what Dictator Application and Add-ins are!
One never stops learning .. :)
Thanks for the clue .. i'll defenitely look into that and learn more about
it.
Cheers
Tendresse
 
Tendresse said:
Hi again, Earl.
Well, i tried your suggestion and it works. I cut all the macros from
'Calendar' and saved them in a new workbook called 'Master' and i attached
the customised tool bar to 'Master'.

But there are a couple of things i need to clarify

1) Now does this mean that every time the user is working on this project,
they always have to have this Master workbook open in the background?!
There
is no any other way around that? somehow to hide it for example?
Click to expand...

Yes, create an addin and put the code there.


http://msdn.microsoft.com/library/d...ml/deovrbuildingapplicationspecificaddins.asp
Building Application Specific Add-Ins

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrexceladdins.asp
Excel Add-Ins

http://msdn.microsoft.com/library/d...html/deovraddinstemplateswizardslibraries.asp
Add-ins, Templates, Wizards, and Libraries

2) when i run the macro that opens the Save As dialog box, it always opens
this dialog box twice! any idea why? here is the code i'm using:

Sub NewYear()

' Prompt the user to save as
MsgBox "The 'Save As' dialog box will now open." & Chr(13) & Chr(13) &
_
"Please give the calendar a new name and" & Chr(13) & _
"save it under the folder of your choice.", vbInformation, "New
Calendar"

' display the Save As dialog box
Application.Dialogs(xlDialogSaveAs).Show

If Not Application.Dialogs(xlDialogSaveAs).Show Then
Exit Sub
Else
' the rest of the code goes here

end if
end sub
Click to expand...


Because you call it twice. Dump the first Application.Dialogs statement.
 
Tendresse,

If the macros are in Master.xls, then when having clicked a toolbar button, they'll get
prompted "This workbook contains macros" as it opens. If you save Master.xls as an add-in,
it'll already be open. But now all users must have Master.xls set up as an add-in.

Since all the users are to have access to all the macros with any saved-as copy of Calendar,
it'll be much simpler if they're in the workbook. If the calendar, by chance, needs only to
be scrolled horizontally, but not vertically, you could put the buttons in the first column,
and have it frozen. If not, read on.

We know we can't use a toolbar button, and there are too many keyboard shortcuts to fool
with. (I didn't know if it'd be just you using the macros, or all the users, at the time I
suggested them). Since the buttons are many, and take up too much room, you could have a
single button that takes them to a menu sheet containing the buttons. A hyperlink is a quick
way of setting that up. The menu sheet could be set up with no gridlines, background colors
in the cells, borders to group various buttons, and it would look quite nice. There could
be a button on the menu sheet that takes them back to whatever sheet they were in when they
started the process, or that could be automatic, when the macro they've run finishes.

For an even more professional look, you could create a userform (a dialog box) that has all
the buttons you need, with a single button on the sheets() to display it. It will appear
over the active sheet, and can be quite large, and when the user has done what needs to be
done, it can be dismissed (in code). You create the userform in the VBE. This solution
requires a bit more macro programming -- you have to pretty much lead it by the hand to get
stuff to happen.

As for the Save As dialog appearing twice, it got called in each of these lines:
Application.Dialogs(xlDialogSaveAs).Show

If Not Application.Dialogs(xlDialogSaveAs).Show Then
Click to expand...

You can dispense with the first one.
 
Tendresse,

It occurs to me that many of your buttons, unlike the Save-As one you asked about, may do
things directly on the worksheet. In that case, using a separate sheet or a userform as I
suggested for macro buttons may not be convenient for users. If that's the case, you'll
probably want to go ahead with a toolbar using Dave's suggestion of building one on the fly.
 
Hi Earl,
Thank you very very much for all your valuable assistance. Even if your
ideas about using a separate sheet or userform may not suit my current
project, i'm sure they may become beneficial for a future one. You taught me
something new i never knew before and i'm sure it will come handy one day.
Thanks Again,
Tendresse :)
 
Dave, thank you very much for your help. YES, i think your idea would make my
life much easier. One never stops learning :)
have a good day
Tendresse
 
Back
Top