Assign new macro to buttons

John · Mar 17th, 2002, 09:58 PM

I have a workbook containing 33 worksheets and a very large amount of macro code. This workbook is about 1.4 mb.

By making various entries and choices the user has a minimum of 3 and a maximum of 4 pages that they need to save. In order to reduce file size I am copying these pages to a new workbook(template) and then saving this new workbook. The original workbook is not saved. The problem is that when the user opens the saved book if they click on any of the buttons they get a message saying (workbook1) macro not found. I need to write a macro that would assign a new macro to the buttons. (a pop up message telling the user they were disabled).

My problem here is that sometimes there will be 4 sheets and sometimes only 3. The sheet names will vary as will the number of buttons on each sheet. Another problem I am finding is that since I sometimes did a cut and paste of buttons often more than one is called button one (these were created from forms) so I could have say 3 button 1’s, 1 button 7 etc (incidentally can you rename these buttons).

Is there a simple way to produce a macro that will select each sheet in turn, determine the number of buttons on that sheet then assign a new macro to them?

Thanks

Anne Troy · Mar 18th, 2002, 12:16 AM

Go hear to learn how:

http://www.cpearson.com/excel/menus.htm#vba

And here for some toys:

http://j-walk.com/ss/excel/tips/tip67.htm

Dave Hawley · Mar 18th, 2002, 12:28 AM

As you will no doubt see there are many ways to do this and unfortnately most over-complicate what should be very simple.

Rather that Copy the Worsheets to another book jut delete the ones you don't wnat and then save it as it's new name. it's that easy!

The use of:

Application.DisplayAlerts=False
Sheet2.Delete
Application.DisplayAlerts=True

Will prevent the sheet delete warining.

Mar 18th, 2002, 12:16 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Go hear to learn how: http://www.cpearson.com/excel/menus.htm#vba And here for some toys: http://j-walk.com/ss/excel/tips/tip67.htm __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 17th, 2002, 09:58 PM	#1
John New Member Join Date: Mar 2002 Location: New Zealand Posts: 24	I have a workbook containing 33 worksheets and a very large amount of macro code. This workbook is about 1.4 mb. By making various entries and choices the user has a minimum of 3 and a maximum of 4 pages that they need to save. In order to reduce file size I am copying these pages to a new workbook(template) and then saving this new workbook. The original workbook is not saved. The problem is that when the user opens the saved book if they click on any of the buttons they get a message saying (workbook1) macro not found. I need to write a macro that would assign a new macro to the buttons. (a pop up message telling the user they were disabled). My problem here is that sometimes there will be 4 sheets and sometimes only 3. The sheet names will vary as will the number of buttons on each sheet. Another problem I am finding is that since I sometimes did a cut and paste of buttons often more than one is called button one (these were created from forms) so I could have say 3 button 1’s, 1 button 7 etc (incidentally can you rename these buttons). Is there a simple way to produce a macro that will select each sheet in turn, determine the number of buttons on that sheet then assign a new macro to them? Thanks

Mar 18th, 2002, 12:28 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	As you will no doubt see there are many ways to do this and unfortnately most over-complicate what should be very simple. Rather that Copy the Worsheets to another book jut delete the ones you don't wnat and then save it as it's new name. it's that easy! The use of: Application.DisplayAlerts=False Sheet2.Delete Application.DisplayAlerts=True Will prevent the sheet delete warining.