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

I wouldn't bother.

I'd just check(!) the checkbox itself.

Private Sub TTWK1_Click()
If me.ttwk1.value = true then
'....



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

--

Dave Peterson

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