Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Control Bar Width with Graph Object | Charts and Charting in Excel | |||
Excel Object in VB when running under scheduler control | Excel Programming | |||
Referencing a ComboBox control from a Workbook Object? | Excel Programming | |||
How to tell wich object/shape was clicked | Excel Programming |