Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox and linkedcell question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox and linkedcell question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox Question | Excel Discussion (Misc queries) | |||
Checkbox Question | Excel Discussion (Misc queries) | |||
Checkbox question | Excel Programming | |||
CheckBox question | Excel Programming | |||
Setting LinkedCell on a CheckBox Control | Excel Programming |