Dear All

I have more than 30 compartments with a check box. Can you suggest what is
the best way to disable all of them once the compartments are selected and to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest what
is
the best way to disable all of them once the compartments are selected and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

D/Nigel
These compartments are oil tanks. So when I approve certain tanks to load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest what
is
the best way to disable all of them once the compartments are selected and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

So are you saying a checkbox is attached to each oil tank

Regards,
Peter T

"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest
what
is
the best way to disable all of them once the compartments are selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

that's a little clearer, so what you actually have is around 30 checkboxes
on a worksheet that you wish to disable

Try this....you must ensure that the checkbox are named CheckBox1, CheckBox2
etc....

Sub LockChecks()
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To 30
.OLEObjects("CheckBox" & i).Enabled = False
Next i
End With
End Sub


--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest
what
is
the best way to disable all of them once the compartments are selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

D/Nigel and Peter

Will try ur code. In the mean time the following code goes to line 10 and
then goes to end of the program. In debug mode When I place the cursor on
checkboxC it shows as "nothing" assigned. What could be the reason.

Sub CHKTRY()

Dim CheckBoxC As CheckBox
'' Dim CheckBoxC As Excel.CheckBox

' Dim CheckBoxC As MSForms.CheckBox

If ActiveSheet.Range("G70").Value = True Then

10 For Each CheckBoxC In ActiveSheet.CheckBoxes

CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

End If

Peter reading in, sorry I did not explain in full.
I have about 30 tanks named in each cell and each cell has a check box and
linked to a cell. I pick certain tanks to load the cargo and dont want
anyone to change it. So after I check the tanks to load, I would like to
disable all the check boxes. If any of the duty officer checks or unchecks
message to prop up asking for a uniq password.

brgds/ captgnvr



"Nigel" wrote:

that's a little clearer, so what you actually have is around 30 checkboxes
on a worksheet that you wish to disable

Try this....you must ensure that the checkbox are named CheckBox1, CheckBox2
etc....

Sub LockChecks()
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To 30
.OLEObjects("CheckBox" & i).Enabled = False
Next i
End With
End Sub


--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest
what
is
the best way to disable all of them once the compartments are selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

Ah, the compartments are "virtual" oil tanks

Normally to disable checkboxes enabled property you can change all in on go
like this
ActiveSheet.CheckBoxes.Enabled = False ' True to re-enable

however if you disable you can't click and call an onaction macro.

Run LockCBS to assign the Onaction to all checkboxes on the sheet (if you
have some you don't want assigned you'll need to loop individually)

Once the macro is assigned, clicking a checkbox will set its value to what
it was previously, then run a prompt to enter a password, which if correct
will remove the onAction macro from all checkboxes.

When done run LockCBS again.


Sub LockCBS()
ActiveSheet.CheckBoxes.OnAction = "GetPW"
End Sub

Sub GetPW()
Dim vAns
On Error Resume Next
With ActiveSheet.CheckBoxes(Application.Caller)
.Value = IIf(.Value = xlOn, xlOff, xlOn)
End With
On Error GoTo 0

vAns = Application.InputBox("Enter password", "my title")
If LCase(vAns) = "hello" Then
ActiveSheet.CheckBoxes.OnAction = ""
MsgBox "Checkboxes enabled"
ElseIf vAns = False Then
Else: MsgBox "invalid password, hint - Hi"
End If

End Sub


Regards,
Peter T


"CAPTGNVR" wrote in message
...
D/Nigel and Peter

Will try ur code. In the mean time the following code goes to line 10 and
then goes to end of the program. In debug mode When I place the cursor
on
checkboxC it shows as "nothing" assigned. What could be the reason.

Sub CHKTRY()

Dim CheckBoxC As CheckBox
'' Dim CheckBoxC As Excel.CheckBox

' Dim CheckBoxC As MSForms.CheckBox

If ActiveSheet.Range("G70").Value = True Then

10 For Each CheckBoxC In ActiveSheet.CheckBoxes

CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

End If

Peter reading in, sorry I did not explain in full.
I have about 30 tanks named in each cell and each cell has a check box and
linked to a cell. I pick certain tanks to load the cargo and dont want
anyone to change it. So after I check the tanks to load, I would like to
disable all the check boxes. If any of the duty officer checks or
unchecks
message to prop up asking for a uniq password.

brgds/ captgnvr



"Nigel" wrote:

that's a little clearer, so what you actually have is around 30
checkboxes
on a worksheet that you wish to disable

Try this....you must ensure that the checkbox are named CheckBox1,
CheckBox2
etc....

Sub LockChecks()
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To 30
.OLEObjects("CheckBox" & i).Enabled = False
Next i
End With
End Sub


--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to
load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest
what
is
the best way to disable all of them once the compartments are
selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to
inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

D/Peter
Thanks for quick follow up. I quickly tried on a sample sheet and works a
charm. I will incorporate it in my real program and revert. Thank you so
much -- especially for making it most simplest.

For knowledge sake is it possible to let me know how to loop if I want to
permit only the marked check boxes tanks?

brgds/captgnvr


"Peter T" wrote:

Ah, the compartments are "virtual" oil tanks

Normally to disable checkboxes enabled property you can change all in on go
like this
ActiveSheet.CheckBoxes.Enabled = False ' True to re-enable

however if you disable you can't click and call an onaction macro.

Run LockCBS to assign the Onaction to all checkboxes on the sheet (if you
have some you don't want assigned you'll need to loop individually)

Once the macro is assigned, clicking a checkbox will set its value to what
it was previously, then run a prompt to enter a password, which if correct
will remove the onAction macro from all checkboxes.

When done run LockCBS again.


Sub LockCBS()
ActiveSheet.CheckBoxes.OnAction = "GetPW"
End Sub

Sub GetPW()
Dim vAns
On Error Resume Next
With ActiveSheet.CheckBoxes(Application.Caller)
.Value = IIf(.Value = xlOn, xlOff, xlOn)
End With
On Error GoTo 0

vAns = Application.InputBox("Enter password", "my title")
If LCase(vAns) = "hello" Then
ActiveSheet.CheckBoxes.OnAction = ""
MsgBox "Checkboxes enabled"
ElseIf vAns = False Then
Else: MsgBox "invalid password, hint - Hi"
End If

End Sub


Regards,
Peter T


"CAPTGNVR" wrote in message
...
D/Nigel and Peter

Will try ur code. In the mean time the following code goes to line 10 and
then goes to end of the program. In debug mode When I place the cursor
on
checkboxC it shows as "nothing" assigned. What could be the reason.

Sub CHKTRY()

Dim CheckBoxC As CheckBox
'' Dim CheckBoxC As Excel.CheckBox

' Dim CheckBoxC As MSForms.CheckBox

If ActiveSheet.Range("G70").Value = True Then

10 For Each CheckBoxC In ActiveSheet.CheckBoxes

CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

End If

Peter reading in, sorry I did not explain in full.
I have about 30 tanks named in each cell and each cell has a check box and
linked to a cell. I pick certain tanks to load the cargo and dont want
anyone to change it. So after I check the tanks to load, I would like to
disable all the check boxes. If any of the duty officer checks or
unchecks
message to prop up asking for a uniq password.

brgds/ captgnvr



"Nigel" wrote:

that's a little clearer, so what you actually have is around 30
checkboxes
on a worksheet that you wish to disable

Try this....you must ensure that the checkbox are named CheckBox1,
CheckBox2
etc....

Sub LockChecks()
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To 30
.OLEObjects("CheckBox" & i).Enabled = False
Next i
End With
End Sub


--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to
load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once the
selection is made and to give a message if accidently uncheck or check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you suggest
what
is
the best way to disable all of them once the compartments are
selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to
inadvertently
change the compartments that are selected for loading.

brgds/captgnvr

If by "marked" you mean ticked

dim cbx as checkbox

for each cbx in activesheet.checkboxes
if cbx.value = xlOn then
' code, eg
' cbx.onaction = "LockCBS"
end if
next

typed into this reply / not tested

Regards,
Peter T



"CAPTGNVR" wrote in message
...
D/Peter
Thanks for quick follow up. I quickly tried on a sample sheet and works a
charm. I will incorporate it in my real program and revert. Thank you so
much -- especially for making it most simplest.

For knowledge sake is it possible to let me know how to loop if I want to
permit only the marked check boxes tanks?

brgds/captgnvr


"Peter T" wrote:

Ah, the compartments are "virtual" oil tanks

Normally to disable checkboxes enabled property you can change all in on
go
like this
ActiveSheet.CheckBoxes.Enabled = False ' True to re-enable

however if you disable you can't click and call an onaction macro.

Run LockCBS to assign the Onaction to all checkboxes on the sheet (if you
have some you don't want assigned you'll need to loop individually)

Once the macro is assigned, clicking a checkbox will set its value to
what
it was previously, then run a prompt to enter a password, which if
correct
will remove the onAction macro from all checkboxes.

When done run LockCBS again.


Sub LockCBS()
ActiveSheet.CheckBoxes.OnAction = "GetPW"
End Sub

Sub GetPW()
Dim vAns
On Error Resume Next
With ActiveSheet.CheckBoxes(Application.Caller)
.Value = IIf(.Value = xlOn, xlOff, xlOn)
End With
On Error GoTo 0

vAns = Application.InputBox("Enter password", "my title")
If LCase(vAns) = "hello" Then
ActiveSheet.CheckBoxes.OnAction = ""
MsgBox "Checkboxes enabled"
ElseIf vAns = False Then
Else: MsgBox "invalid password, hint - Hi"
End If

End Sub


Regards,
Peter T


"CAPTGNVR" wrote in message
...
D/Nigel and Peter

Will try ur code. In the mean time the following code goes to line 10
and
then goes to end of the program. In debug mode When I place the
cursor
on
checkboxC it shows as "nothing" assigned. What could be the reason.

Sub CHKTRY()

Dim CheckBoxC As CheckBox
'' Dim CheckBoxC As Excel.CheckBox

' Dim CheckBoxC As MSForms.CheckBox

If ActiveSheet.Range("G70").Value = True Then

10 For Each CheckBoxC In ActiveSheet.CheckBoxes

CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

End If

Peter reading in, sorry I did not explain in full.
I have about 30 tanks named in each cell and each cell has a check box
and
linked to a cell. I pick certain tanks to load the cargo and dont want
anyone to change it. So after I check the tanks to load, I would like
to
disable all the check boxes. If any of the duty officer checks or
unchecks
message to prop up asking for a uniq password.

brgds/ captgnvr



"Nigel" wrote:

that's a little clearer, so what you actually have is around 30
checkboxes
on a worksheet that you wish to disable

Try this....you must ensure that the checkbox are named CheckBox1,
CheckBox2
etc....

Sub LockChecks()
Dim i As Integer
With Worksheets("Sheet1")
For i = 1 To 30
.OLEObjects("CheckBox" & i).Enabled = False
Next i
End With
End Sub


--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
D/Nigel
These compartments are oil tanks. So when I approve certain tanks to
load,
duty officer should not change it inadvertently.

So I need a way to block all these 30 odd check boxes locked once
the
selection is made and to give a message if accidently uncheck or
check
these
boxes.

thnks ur response and I kept on looking for response or guidance.

brgds/captgnvr

"Nigel" wrote:

What do you mean 'compartment' ? Is it frame or something else.

--

Regards,
Nigel




"CAPTGNVR" wrote in message
...
Dear All

I have more than 30 compartments with a check box. Can you
suggest
what
is
the best way to disable all of them once the compartments are
selected
and
to
enable them only after entering a special password?

The reason for this requirement is, I dont want anyone to
inadvertently
change the compartments that are selected for loading.

brgds/captgnvr