Way to make non-selectable worksheet?

R

robotman

I have a spreadsheet with 4 form buttons on it that will run different
macros. Is there any way to make it so the user can't click on any
part of the worksheet except for the buttons?

I don't want a message to pop up, I just want Excel to ignore if the
user tries to click on anything except the buttons.

Thanks!
 
E

Earl Kiosterud

Botman,

A couple of possibilities: You could protect the sheet (Tools -
Protection - Protect Sheet. You'll want no locked cells (Format - Cells -
Protection - Locked). In Excel 2002, you can specify that locked cells
cannot be selected. Can't do that in XL97 -- not sure about 2000.

Another is to set the scroll area to just one cell, which could be in back
of one of the buttons, or in a hidden row or column. This has to be done in
a macro, as it must be set each time the workbook is opened. In the
ThisWorkbook module:

Private Sub Workbook_Open()
Sheets("MySheet").ScrollArea = "D10"
End Sub
 
W

whicks

Hey Robot,

I'm guessing since you have buttons, there are macros assisgned to
them, and since you have macros, you are a little experienced with VBA.

Select ALL the cells on your sheet and select format, format cells, go
to the protection tab and check "locked". Then go to tools and protect
your sheet, under where it says, "Allow user of this worksheet to:"
uncheck everything and click ok.

In your VBA code, if macro action occurs on the sheet you just
protected, add the following to your code

This at the beginning of your code:

ActiveSheet.Unprotect

And at the end add:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True


Hope this will do the trick,

Willy
 
R

robotman

Just when I think I've mastered Excel VBA, I learn something new. I
didn't know I could reduce the scroll area to one cell. I'll have to
play with this.

Thanks for both your ideas! I think between the two ideas, I can make
Excel look pretty seamless.
 
Top