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!

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

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

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

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.

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

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

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

Posted by Chris on January 20, 2000 6:33 AM

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

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?

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

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

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.