Loop through excel checkboxes

[keenanbr]

I need to loop through checkboxes in excel spreadsheet and if checked do something depending on which checkbox. What I would really like is to be able to determine which row and column any checkbox in on. Any help appreciated.

Regards,

 

[SkipVought]

Hi,

What code do you have so far? Please post.

You code will tell us what kind of check boxes you are working with: Forms or Control Toolbox.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[keenanbr]

I figured it out. Here is my code

Sub Process_CheckBox()

Dim cBox As CheckBox
Dim LRow As Integer
Dim LCol As Integer
Dim LName As String
Dim cellToUpdate As String
Dim cellToMoveTo As String
Dim EMailTextCell As String
Dim EmailText As String
Dim recips As String
Dim cc As String

LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)

'Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LCol = cBox.TopLeftCell.Column


cellToUpdate = Cells(LRow, LCol + 2).Address(False, False)
cellToMoveTo = Cells(LRow, LCol + 3).Address(False, False)
cellToEmail = Cells(LRow, LCol - 1).Address(False, False)
EMailTextCell = Cells(LRow, LCol + 4).Address(False, False)

If cBox.Value > 0 Then
ActiveSheet.Range(cellToUpdate).Value = Now
If ActiveSheet.Range(cellToEmail).Value <> "" Then
GetEmailRecips ActiveSheet.Range(cellToEmail).Value, recips, cc
EmailText = ActiveSheet.Range(EMailTextCell).Value
GenerelEmail recips, cc, EmailText
End If
Else
ActiveSheet.Range(cellToUpdate).Value = Null
End If

Range(cellToMoveTo).Select

End Sub

I have a spreadsheet with checkboxes in a column. I needed to know which checkbox was selected and then pick up details from the adjacent columns if checked. I didn't want to write seperate macros for each checkbox, so I just associate this macro with all the checkboxes and once I know the row I can pick up the details I want.