Return Index of Control Object Clicked!
 

Hi,

How do i return the Index of the Checkbox (control ToolBox item, not Forms
toolbar item) embedded on a worksheet (sheet1) on CLICKING it. Please note
that there are many checkboxes embedded on the worksheet.

I Dont want (something as shown below) to create seperate code for each
Checkbox as there are around 250 Checkboxes on the Sheet1.
Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub
................but one code which will run for every clicked checkbox on the
worksheet. thats why the need for refering index of checkbox.

What i want to do is:
Once the Checkbox is clicked, it should change its Caption Property to show
system-Time (Now), Linked cell property - linked to a similar cell (say,
Checkbox1 placed on cell A5 on Sheet1, so Linked Cell will be Sheet2!A5) on
Sheet2.


How do i do this using VBA?

Eijaz

Return Index of Control Object Clicked!
 

I don't think you can.

But you could if you used the checkbox from the Forms toolbar:

Option Explicit
Sub CBoxClick()
Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

With myCBX
If .Value = xlOn Then
.Caption = Format(Time, "hh:mm:ss") & _
IIf(.LinkedCell = "", "", "--" & .LinkedCell)
Else
.Caption = "Click and watch!"
End If
End With
End Sub

If you meant you wanted to change the linked cell, you could use:

Option Explicit
Sub CBoxClick()
Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

With myCBX
If .Value = xlOn Then
.Caption = Format(Time, "hh:mm:ss")
.LinkedCell _
= Worksheets("sheet2").Range(.TopLeftCell.Address) _
.Address(external:=True)
Else
.Caption = "Click and watch!"
.LinkedCell = ""
End If
End With
End Sub

gr8guy wrote:

Hi,

How do i return the Index of the Checkbox (control ToolBox item, not Forms
toolbar item) embedded on a worksheet (sheet1) on CLICKING it. Please note
that there are many checkboxes embedded on the worksheet.

I Dont want (something as shown below) to create seperate code for each
Checkbox as there are around 250 Checkboxes on the Sheet1.
Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub
...............but one code which will run for every clicked checkbox on the
worksheet. thats why the need for refering index of checkbox.

What i want to do is:
Once the Checkbox is clicked, it should change its Caption Property to show
system-Time (Now), Linked cell property - linked to a similar cell (say,
Checkbox1 placed on cell A5 on Sheet1, so Linked Cell will be Sheet2!A5) on
Sheet2.

How do i do this using VBA?

Eijaz

--

Dave Peterson

Return Index of Control Object Clicked!
 

gr8,

You need to create a checkbox Class to which you can add all the Checkboxes.
Then clicking any of them will call the class module:

First create a class module in the VBE by Insert- Class Module. Name it
SheetCboxClass. In this module paste the following code:

Option Explicit
Public WithEvents SheetCbox As msforms.CheckBox

Private Sub SheetCbox_Click()

SheetCbox.Caption = Format(Time, "hh:mm:ss")
'This just does the time part
'I didn't understand your linked cell request

End Sub

Then paste the following into the Sheet Module for the worksheet that
contains your comboboxes:

Dim myControls As Collection ' note this is above any subroutines

Option Explicit
Private Sub Workbook_Open()

Dim tmpctl As OLEObject
Dim ctl As SheetCboxClass

Set myControls = New Collection
For Each tmpctl In Sheet1.OLEObjects
If TypeOf tmpctl.Object Is msforms.CheckBox Then
Set ctl = New SheetCboxClass
Set ctl.SheetCbox = tmpctl.Object
myControls.Add ctl
End If
Next

End Sub

For more info on class modules see this link at John Walkenbach's site
http://j-walk.com/ss/excel/tips/tip44.htm

hth,

Doug

"gr8guy" wrote in message
...
Hi,

How do i return the Index of the Checkbox (control ToolBox item, not Forms
toolbar item) embedded on a worksheet (sheet1) on CLICKING it. Please note
that there are many checkboxes embedded on the worksheet.

I Dont want (something as shown below) to create seperate code for each
Checkbox as there are around 250 Checkboxes on the Sheet1.
Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub
...............but one code which will run for every clicked checkbox on
the
worksheet. thats why the need for refering index of checkbox.

What i want to do is:
Once the Checkbox is clicked, it should change its Caption Property to
show
system-Time (Now), Linked cell property - linked to a similar cell (say,
Checkbox1 placed on cell A5 on Sheet1, so Linked Cell will be Sheet2!A5)
on
Sheet2.


How do i do this using VBA?

Eijaz

Return Index of Control Object Clicked!
 

"gr8guy" wrote in message ...
Hi,

How do i return the Index of the Checkbox (control ToolBox item, not Forms
toolbar item) embedded on a worksheet (sheet1) on CLICKING it. Please note
that there are many checkboxes embedded on the worksheet.

I Dont want (something as shown below) to create seperate code for each
Checkbox as there are around 250 Checkboxes on the Sheet1.
Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub
...............but one code which will run for every clicked checkbox on the
worksheet. thats why the need for refering index of checkbox.

What i want to do is:
Once the Checkbox is clicked, it should change its Caption Property to show
system-Time (Now), Linked cell property - linked to a similar cell (say,
Checkbox1 placed on cell A5 on Sheet1, so Linked Cell will be Sheet2!A5) on
Sheet2.


How do i do this using VBA?

Eijaz



Hi Eijaz,

Using controls from the Control Toolbox means you'll need 250 separate
CheckBox_Click macros. (VBA does not support Control Arrays).
Fortunately, you can make them short & sweet by calling a common routine
and passing a reference to the individual CheckBoxes.
eg.

Private Sub CheckBox1_Click()
CommonSub ActiveSheet.OLEObjects("CheckBox1")
End Sub

Private Sub CheckBox2_Click()
CommonSub ActiveSheet.OLEObjects("CheckBox2")
End Sub

Private Sub CheckBox3_Click()
CommonSub ActiveSheet.OLEObjects("CheckBox3")
End Sub

Sub CommonSub(OLEobj As OLEObject)
With OLEobj
.Object.Caption = Now
.LinkedCell = "Sheet2!" & .TopLeftCell.Address
End With
End Sub


If you want to reconsider using CheckBoxes from the Forms toolbar, you can do
away with the 250 stubs and use Application.Caller to identify the clicked
CheckBox. eg.

Sub ClickRoutine()
With ActiveSheet.Shapes(Application.Caller)
.TextFrame.Characters.Text = Now
.ControlFormat.LinkedCell = "Sheet2!" & .TopLeftCell.Address
End With
End Sub

The trouble with that is, you need to Assign the macro to 250 controls, but that
can be done easily with something like this...

Sub MassAssigner()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 10) = "Check Box " Then
shp.OnAction = "ClickRoutine"
End If
Next shp
End Sub


Regards,
Vic Eldridge