Macro's assigned to a custom toolbar button don't run after renaming the worksheet

**marcelw** · 11-23-2007, 12:02 PM

I have to simple macros assigned to two different buttons on a custom toolbar. It works fine until the location of the worksheet (or the worksheet name) is changed to another directory. When opening the worksheet and using the toolbar, an error message is shown that the macro couldn´t be found on the original file location.

Of course, the macros are in the workbook and can be reassigned to the toolbar again....

Anyone knows how to make these macro mappings independent of the worksheet's file location (or worksheet´s name)?

Marcel

**shg** · 11-23-2007, 12:06 PM

Do you assign the macros in code or via the user interface? If the former, can you post the code?

**marcelw** · 11-23-2007, 12:26 PM

The macro is written in code (see below)
I've assigned it to a toolbar using the user interface (using the 'assign macro....' menu option.

Marcel

Sub BekoNavigeerNaarTegoedenEnRechten()
'
'Deze routine brengt de active cell terug naar het tabblad tegoeden en rechten en positioneert op de juiste plek.
    nummer = ActiveSheet.Range("AD1").Value
    
    'Neem aan dat het eerste sheet altijd 'Tegoeden en Rechten' is.
    Worksheets(1).Activate
    
    Worksheets(1).Range("A1").Activate
    
    With ActiveSheet.Range("a2:a300")
        Set c = .Find(nummer, LookIn:=xlValues)
        If Not c Is Nothing Then
            ActiveSheet.Range(c.Address).Activate
        Else
            response = MsgBox("Fout: Personeelsnummer " & nummer & " niet gevonden in tabel 'Tegoeden en Rechten'", vbExclamation + vbOKOnly)
        End If
    End With

End Sub

**mudraker** · 11-23-2007, 05:58 PM

marcelw

Please read forum rules & wrap your VBA code

A thread with the rules is available at the top of each forum or see links below

If you do not understand the VBA code wrap instructions have a look at my last reply in this thread
http://www.excelforum.com/showthread.php?t=583950

**Southernw2002** · 11-23-2007, 07:47 PM

I have a similar problem .. but for me the macro still runs but if I use the toobar icon it refers back to the old worksheet

**shg** · 11-23-2007, 08:37 PM

Sorry for the delay in responding, and please do edit your post to add code tags as mudraker requests.

I reckon you create hard-coded links to the workbook path when you assign a macro to a toolbar button via the UI (I know this is true for macros in add-ins). Try assigning the button in the workbook open event.