Make a check box fill in a value?

B

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
 
D

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.
 
D

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
 
B

Bob

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


Bob said:
That will work GREAT! Thanks for the help!

-Bob
Click to expand...
 
D

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.
Dave,
What about making this work using a Check Box from the "Control Toolbox"
rather than the forms toolbox. Is that possible?
Click to expand...
 
Top