Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA parameters and checkboxes in Excel
Assuming the names are aligned, CB_BDA to say TB_BDA etc., then first create
a class module and add this code Public WithEvents mCheckboxes As MSForms.CheckBox Private Sub mCheckboxes_Click() Dim sCB As String If mCheckboxes.Value Then sCB = Right(mCheckboxes.Name, Len(mCheckboxes.Name) - 3) ActiveSheet.OLEObjects("TB_" & sCB).Object.Text = "yes" End If End Sub and add this code to the sheet code module Dim mcolEvents As Collection Private Sub Worksheet_Activate() Dim cCBEvents As clsActiveXEvents Dim shp As Shape Set mcolEvents = New Collection For Each shp In Me.Shapes If shp.Type = msoOLEControlObject Then If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then Set cCBEvents = New clsActiveXEvents Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object mcolEvents.Add cCBEvents End If End If Next End Sub My code just loads yes into the textbox, I am not clear where the value will be gotten from to make it parameterised as you suggest. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "emtrouwee" wrote in message ... Hi, I have a sheet with several checkboxes. Names are CB_BDA, CB_BDB etc. Now I want to use VBA code to check the CB status and change a textbox on a sheet according to the status.I managed this by making a routine for each checkbox, but I want to use a subroutine I can call for each checkbox. E.g. checkbox(BDA), checkbox(BDB). And that the routine adds the three letters. E.g.: SUB checkbox(letters) sheet1.CB_???.Value = "yes" End Sub The ??? should ne replaced by the 3 letters which are given as a parameter. I hope someone can help me Edwin |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with excel and checkboxes | Excel Discussion (Misc queries) | |||
How do I set up checkboxes in excel? | Excel Discussion (Misc queries) | |||
Excel Checkboxes | Excel Discussion (Misc queries) | |||
Using Checkboxes in Excel | Excel Programming | |||
Checkboxes in Excel - Please Help! | Excel Discussion (Misc queries) |