MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro that works 90% of the time


Posted by Philip on March 02, 2001 11:51 AM

Hi all,
I am stumped. I am working on a project that has about 50 or 60 worksheets in it. To make it easy to get around, as the first page I decided to add a clickable Table of Contents. This basically consists of 50 text boxes that each have a separate macro assigned. When you click on the text box that says, "Chicago" the macro transports the user to the worksheet called "Chicago" via the following code:

Sub Macro4()
Sheets("Chicago").Select
End Sub

This works great!....90% of the time. This workbook was emailed out to 40 people and out of those 40 people, 4 of them have written back saying that when they click on ANY of the 50 text boxes they get an illegal operation error and Excel shuts down. I have had 2 of them email me back their copies of the workbooks so I could see what is happening and even their copies work fine for me. I am using Excel 2000 but I have tested this in 2000 and 97 and both seem to work fine. I have searched all over the web looking for a possible solution but have yet to find one.

Anyone have any bright ideas?


Posted by David Hawley on March 02, 2001 6:13 PM

Hi Philip

The error they are recieving is most likely due to a problem on their PC and not your macro. Tell them to run Scan Disks and then Disk Deframenter.


Are you aware that you can right click on the scroll arrows to the left of your sheet name tabs and you will get a pop-up list of all sheets ?


...Or you could use a list of Hyperlinks to allow users to get to worksheets rather than Macros attached to Textboxes.


Dave


Dave

OzGrid Business Applications