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" wrote in message
...
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" 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