Excel 2007, Macros, Quick access toolbar, buttons don't work!

Consider the following scenario. You add a macro to the Quick Access Toolbar in Microsoft Office Excel 2007. Then you close Excel 2007. Later, when you are working in Excel 2007, you click the button for a macro. In this scenario, you receive the following error message: Cannot run the macro 'macroname'. The macro may not be available in this workbook or all macros may be disabled

I looked at http://support.microsoft.com/kb/930076/en-us but it doesn't fix my problem.

I copied the macro modules I created in Office 2003 personal file to Office 2007 personal file in the vba project. Then I created buttons for each in the quick access toolbar.

But when I open a spreadsheet the buttons do not work and come back with the error message "Cannot run the macro 'macroname'. The macro may not be available in this workbook or all macros may be disabled"

I tried enabling all macro's to no effect. I can run the macros fine, manually from Alt+F8 but this defeats the whole purpose of having the macros in the first place (saves time). 

In 2003 this isn't an issue, buttons work fine.

In 2007 however they don't want to work!

Office 2003 is running on XP (NTFS) at work.

Office 2007 Enterprise (HUP) is running on Vista Home Premium (32 bit) at home.

I've got my excel files on a 4gb U3 Toshiba Flash Drive (Security enabled).

How do I fix this?

petedavo

There should be no issue with the macros firing from a QAT button, providing the workbook is open (albeit hidden). I have suspicions that the code is in the 'wrong' personal file or you do not have it in an xlStart folder that Excel is recognising. This can vary depending on your installation and version.

To test whether you have the correct version/placing do the following:

1. Open XL2007 and go to the View Tab>Windows Group. Is the 'Unhide' button 'lit'? If not, you do not have a personal.xls open with Excel and there is your issue. If you do, click the button and check the name of the file in the dialog. It should be either personal.xls or personal.xlsb
2. With the knowledge gained from '1', open a search window and in advanced search enter personal.* and ask it to search for documents and INCLUDE hidden locations. Allow it to search completely, even if one shows up straight away.
3. Once the search is finished, right click each file and select 'Open location'. Cut the file from it's location. Noting where it was and paste it on your desktop or somewhere. (Do this for all personal.xls? found in your search)
4. Close Excel and re-open
5. Now in Excel choose to run a new macro (by pressing the button on the status commandbar) and select to store it in your personal workbook.
6. Just type Test in A1 and end the macro.
7. Now close Excel, answering 'Yes' when asked to save the personal workbook (second prompt). (You do not need to save the workbook you typed 'test' into)
8. Now do a search, exactly as '2' and find the new personal.xlsb file.
9. Right-click this file and select open location. This is the location to place your personal.xls or personal.xlsb with your toolbar code. (In Vista), remember it will likely be a different location in Windows XP.

This sounds a long winded way, but you need to be sure exactly where your version/operating system combination 'thinks' it should be, not where it is on some other combination.

As long as you have not changed it you should also get a clue from the Autosave location under Office Button>Excel options>Save. (Just add xlStart to the end, to get the personal.xlsb location), mine is C:\Users\UserName\AppData\Roaming\Microsoft\Excel\ (the username is of course my logon, not what you see here), so it would be (and is) C:\Users\UserName\AppData\Roaming\Microsoft\Excel\xlStart\

I do not know why you are mentioning the excel files being on the stick, as, unless you have told both versions of Excel that the 'start-up' folder for Excel is on your stick, excel certainly won't look there for your code.

Remember: Excel can only run VBA code in open workbooks, so I suspect your 'correct' personal file is either not open or you have the code in the wrong one.

For the OP

See

http://www.rondebruin.nl/qat.htm

The personal.xlsb file is in the correct place and starts with xl.

I even tried substituting xlsb with an xlsm version to no effect.

However when I view the macros dialog box from the developer tab, two of the modules have "personal.xlsb!" prefixed to the module name, so I woder if this means that they will only work in that file and not globally?  

I thought that openning the spreadsheet from the pen drive might disable macros for some security reason, likewise because they are outlook msg attachments, but I enabled macros in Outlook.

I think the problem might be in the actual code, so I've copied the 3 macros below.

Sub Unlock_and_Copy()

Dim Counter As Long

Dim HighRow As Long

'ActiveSheet.Unprotect ("8ball")

'Find out how many rows are in that column
     For Counter = 16 To 140
        If Cells(Counter, 4).Value = "" Then
               
        Else
       
       
                If Cells(Counter, 4).Value <> "TOTALS" Then
               
                    If Cells(Counter, 4).Value <> "Name/ PD No/ Date/ Title" Then
                   
               
                        HighRow = Counter
                    End If
                   
                End If
                    
       
        End If
       
    Next Counter
 
 

 Range(Cells(16, 1), Cells(HighRow, 22)).Copy

End Sub

Sub Pastespecial()

    Selection.Pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Sub ColouredRows()
 
Dim Counter As Long
Dim Counter2 As Long
Dim MainColumn As String
Dim HighRow As Long
Dim PD1 As String
Dim PD2 As String
PD1 = 0
PD2 = 0
Dim LightBlue, LightYellow, LightGreen, PaleBlue, Rose, Lavender As Integer
LightBlue = 34
LightYellow = 36
Rose = 38
Lavender = 39
LightGreen = 35
PaleBlue = 37
Dim Colour1 As Integer
Dim Colour2 As Integer
Colour1 = PaleBlue
Colour2 = LightGreen
 
'Ask for the column to do everything for
MainColumn = InputBox("Enter the column containing the data you wish to use", Title)

'Find out how many rows are in that column
     For Counter = 1 To 40000
        If Cells(Counter, 2).Value = "" Then
               
        Else
               
                HighRow = Counter
       
        End If
       
    Next Counter
 

 'Now change the colours
    For Counter = 1 To HighRow
              
        If Counter = 1 Then
        Rows(Counter).Interior.ColorIndex = Colour1
           
        Else
            PD1 = Cells(Counter, MainColumn).Value
            PD2 = Cells(Counter - 1, MainColumn).Value
           
            If PD1 = PD2 Then
                If Cells(Counter - 1, MainColumn).Interior.ColorIndex = Colour1 Then
                    Rows(Counter).Interior.ColorIndex = Colour1
                Else
                    Rows(Counter).Interior.ColorIndex = Colour2
                End If
            Else
                If Cells(Counter - 1, MainColumn).Interior.ColorIndex = Colour1 Then
                    Rows(Counter).Interior.ColorIndex = Colour2
                Else
                    Rows(Counter).Interior.ColorIndex = Colour1
                End If
            End If
        End If
           
    Next Counter
End Sub

petedavo

You will need to prefix them with the workbook name, the simplest way to attribute code to the QAT button is to right click on it select customize, then macros from the drop-downa and just add them This way I the Personal.xlsb! will be added automatically.

I am confused as to the 'pen drive and Outlook' piece. So long as these ar saved off the attachment as simple XL files (any type) and the personal.xlsb is in the correct place on the hard drive, that would not be your issue

Thanks very muchly.

Everything should work because according to all the responses so far, all three macros are set up correctly (exactly as suggested) already and run fine manually. It's just the buttons won't work. There must be a bug in Office Enterprise HUP 2007 SP1 then.

 You might want to look at the qat file itself to make sure it is correct.  It should be since you customized it from the application, but it wouldn't hurt to check.

This could be in various places depending on your OS.  In XP my file is in:

'C:\Documents and Settings\zfraile\Local Settings\Application Data\Microsoft\Office\Excel.qat

In Vista it's in:

C:\Users\zfraile\AppData\Local\Microsoft\Office\Excel.qat

Open it up in notepad and look for the line that hooks up your macro button.  A working example from mine is:

<mso:button idQ="x1:PERSONAL.XLS_ToggleHeadings_1" visible="true" label="Toggle Headings" onAction="PERSONAL.XLS!ToggleHeadings" imageMso="CharacterShading"/>

Yours should look the same, but with the PERSONAL.XLS pointed to your PERSONAL.XLSB file, the label would be what you want in the tooltip, the onAction would refer to your macro.  The imageMso is the icon name.  I don't think the directory matters so long as the file with the macro is open.

Everyone is right in a way.

Discovered the problem yesterday. The QAT buttons were looking the macros up in the wrong place as everyone has guessed.

 It's not still you restart excel some time later that int becomes obvious.

It appears that in opening the old XL 2003 personal.xls file (that I copied onto the pen drive), to copy the modules into the 2007 personal.xlsb file (on the hard drive at home), that some sort of "ghost" file is temporarily created in 'my documents'. If the QAT buttons are created straight away after copying the macros across, then the buttons will actually point to the ghost. Everything 'looks' fine but the behaviour on the buttons and keyboard shortcuts are as previously stated.

I am currently having the same problem.  Can you please elaborate on how/what you did to fix the problem?  Thank you. Jeff

Jeff

It is tough to know what the answer was, but I still suspect that the wrong personal.xls or personal.xlsb was being opened.

Have you tried following the steps I gave in my earlier post?

If we start there, we can run through other steps if it doesn't work.

Let us know

In my case, I noticed that Office 2003 did the same thing when I got back to work after holidays, but the beaut thing about 2003 compared to 2007 is that it actually told you where the button was looking for the macro in the error message!

That gave me the clue that I needed.

When I went back onto Excel 2007 at home, I simply went into the properties of the buttons and in the panel of available macros - viola the personal.xlsb! prefixed versions were there! and I simply changed the button to those. Failing that, you could simply remove them and make new buttons.

It's just that if you don't restart your computer and create buttons immediately after copying the macros, then the buttons will be looking for the ghost personal file, and not the one in xlstart! 

On a somewhat related side note, I noticed another issue with the personal macro workbook come up in one of my Excel 2007 installs.  For some reason, the personal.xls file stopped opening automatically.  I could open it manually and it would work, but it no longer auto-opened.  This is after no changes to the personal.xls file, updates to Excel, or change in preference.  Also, other items in the same xlstart directory do open.  The answer is, beats me.  I let Excel create a personal.xlsm file for me (by recording a macro to it and letting Excel make the file).  I moved all my modules and workbook code to the xlsm and that works fine.  The personal.xls still works fine in my other 2 installs.  The others are on Vista and the non-working one is on XP, but I don't think that matters.

Hi all, love the site, first time poster

//*subject : distributing default excel workbook with  buttons on the qat and macros associated

Zfraile, I think your point might help me out but maybe you could elaborate.  I have office xp and the it support guy has vista...

I have my qat customized and macros associated as a default workbook and everything works fine. Now I want to distribute my default excel file so other users can make it their default workbook.

So i sent my excel file to IT support. Certain features of my default workbook are there (he is using vista), but the icons on my qat and asscociated macros are gone.  Is the problem what you mention above?

*** SOLUTION ***

Excel 2007   trully destroys the ability to call marcros from a quick launch bar.  The problem is that the buttons point to local Macro names only.  If don't have the particular XLS file open as primary, then it does not work.   Excel 2003 would embed the source XLS file name so this problem never came up.

I found through various searches that Excel 2007 stores the ribon setting in Excel.qat file.  

     In Windows XP you will find it here:
     C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat

     In Vista you can find it here:
     C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat

Solution is to:

   1) Add your macro per Excel 2007 instructions. 

   2) Close all Excel 2007 instances.

   3) Open this file in a text editor ( such as emacs ) and fully qualify the INTENDED_MACRO_NAME.  For example.  Change this:

                <mso:button idQ="x1:INTENDED_MARCRONAME_1" visible="true" label="INTENDED MACRO NAME"

                 onAction="INTENDED_MACRO_NAME"

                 imageMso="DatabaseCopyDatabaseFile"/>

        to this:

                <mso:button idQ="x1:INTENDED_MARCRONAME_1" visible="true" label="INTENDED MACRO NAME"

                onAction="'U:\Top_Directory\Rest_of_Path\File_with_Macros.xls'!INTENDED_MACRO_NAME"

                imageMso="DatabaseCopyDatabaseFile"/>

Now, open Excel 2007.  Your macros WORK.

I've been going through different posts, looking to see if someone has come up with a better solution that what I have found to have a toolbar to use macros in Excel 2007. I haven't found one, and everyone seems to be screaming for an answer, so I'll share what I did. It took alot of research and trial and error, but this works. This is easier to do if you're able to share your files on a network. You also need access to Office 2003 to make this work. In Excel 2003: 1. Save your macros in a new workbook. Close Excel, then open the workbook with your macros in it. 2. Create a macro toolbar, and make sure you attach the macros from the newly created workbook. Do not attach the macros you have in your personal workbook. 3. Attach the toolbar to a new workbook, and save it. 4. Open the workbook with the attached toolbar in Excel 2007. The macro toolbar will be on the Add-Ins tab. You can run the macros from there or create a shortcut to the toolbar on the Quick Access Toolbar. This is very important. To run the macros this way, the file path to the workbook that has your macros in it has to be EXACTLY the same for Excel '03 and Excel '07. This is much easier to accomplish if you share the macros across a network. Try this out and tell me what you think and ask if you have any questions.