Column/cell protection

[Alwyn]

I have a worksheet containing two columns of Command
Buttons sourced from the Tool Box menu. They are simple
GoTo command buttons with the following syntax:
Private Sub CommandButton1_Click()
Application.Goto Reference:=Worksheets("Sheet1").Range
("N8:V15"), _
Scroll:=True
End Sub
When I unlock the cells containing the buttons
(Format/cells/protection tab/clear locked check box),and
apply worksheet protection the buttons fail and return
error #1004. With no worksheet protection the buttons
work fine.
How can I protect the heart of my worksheet to prevent
inadvertent changes to formulas and still retain the
functionality of the Goto buttons?

 

[Matthew Connor]

I have a worksheet containing two columns of Command
Buttons sourced from the Tool Box menu. They are simple
GoTo command buttons with the following syntax:
Private Sub CommandButton1_Click()
Application.Goto Reference:=Worksheets("Sheet1").Range
("N8:V15"), _
Scroll:=True
End Sub
When I unlock the cells containing the buttons
(Format/cells/protection tab/clear locked check box),and
apply worksheet protection the buttons fail and return
error #1004. With no worksheet protection the buttons
work fine.
How can I protect the heart of my worksheet to prevent
inadvertent changes to formulas and still retain the
functionality of the Goto buttons?

I created a new workbook and tried to replicate your error. I
couldn't' - the code worked fine with the sheet protected and unprotected.

Are there any event macros that are in the workbook? This sub could be
triggering them.

When the error appears, you have the choice of End, Debug, Help. If
you choice Debug you will be brought to the VBA code (although not
always exactly where the error is occuring - try hitting F8 until the
error occurs again - that is the troublesome line).

If the error really occurs on the Application.Goto line, I'm afraid
I've run out of ideas.


Hope this helps,

Matthew

 

[Alwyn]

-----Original Message-----

I created a new workbook and tried to replicate your error. I
couldn't' - the code worked fine with the sheet protected and unprotected.

Are there any event macros that are in the workbook? This sub could be
triggering them.

When the error appears, you have the choice of End, Debug, Help. If
you choice Debug you will be brought to the VBA code (although not
always exactly where the error is occuring - try hitting F8 until the
error occurs again - that is the troublesome line).

If the error really occurs on the Application.Goto line, I'm afraid
I've run out of ideas.


Hope this helps,

Matthew

Thanks Matthew - I'm still puzzled by this problem - the

line "Application.Goto Reference:=Worksheets
("Sheet1").Range("A8")" (or any other range), and the
line " Scroll:=True" " are both highlighted when I
attempt debug. I'm sure doing something wrong - but I'll
keep working on it - Alwyn

 

[Dave Peterson]

Are you using xl97?

If yes, try changing the .takefocusonclick property to false for each of the
command buttons. (If you use a different control from the control toolbox
toolbar that doesn't have this property, you could add:

activecell.activate

to the top of your code. (Actually, this'll work for your commandbuttons, too.)

This bug was fixed in xl2k.