Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fix multiple 'assign macro' references broken due to moving sheet
I'm mystified as to why this happened, but I'm hoping someone can help me fix
it in an easy way... I have a workbook with multiple sheets, and many buttons with assigned macros on different sheets for navigation, filtering, etc. I created a new blank sheet within the workbook for a throwaway calculation, and then decided I didn't want it in that book so right clicked and moved to a new book. That was fine and I proceeded with my calculating for a while. Then I went back to the original book and tried to use a button, but got an error that the macro did not exist, referencing 'Book12', the new book created with my calculation sheet. I figured something odd had happened with moving the sheet, so I moved it back again, but now clicking a button gives me 'Book12.htm cannot be found...' I really don't want to have to go through each one and reassign all the macros one at a time - is there any way of fixing this straightforwardly? Note: The macros themselves are saved in a module within the workbook, not on any individual sheet. This is what makes the whole thing so odd. Removing the 'Book12!' from the start of each reference makes it work fine again. Thanks in advance for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fix multiple 'assign macro' references broken due to moving sheet
I can't reproduce what you describe, most odd!
Maybe this macro will reset your onAction links Sub test() Dim btn As Button Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets For Each btn In ws.Buttons btn.OnAction = Replace(btn.OnAction, "Book12!", "") Next Next End Sub Regards, Peter T "Tara H" wrote in message ... I'm mystified as to why this happened, but I'm hoping someone can help me fix it in an easy way... I have a workbook with multiple sheets, and many buttons with assigned macros on different sheets for navigation, filtering, etc. I created a new blank sheet within the workbook for a throwaway calculation, and then decided I didn't want it in that book so right clicked and moved to a new book. That was fine and I proceeded with my calculating for a while. Then I went back to the original book and tried to use a button, but got an error that the macro did not exist, referencing 'Book12', the new book created with my calculation sheet. I figured something odd had happened with moving the sheet, so I moved it back again, but now clicking a button gives me 'Book12.htm cannot be found...' I really don't want to have to go through each one and reassign all the macros one at a time - is there any way of fixing this straightforwardly? Note: The macros themselves are saved in a module within the workbook, not on any individual sheet. This is what makes the whole thing so odd. Removing the 'Book12!' from the start of each reference makes it work fine again. Thanks in advance for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple sheet references in one function | Excel Worksheet Functions | |||
Assign Macro with Hidden Sheet | Excel Worksheet Functions | |||
Named references broken on copied worksheet | Excel Discussion (Misc queries) | |||
Assign macro broken after reference removed | Excel Programming | |||
Broken References | Excel Programming |