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 :)


"Earl Kiosterud" wrote:

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.
--
Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
"Earl Kiosterud" wrote in message
...
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

You can dispense with the first one.
--
Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
"Tendresse" wrote in message
...
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



"Tendresse" wrote:

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





"Earl Kiosterud" wrote:

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...
-----------------------------------------------------------------------
"Tendresse" wrote in message
...
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