Archive of Mr Excel Message Board


Back to Controls in Excel archive index
Back to archive home

Button on every sheet

Posted by Ulf on January 19, 2000 12:52 AM
Hi!
I want a spinbutton to appear on every page in a workbook except the first
and last one but I don't want to write the code for every single sheet. The
spinbutton's function will be to go to the next or previous page when clicked
so the code is the same for all buttons.
I would also like the buttons to NOT scroll with the page.
I would also like to limit the number of characters written in a cell WHEN they
are typed i.e. before ENTER is hit.
Is there a way to solve these problems?

Thanks!


Re: Button on every sheet

Posted by Chris on January 19, 2000 5:40 AM
Assign all the spin buttons to the same 2 macros that look like this:

Sub GoForward()
Activesheet.next.select
End Sub

Sub GoBack()
Activesheet.previous.select
End SUb

If you put the button at the top of the sheet, you can use freezepanes to make sure it's always visible.

Nothing has come to mind on how to limit the number of characters while the user is typing (at least in a cell anyway). You could certainly inform the user after the enter key has been hit.

Chris


Thanks, but these solutions does not work for me. (txt)

Posted by Ulf on January 19, 2000 9:51 PM

Thanks for the code. It works better than the one I'm using now.

I don't want to put the buttons on the sheets one by one. Let's say I have 1000 sheets. It will take a lot of time...

This is not applicable in my application.


Re: Thanks, but these solutions does not work for me. (txt)

Posted by kaiowas on January 20, 2000 5:46 AM

A quick and dirty fix would be to assign the above macros to a pair of custom toolbar buttons



Re: Thanks, but these solutions does not work for me. (txt)

Posted by Chris on January 20, 2000 6:33 AM
To put the buttons on every sheet, just copy one, select all the sheets, and choose paste.

Why would you have a thousand sheets anyway? It doesn't sound like Excel is likely to be the best application for you.

Why can't you freeze the top row?


Re: Thanks, but these solutions does not work for me. (txt)

Posted by Ulf on January 21, 2000 1:04 AM

Thanks, didn't know you could do that. Why would you have a thousand sheets anyway? It doesn't sound like Excel is likely to be the best application for you.

I don't have thousands of sheets, but I have so many (15-20) that I don't want to put the buttons on every sheet by hand.
Is there a way to paste the buttons on a special "coordinate"? The cells are not the same on all sheets. Why can't you freeze the top row?

I can, but I don't want the buttons on the top row.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.