MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to make an online form unscrollable


Posted by Bret on March 10, 2001 5:45 AM

I'm using an Excel sheet to disply an interactive
equipment status screen. The only problem is that
users sometimes accidentally hit the arrow keys
causing the screen to move. Does anyone know how I can
either get rid of all those thousands of extra cells,
or just stop the ability to scroll to the rest of the
sheet with the arrow keys? I placed a link to my
template at (link outdated)
if you want to have a look at it.

Locking the window seems like it would work, but it
comes up as only half the screen when I do that.


Posted by Dave Hawley on March 10, 2001 6:05 AM

Hi Bret

Here are 2 ways. One with VBA and one without.

With VBA:
Right click on the sheet name tab and paste in this code:

Private Sub Worksheet_Activate()
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:L20"
End SubEnd Sub

This will limit the scroll are to the defined range. To get it back to normal use:
Me.ScrollArea = ""

Non VBA method:

select the entire row on the bottom of your screen, say row 22

Hold down Ctrl+Shift and push the down arrow.
Go to Format>Row>Hide.

Select the entire Column on the left of your screen, say Column L

Hold down Ctrl+Shift and push the right arrow.
Go to Format>Column>Hide.

Apply protection if needed.


Dave


OzGrid Business Applications

Posted by Bret on March 10, 2001 10:48 PM

Dave,

Thanks a bunch for your help. The selecting row/column method worked great!
But I couldn't get the code entry part to work; it said there were errors,
so I fgure I was pasting in the code incorrectly.
Was I supposed to remove the stuff that was already there(a first line, don't
remember what it said, and a last line, which said EndSub. It's amazing that the
method of selecting all the rows and columns (ctrl-shift-arrow key)
was not to be found anywhere in the help files that I could find, and also the
templates that were included also could be scrolled into infinity.

So thanks again, Dave for helping a rank novice (me) to idiot-proof my little application.

Bret


Posted by Dave Hawley on March 10, 2001 11:35 PM

Bret, dud code!

Thanks a bunch for your help. The selecting row/column method worked great!

Bret, I see why it didn't work! It somehow go messed up in the transfer. Follow the same instructions, but use this code instead if interested:

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:L20"
End Sub


Dave


OzGrid Business Applications