check box...in a cell...is it really?

[Davers]

Hey everyone, this may be a silly question but... Is it possible to use terms like Offset with a checkbox? For instance, if I put a checkbox in cell A1...I know it's not really in cell A1, but is it aware of where it is in relation to the sheet? For instance if I put a check in the checkbox, and the check box is in cell A1, can I say something like:

If Worksheets("Sheet1").CheckBox1 = True Then
Cells.Offset.(0, 2).Font.Strikethrough = True
etc.

It would be nice if you could...

Thanks for any help...

Dave

 

[Joe Was]

You need to put a "Form" checkbox on the sheet!
Then assign a macro to it. Do not test for the check being true!

Sub cbCheck()
'Sheet module code!
'For a "Form CheckBox" used on a sheet.

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.Select
Selection.Font.Strikethrough = True
End Sub

For a control checkbox:

Private Sub CheckBox1_Click()
'Sheet module code!
'For a "Control Tool Box CheckBox" used on a sheet.

If CheckBox1.Value = -1 Then
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.Select
Selection.Font.Strikethrough = True
End If
End Sub

 

[Ekim]

If the Checkbox (Control Toolbox menu) is covering say cell B3, and the link cell is B3 (i.e. underneath the Checkbox) then the following will work (put the macro in the relevant sheet module):

Private Sub CheckBox1_Click()

With Worksheets("Sheet1")
  If Worksheets("Sheet1").CheckBox1 = True Then
   .Range("B3").Offset(0, 2).Font.Strikethrough = True
   Else
   .Range("B3").Offset(0, 2).Font.Strikethrough = False
  End If
End With
End Sub

Alternatively, drop the offset and directly format the target cell:

Private Sub CheckBox1_Click()

With Worksheets("Sheet1")
  If Worksheets("Sheet1").CheckBox1 = True Then
   .Range("D3").Font.Strikethrough = True
   Else
   .Range("D3").Font.Strikethrough = False
  End If
End With
End Sub

HTH

Mike

 

[Davers]

I love this site!!!!! Man, thanks both of you very much!!!

Have a good day,

Dave

 

[Joe Was]

In case you wanted to use the Form CheckBox and get it to cycle on and off use a soft switch:

Sub cbCheck()
'Sheet module code!
'For a "Form CheckBox" used on a sheet.
Static myClick

If myClick = "" Then
'ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.Select
Selection.Font.Strikethrough = True
myClick = "Yes"
Else
ActiveCell.Select
Selection.Font.Strikethrough = False
myClick = ""
End If
End Sub