PDA

View Full Version : Solved: Can't Assign Macro to Toolbar Icons



Cyberdude
10-27-2006, 07:55 PM
I've been having a LOT of trouble with Excel lately (like discovering the presence of over 20,000 invisible (but apparently defined) "LINE"s on my sheet with embedded charts. Had to rebuild that one 'cause I couldn't delete any of the lines. Anyway, today I discovered that three of my most treasured toolbar icons had disappeared, so I've been trying to reconstruct them. My immediate problem is that I can't assign macros resident in Personal.xls to any of the new icons.

I can never remember the names of the (in my case) three toolbars at the top of the Excel screen, but the icons I create usually go on the top two (third one is full). The three I'm trying to create are:
1. Display the Conditional Formatting dialog box.
2. Display the Defined Names dialog box.
3. Display the Paste Special dialog box.

I have a short macro for each function. In addition to having them in Personal.xls, I have them in a couple of other workbooks that I've been testing with. When I try to assign a macro from Personal.xls to a new icon, everything goes smoothly, except the assignment doesn't occur. I even included the full path to Personal as a prefix to the macro name, but when I tried using it, I got a message saying it couldn't be found. Yes, I checked and rechecked the spelling of everything.

When I first started doing this, the macro names in the workbook I was using at the time did take effect, but when I started using the icon with another workbook, then it would open the original workbook to get it (of course). Keep in mind that I have been successfully using the icons that disappeared for years, now I'm having trouble recreating them.

Why don't I just drag the command from the customizing box to the toolbar? Well, I can't find one that displays the Defined Names dialog box. The other two insist of showing a too-long text name that exceeds my available space.

The main question is, why can't I assign a macro that's resident in Personal.xls?? :confused:

Jan Karel Pieterse
10-28-2006, 04:34 AM
Maybe your toolbar customisation file is corrupt. Look for a file named Excel.xlb (or similar) and rename it's extension to something like Excel.oldxlb. Then start Excel and try again.

Make sure you search includes hidden files and folders.

NB: What I have done is create a custom toolbar with all buttons I like, looking like the standard toolbar. Then I attached that toolbar to an Excel file. If I ever loose the toolbar, I simply reopen that particular excel file and I'm ready to go.

NBNB: No need to use a macro to open the pastespecial dialog, it has it's own button you can drag onto a toolbar. Look on the commands tab, edit section of the toolbar customisation dialog.

Cyberdude
10-28-2006, 11:52 AM
Thanks for the reply, JK! I found the Paste Special command you mentioned, and I am using it now ... but I don't like it. I have to use text (which I reduced to PSpcl), and that uses more space than an icon, but I am using it out of desperation.

I've wondered about doing what you suggest and make my own backup toolbar, if I can. I figured out a tacky workaround for the inability to assign a button to a macro in Personal.xls. I created a new, essentially empty workbook (named Icon Macros) that I put the macros into, then put the workbook in the same folder where I keep Personal.xls. Since Excel opens automatically everything it finds in that folder, I'm willing to live with a little overhead in order to assign the buttons to the macros in the empty workbook. So far it's working like a charm. Why it lets me assign the buttons to the empty workbook, but not to Personal.xls, is conjecture for the gods. As you said ... something has gotten corrupted.

Thanks again for the reply. :friends:

Jan Karel Pieterse
10-28-2006, 12:01 PM
Maybe it is your personal.xls that's in trouble. Have you ever tried ROb Bovey's code cleaner: www.appspro.com (http://www.appspro.com) ?

lucas
10-28-2006, 06:59 PM
This is why I use an addin with my menu and macro's included in the xla...
I do basically the same in Word. I learned from Gerry that the normal.dot can go bad and that can be a problem if I don't get it backed up so I use a global template in the Word startup directory with my menu and macro's for Word. Gets loaded every time.

SamT
10-29-2006, 08:50 AM
I recently read somewhere that, IIRC, custom toolbars are stored in C:/Windows/UserName.xlb

Cyberdude
10-31-2006, 02:19 PM
It occurred to me that it's rather easy to create a toolbar while in the "customizing" state. So as a first step of backup, I've duplicated all my Excel and VBE toolbars with meaningful names regarding positions. This was easy to do and has the advantage of being able to copy the many specially modified icons I've created. I've had to modify many of my icons because I can't see them well using the default colors. I hate having to recreate all of them when disaster strikes.

I also move files
Excel11.pip
OIS11.pip
VB11.pip
Word11.pip
Excel.xlb
Excel11.xlb
to a CD once a week. My problem is that I'm not sure how to use them to do a restore. Do ".pip" files work with ".xlb" files? Which ones contain that stuff I want to restore? :p

lucas
10-31-2006, 03:44 PM
Cyber,
Once again I use an addin....then that's all you have to back up...