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!
0
Utf
2/18/2010 11:15:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
655 Views

Similar Articles

[PageSpeed] 25

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" <TaraH@discussions.microsoft.com> wrote in message 
news:2F8832C3-9F4F-4C46-90AE-C6705AE1D43C@microsoft.com...
> 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! 


0
Peter
2/18/2010 12:11:18 PM
Reply:

Similar Artilces:

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

All macros failing part way through with different error messages
All the macros I have in various workbooks are failing - usually part-way through - with different error messages each time. When I check the debugger and try to edit the text, there seems no reason why there should be a problem. The macro has so far stopped on instructions to : - open a workbook - select a worksheet - paste a column in a different place Several times the macro has just stopped running without completing and without showing an error message. I cannot run a host of urgent daily reports without using the macros. Please help! NO way to help unless you tell us exactly...

Controlling multiple TCP connections from Outlook 2003 clients
I've noticed that Outlook 2003 clients can have multiple TCP connections to the mailbox store on Exchange. As the following web link claims: http://www.windowsitpro.com/Article/ArticleID/46319/46319.html "This behavior is by design. Outlook can open multiple connections to parallelize its data communications with the Exchange server. The actual number of connections will vary according to the version of Outlook that you're using and the mode in which you're using it. For Microsoft Office Outlook 2003 in Cached Exchange Mode, you'll see four connections for the mailbox, ...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

Excel moving horizontal columns to vertical
I am trying to find a simple way of moving data in horizontal columns (referencing the key in column 1) to vertical colums (still referenceing the same key). This would mean automatically repeating the values in column one for every entry in the moved columns. I can do this through the pivot table, but this is a long drawn out process. Any ideas I'm not sure if this will work for you, but you can try this: - Highlight & copy on the horizontal value you want to make vertical - Now highlight the vertical area you want these value to got - Right Click when highlighted & choose...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

Macros #34
I am trying to edit and create some macros in an excel spreadsheet that was created by someone else. There are macros for this spreadsheet but for some reason when I go to Tools -> Macro -> Macros it doesn't show any in the list. When I go to Tools -> Macro -> Visual Basic Editor there is code for the macros. I have some buttons on the spreadsheet that I want to assoicate with the macros but the buttons almost seem like they are locked, when i write click them nothing happens but a shortcut menu should appear. Is there maybe some special security on there I don't...

NNTP move to Exchange?
Hi, I'm having a problem with getting a NNTP feed from a INND 2.2.1 server to Exchange 2003; are there any known issues here? Has anyone done this type of migration before? What issues did you encounter? It seems as if Exchange just wants to pull down a few of the groups (of which we have over 100 internal) and even with that, not even pull all of the messages in from those few groups (it usually gets a few hundred messages). I'm using the basic NNTP wizards but everything looks to be configured correctly. Thanks, Dan ...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

Outlook 2003: Most messages are moved to junk folder
Guys, I am getting a lot of spam recently, at least looking at the numbers of mails in my junk folder. However, it turns out that these messages are not spam at all, but just regular messages (even from the same domain), that should remain in the regular inbox. Even when I identify a message as 'Not spam', then subsequent messages from that sender keep popping up in the junk folder. Obviously I have changed the junk mail settings to 'No automatic filtering' but Outlook keeps thinking it is smarter than I am. Any idea? Googled the internet already but couldn't find anoth...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Excel sheet not visible
When I open a local copy all I can see is the content of 1 cell in the menu bar, the work area is all blank gray. I can see the complete sheet (3 Pages) in print preview. Most users have no problem with this sheet. I have repaired the installation of Office 2000 and ran all updates, Same problem. It is something on the local PC that is causing this effect. Other sheets show up just fine. This is a very simple spred sheet with no formulas. Maybe it's just off the screen. Window|Arrange|Tiled (and then resize it the way you want) New group user 944 wrote: > > When I open a l...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...

Hiding sheet tab names
I created an automated workbook where I need to keep the sheet tab name hidden from the user. I went into Tools-Options-View and unchecke Sheet Tabs. Then I protected the workbook and the sheet yet the use can still go into Tools-Options-View and re-check the Sheet Tabs t view them again. How do I lock the user out of viewing the sheet tabs? :confused -- Message posted from http://www.ExcelForum.com You could use the "very hidden" property that prevents users from viewing hidden worksheets without using VBA: Dim ws2 As Worksheet For Each ws2 In ThisWorkbook.Worksheets If w...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

macro for invoice totals
I have this macro that inserts lines and copies a invoice header. Now I want to total the invoice. In the first row after a value in B in E the word totals must be inserted, H, I and K must be summed the amount of lines differ on each invoice but there is a heading from where it must be summed. CTNS(H), QTY(I), Total(K) Thanks a lot. Public Sub Deilv2() Dim LastRow As Long Dim row_index As Long Dim rng As Range Set rng = Range("B2:K25") Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 S...

Is there a way to sort a column without invoking a macro?
I am trying to get a feel for some probabilistic properties. For example, I am trying to look at the statistics of the intervals between randomly occurring pulses. I have no problem getting a column of uniformly distributed pulse times, but they are unsorted, Every time I recalculate with cmd-=, I get a new unsorted set of pulse times because of the use of the RAND function. Is there a way of taking this unsorted column and pasting it into another colun that would be sorted without invoking a macro? To simplify and be more specific consider the following. 1. In A1:A100 I have a set...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

Reminder Time vs Due By Field
I'm using O2003. For a contact, there is the Due by Field. There is also a Reminder Time field. If you update the Due By field, it updates the Reminder Time field. However, if you update the Reminder Time field, it does not update the Due By field. By default for a contact, you have access to the Due By field. The Reminder field is avaialble, but you have to manually add it. In Tasks, it seems to work the same in that if you update the Due By field, it updates the Reminder Time field. However, if you update the Reminder Time field, it does not update the Due By field. However, you have a...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Sheet event
Hi, Is there a way(technic) to find out, when a sheet is being called by a module, to find out the name of the module calling the sheet beside passing the name of the module has a parameter? (The objectif is to avoid the execution of an other module in the "Worksheet Active" event for performance purpose) Regards. On Mar 5, 3:56=A0am, Bobby <rpq...@gmail.com> wrote: > Hi, > Is there a way(technic) to find out, when a sheet is being called by a > module, to find out the name of the module calling the sheet beside > passing the name of the module has a parameter? (Th...