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" <(E-Mail Removed)> wrote in message
news:2586562C-9AB6-4951-80E9-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "CAPTGNVR" <(E-Mail Removed)> wrote in message
>> news:0C32FB69-1317-426A-B034-(E-Mail Removed)...
>> > 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
>> >> (E-Mail Removed)
>> >>
>> >>
>> >>
>> >> "CAPTGNVR" <(E-Mail Removed)> wrote in message
>> >> news:9A70F5F4-588F-4161-B606-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>>
>>
Similar Threads
