I have written a subroutine that is to respond to an excel error when the
user of my spreadsheet tries to change the value of a cell that has been
locked and protected. I am having trouble trying to access the event to call
the routine. Does anyone have any tips on how to access this response?
Also, I'd like to override excel's response (in the form of an error box) and
replace it with my own. Is this possible?
--
BRG

I don't think you're going to squeeze your subroutine in with any of excel's
events.

But as an alternative, why not stop the users from selecting cells that are
locked on that protected sheet.

If you protect the worksheet in code, you can toggle this setting:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

BRG wrote:

I have written a subroutine that is to respond to an excel error when the
user of my spreadsheet tries to change the value of a cell that has been
locked and protected. I am having trouble trying to access the event to call
the routine. Does anyone have any tips on how to access this response?
Also, I'd like to override excel's response (in the form of an error box) and
replace it with my own. Is this possible?
--
BRG

--

Dave Peterson

Thanks for the idea Dave. I have one problem with that if you can think of a
way around it.

The cells that are locked are linked to control toolbar option buttons and
check boxes. The original problem was that when the user tries to change a
selection for a column of locked buttons, the graphic of the button changes
(from selected to unselected or visa versa) but the value in the linked cell
(which is used for calculations) doesn't. The subroutine I have since
written goes through the OLE objects in the sheet and sets their value equal
to the value in their linked cell, so I'm now looking for an event that will
call the subrouting.

Maybe as an alternative along the lines of your suggestion, is there a way
to make the control toolbar objects unselectable?

--
BRG


"Dave Peterson" wrote:

I don't think you're going to squeeze your subroutine in with any of excel's
events.

But as an alternative, why not stop the users from selecting cells that are
locked on that protected sheet.

If you protect the worksheet in code, you can toggle this setting:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

BRG wrote:

I have written a subroutine that is to respond to an excel error when the
user of my spreadsheet tries to change the value of a cell that has been
locked and protected. I am having trouble trying to access the event to call
the routine. Does anyone have any tips on how to access this response?
Also, I'd like to override excel's response (in the form of an error box) and
replace it with my own. Is this possible?
--
BRG

--

Dave Peterson

Link to a cell in another (hidden) worksheet and use a formula to retrieve that
value.

Or...

Drop the linked cell business and use code to put the value back in the cell.

You can protect a sheet in code
Worksheets("sheet1").Protect Password:="hi", userinterfaceonly:=True

Then you can do lots of things in code that the user can't do. (Or just
unprotect, do the work and reprotect.)

BRG wrote:

Thanks for the idea Dave. I have one problem with that if you can think of a
way around it.

The cells that are locked are linked to control toolbar option buttons and
check boxes. The original problem was that when the user tries to change a
selection for a column of locked buttons, the graphic of the button changes
(from selected to unselected or visa versa) but the value in the linked cell
(which is used for calculations) doesn't. The subroutine I have since
written goes through the OLE objects in the sheet and sets their value equal
to the value in their linked cell, so I'm now looking for an event that will
call the subrouting.

Maybe as an alternative along the lines of your suggestion, is there a way
to make the control toolbar objects unselectable?

--
BRG

"Dave Peterson" wrote:

I don't think you're going to squeeze your subroutine in with any of excel's
events.

But as an alternative, why not stop the users from selecting cells that are
locked on that protected sheet.

If you protect the worksheet in code, you can toggle this setting:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

BRG wrote:

I have written a subroutine that is to respond to an excel error when the
user of my spreadsheet tries to change the value of a cell that has been
locked and protected. I am having trouble trying to access the event to call
the routine. Does anyone have any tips on how to access this response?
Also, I'd like to override excel's response (in the form of an error box) and
replace it with my own. Is this possible?
--
BRG

--

Dave Peterson


--

Dave Peterson