Bob Phillips
12/13/2006 9:17:00 AM
I thought that you were looking for generic code associated with all of the
checkboxes that would have some structured effect upon associated textboxes.
In my reasoning, this is event code, and you cannot pass such values to
those events. You could just code the individual checkbox click events to
call a sub that does what you want, but you then lose the generic nature.
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"emtrouwee" <emtrouwee@discussions.microsoft.com> wrote in message
news:2AC7446C-E26E-4A6E-8188-ECDD239DE9C0@microsoft.com...
>I just make the list like I typed below
>
> clearlist ("bda")
> clearlist ("bdb")
>
> It's just that I want to only have one place to change.
>
> I can send you the original file if you send me your e-mail (send it to my
> username at hotmail.com)
>
> "Bob Phillips" wrote:
>
>> But my question remains, how do you pass those registrations to the sub,
>> where do they come from?
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>
>> "emtrouwee" <emtrouwee@discussions.microsoft.com> wrote in message
>> news:F6FF76A4-61C7-4FFE-8AF9-E4927BA5B188@microsoft.com...
>> > Well at the moment I when the file is opened I set all checkboxes to
>> > unchecked and background for the textboxes to white. I do this by
>> > making a
>> > subroutine for every letter combination (aircraft registration). I just
>> > want
>> > one subroutine which can be called with the different registrations, so
>> > it
>> > is
>> > easier to update.
>> >
>> > E.G.
>> >
>> > Sub clearlist(registration)
>> > Sheet1.CB_???.value = false
>> > Sheet1.TB_???.Background.Value = 255,255,255
>> > ....
>> > ....
>> > End Sub
>> >
>> > clearlist ("bda")
>> > clearlist ("bdb")
>> > clearlist ("bdc")
>> >
>> > This way I don't have to add another subroutine where I need to change
>> > all
>> > data, but just add a line to the clearlist ("xxx").
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> 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" <emtrouwee@discussions.microsoft.com> wrote in message
>> >> news:4F46FC9C-20F9-495F-85CB-5F00D0B2D5D9@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>