Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
This is a shot in the dark, but can't you go to the trash can and put it back
-- William<"M" "Tendresse" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 "Sunday88310" wrote: This is a shot in the dark, but can't you go to the trash can and put it back -- William<"M" "Tendresse" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
I see your point.
---------------------------------------------------------- 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! ------------------------------------------------------------------------------------ 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" "Tendresse" wrote: 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 "Sunday88310" wrote: This is a shot in the dark, but can't you go to the trash can and put it back -- William<"M" "Tendresse" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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. "Sunday88310" wrote: I see your point. ---------------------------------------------------------- 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! ------------------------------------------------------------------------------------ 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" "Tendresse" wrote: 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 "Sunday88310" wrote: This is a shot in the dark, but can't you go to the trash can and put it back -- William<"M" "Tendresse" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
I would give serious thought to writing that program as a Dictator
Application and creating the add-ins -- William<"M" "Tendresse" wrote: 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. "Sunday88310" wrote: I see your point. ---------------------------------------------------------- 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! ------------------------------------------------------------------------------------ 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" "Tendresse" wrote: 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 "Sunday88310" wrote: This is a shot in the dark, but can't you go to the trash can and put it back -- William<"M" "Tendresse" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 "Sunday88310" wrote: I would give serious thought to writing that program as a Dictator Application and creating the add-ins -- William<"M" "Tendresse" wrote: 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. "Sunday88310" wrote: I see your point. ---------------------------------------------------------- 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! ------------------------------------------------------------------------------------ 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" "Tendresse" wrote: 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 "Sunday88310" wrote: This is a shot in the dark, but can't you go to the trash can and put it back -- William<"M" "Tendresse" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
"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? Yes, create an addin and put the code there. http://msdn.microsoft.com/library/de...ificaddins.asp Building Application Specific Add-Ins http://msdn.microsoft.com/library/de...xceladdins.asp Excel Add-Ins http://msdn.microsoft.com/library/de...slibraries.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 Because you call it twice. Dump the first Application.Dialogs statement. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the workbook is closed. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) Tendresse wrote: 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 -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paths for macros
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 "Dave Peterson" wrote: Your life will become much simpler if you include code to create the toolbar when the workbook is opened and include code to destroy the toolbar when the workbook is closed. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) Tendresse wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paths | New Users to Excel | |||
I hate paths! | Excel Discussion (Misc queries) | |||
DISPLAYING THE LINK PATHS | Excel Worksheet Functions | |||
Help with filenames and paths. | Excel Discussion (Misc queries) | |||
file paths | Excel Discussion (Misc queries) |