Make a check box fill in a value?

[Bob]

Hi all,
Is there a way in Excel to that when a user (on a protected document)
checks or uncheks a Form Check Box to have that action fill in a value for a
specific cell?

Thanks for the help
Bob

[Dave Peterson]

I put a checkbox from the Forms toolbar on a worksheet.

I pasted this into a General module:
Option Explicit
Sub testme()

Dim myCell As Range
Set myCell = ActiveSheet.Range("a1")

If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
myCell.Value = "It's on"
Else
myCell.Value = "it's off"
End If
End Sub

I rightclicked on that checkbox and assigned this macro to that that checkbox.

I unlocked A1 of that worksheet (format|cell|protection tab)

I protected the worksheet (tools|protection|protect sheet).

And it worked fine.

Bob wrote:
>
> Hi all,
> Is there a way in Excel to that when a user (on a protected document)
> checks or uncheks a Form Check Box to have that action fill in a value for a
> specific cell?
>
> Thanks for the help
> Bob

--

Dave Peterson

[Dave Peterson]

In fact, if I kept A1 locked on that protected sheet, the macro could unprotect
the worksheet (if it knows the password), do the work and then reprotect the
password.

Option Explicit
Sub testme2()

Dim myCell As Range
Set myCell = ActiveSheet.Range("a1")

ActiveSheet.Unprotect Password:="hi"

If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
myCell.Value = "It's on"
Else
myCell.Value = "it's off"
End If

ActiveSheet.Protect Password:="hi"
End Sub

Bob wrote:
>
> Hi all,
> Is there a way in Excel to that when a user (on a protected document)
> checks or uncheks a Form Check Box to have that action fill in a value for a
> specific cell?
>
> Thanks for the help
> Bob

--

Dave Peterson

[Bob]

That will work GREAT! Thanks for the help!

-Bob


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:430BC0EE.D965DB3E@verizonXSPAM.net...
> In fact, if I kept A1 locked on that protected sheet, the macro could
> unprotect
> the worksheet (if it knows the password), do the work and then reprotect
> the
> password.
>
> Option Explicit
> Sub testme2()
>
> Dim myCell As Range
> Set myCell = ActiveSheet.Range("a1")
>
> ActiveSheet.Unprotect Password:="hi"
>
> If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
> myCell.Value = "It's on"
> Else
> myCell.Value = "it's off"
> End If
>
> ActiveSheet.Protect Password:="hi"
> End Sub
>
> Bob wrote:
>>
>> Hi all,
>> Is there a way in Excel to that when a user (on a protected document)
>> checks or uncheks a Form Check Box to have that action fill in a value
>> for a
>> specific cell?
>>
>> Thanks for the help
>> Bob
>
> --
>
> Dave Peterson

[Bob]

Dave,
What about making this work using a Check Box from the "Control Toolbox"
rather than the forms toolbox. Is that possible?


"Bob" <here@nowhere.com> wrote in message
news:O8IJtDNqFHA.1324@tk2msftngp13.phx.gbl...
> That will work GREAT! Thanks for the help!
>
> -Bob
>
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:430BC0EE.D965DB3E@verizonXSPAM.net...
>> In fact, if I kept A1 locked on that protected sheet, the macro could
>> unprotect
>> the worksheet (if it knows the password), do the work and then reprotect
>> the
>> password.
>>
>> Option Explicit
>> Sub testme2()
>>
>> Dim myCell As Range
>> Set myCell = ActiveSheet.Range("a1")
>>
>> ActiveSheet.Unprotect Password:="hi"
>>
>> If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
>> myCell.Value = "It's on"
>> Else
>> myCell.Value = "it's off"
>> End If
>>
>> ActiveSheet.Protect Password:="hi"
>> End Sub
>>
>> Bob wrote:
>>>
>>> Hi all,
>>> Is there a way in Excel to that when a user (on a protected
>>> document)
>>> checks or uncheks a Form Check Box to have that action fill in a value
>>> for a
>>> specific cell?
>>>
>>> Thanks for the help
>>> Bob
>>
>> --
>>
>> Dave Peterson
>
>

[Dave Peterson]

If you use the checkbox from the control toolbox, then click on the design mode
icon (also on that toolbar) and double click on that checkbox.

You'll be taken to where you should place the code.

This worked for me:

Option Explicit
Private Sub CheckBox1_Click()

Dim myCell As Range
Set myCell = Me.Range("a1")

Me.Unprotect Password:="hi"

If Me.CheckBox1.Value = True Then
myCell.Value = "It's on"
Else
myCell.Value = "it's off"
End If

Me.Protect Password:="hi"
End Sub


It's almost the same--notice that activesheet. was replaced with Me. Me is the
object that owns the code--in this case it's the worksheet that holds that
checkbox from the control toolbox toolbar.

One nice thing about using the checkbox from the forms toolbar is you can assign
the same macro to each checkbox. (You'd have to add a bit to make sure you got
the correct corresponding cell, though.)

But with the checkboxes from the control toolbox toolbar, each checkbox has its
own code.

Bob wrote:
>
> Dave,
> What about making this work using a Check Box from the "Control Toolbox"
> rather than the forms toolbox. Is that possible?
>
> "Bob" <here@nowhere.com> wrote in message
> news:O8IJtDNqFHA.1324@tk2msftngp13.phx.gbl...
> > That will work GREAT! Thanks for the help!
> >
> > -Bob
> >
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:430BC0EE.D965DB3E@verizonXSPAM.net...
> >> In fact, if I kept A1 locked on that protected sheet, the macro could
> >> unprotect
> >> the worksheet (if it knows the password), do the work and then reprotect
> >> the
> >> password.
> >>
> >> Option Explicit
> >> Sub testme2()
> >>
> >> Dim myCell As Range
> >> Set myCell = ActiveSheet.Range("a1")
> >>
> >> ActiveSheet.Unprotect Password:="hi"
> >>
> >> If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
> >> myCell.Value = "It's on"
> >> Else
> >> myCell.Value = "it's off"
> >> End If
> >>
> >> ActiveSheet.Protect Password:="hi"
> >> End Sub
> >>
> >> Bob wrote:
> >>>
> >>> Hi all,
> >>> Is there a way in Excel to that when a user (on a protected
> >>> document)
> >>> checks or uncheks a Form Check Box to have that action fill in a value
> >>> for a
> >>> specific cell?
> >>>
> >>> Thanks for the help
> >>> Bob
> >>
> >> --
> >>
> >> Dave Peterson
> >
> >

--

Dave Peterson