Checkbox and linkedcell question
Hi Athena,
Further to what Dave has said, there is also no need to create a formula in
the cell and then copy and past the values. You can do it in one line with
the following code.
Cells(3, 1) = Now()
I can't say it will save you much but you can also place the majority of the
code in a standard module and call that module from each of the checkbox
click events and pass the checkbox and row number parameters as follows. (It
is more applicable where you have a lot of code that is all the same for each
checkbox)
The checkbox code would be like the following. The 3 parameter in
Call UpdateNow(chkBox, 3) is the row number where you want the value to be
placed.
Private Sub TTWK1_Click()
Dim chkBox As Object
Set chkBox = Me.TTWK1
Call UpdateNow(chkBox, 3)
End Sub
The following code would then be in a standard module.
Sub UpdateNow(chk As Object, rowNumb As Long)
If chk.Value = True Then
Cells(rowNumb, 1) = Now()
Else
Cells(rowNumb, 1) = ""
End If
End Sub
--
Regards,
OssieMac
"treesinger101" wrote:
I want to use the cell address of a checkbox's linked cell. How do I
reference it in VBA so I don't have to write the code over and over. I have
the location hardcoded now, but I have about 30 checkboxes.
Here is what I want to happen when I click any checkbox.
Private Sub TTWK1_Click()
If Cells(3, 2).Value = True Then
Cells(3, 1) = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
Cells(3, 1) = ""
End If
End Sub
Thank you for any help.
--
Athena
Payroll Goddess
|