Worksheet ActiveX Objects

Hi All
I have an application that uses an unprotect / protect process before a user
wish to make changes.

I have a number of control buttons on the sheet (not userform) that when
clicked, intiate an unprotect - sheet(s) update - protect process. This
works OK.

I have now added some option buttons, which uses the clcik event to intiatie
the same process, however now I get an error arising form the unprotect
process. It appears that the unprotect fails due to the control button being
active (having clicked it). The control buttons have the TakeFocusOnClick
set to false, but there does not appear to be a similar option for
OptionButtons?

Nigel,

I am not sure I quite get the problem, but I added a couple of option
buttons that protect and unprotect fine.

Am I missing a step perhaps?

--
HTH

Bob Phillips

"Nigel" <Nigel@discussions.microsoft.com> wrote in message
news:2775F79D-8297-40F7-977F-86E6C6331C09@microsoft.com...
> Hi All
> I have an application that uses an unprotect / protect process before a
user
> wish to make changes.
>
> I have a number of control buttons on the sheet (not userform) that when
> clicked, intiate an unprotect - sheet(s) update - protect process. This
> works OK.
>
> I have now added some option buttons, which uses the clcik event to
intiatie
> the same process, however now I get an error arising form the unprotect
> process. It appears that the unprotect fails due to the control button
being
> active (having clicked it). The control buttons have the
TakeFocusOnClick
> set to false, but there does not appear to be a similar option for
> OptionButtons?

Hi Bob,
Not sure I get it either!
But the code I use to lock unlock / lock the worksheets and the code that
calls it are....

' this code works from a control button....
Private Sub controlbutton1_Click()
Call xUnprotect
Call UpdatePIT
Call xProtect
End Sub

' this code does not work from an option button
Private Sub OptionButton1_Click()
Call xUnprotect
Call UpdatePIT
Call xProtect
End Sub

' these are the protect / unprotect procedures..
Sub xProtect()
Application.ScreenUpdating = False
Dim xs As Integer
For xs = 1 To ActiveWorkbook.Sheets.Count
Sheets(xs).Protect
Next
ActiveWorkbook.Protect Structure:=True, Windows:=True
End Sub

Sub xUnprotect()
Application.ScreenUpdating = False
Dim xs As Integer
For xs = 1 To ActiveWorkbook.Sheets.Count
Sheets(xs).Unprotect
Next
ActiveWorkbook.Unprotect
End Sub




"Bob Phillips" wrote:

> Nigel,
>
> I am not sure I quite get the problem, but I added a couple of option
> buttons that protect and unprotect fine.


>
> Am I missing a step perhaps?
>
> --
> HTH
>
> Bob Phillips
>
> "Nigel" <Nigel@discussions.microsoft.com> wrote in message
> news:2775F79D-8297-40F7-977F-86E6C6331C09@microsoft.com...
> > Hi All
> > I have an application that uses an unprotect / protect process before a
> user
> > wish to make changes.
> >
> > I have a number of control buttons on the sheet (not userform) that when
> > clicked, intiate an unprotect - sheet(s) update - protect process. This
> > works OK.
> >
> > I have now added some option buttons, which uses the clcik event to
> intiatie
> > the same process, however now I get an error arising form the unprotect
> > process. It appears that the unprotect fails due to the control button
> being
> > active (having clicked it). The control buttons have the
> TakeFocusOnClick
> > set to false, but there does not appear to be a similar option for
> > OptionButtons?
>
>
>

Hi Bob
Dug in a little bit more and discover that having clicked the option button,
it remains in focus and the protection fails. Similar to the problem of a
control button TakeFocusOnClick being left as True.

I am using xl97 as the target application version of Excel

--
Cheers
Nigel



"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:ercHutnlFHA.3584@TK2MSFTNGP10.phx.gbl...
> Nigel,
>
> I am not sure I quite get the problem, but I added a couple of option
> buttons that protect and unprotect fine.
>
> Am I missing a step perhaps?
>
> --
> HTH
>
> Bob Phillips
>
> "Nigel" <Nigel@discussions.microsoft.com> wrote in message
> news:2775F79D-8297-40F7-977F-86E6C6331C09@microsoft.com...
> > Hi All
> > I have an application that uses an unprotect / protect process before a
> user
> > wish to make changes.
> >
> > I have a number of control buttons on the sheet (not userform) that when
> > clicked, intiate an unprotect - sheet(s) update - protect process. This
> > works OK.
> >
> > I have now added some option buttons, which uses the clcik event to
> intiatie
> > the same process, however now I get an error arising form the unprotect
> > process. It appears that the unprotect fails due to the control button
> being
> > active (having clicked it). The control buttons have the
> TakeFocusOnClick
> > set to false, but there does not appear to be a similar option for
> > OptionButtons?
>
>