Control Array

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I have a form with over 200 toggle buttons and I am looking for a way for all
the buttons to share the same event procedure.

In doing research to find a solution I found that VB has what is called Control
Arrays where a group of the same type of controls share the same event
procedure. Unfortunately VBA does not support Control Arrays. Does anyone have
any ideas on how to create the same functionality in VBA?

Thanks!

Rachel
 
I have a form with over 200 toggle buttons and I am looking for a
way for all the buttons to share the same event procedure.

In doing research to find a solution I found that VB has what is
called Control Arrays where a group of the same type of controls
share the same event procedure. Unfortunately VBA does not support
Control Arrays. Does anyone have any ideas on how to create the
same functionality in VBA?
Click to expand...
You can create a module, put your code there and put a call to the main
procedure from each OnClick event.

If you name all your buttons with alpha and then numeric you can access
each button within a loop using the loop index.

Bob
 
Bob,

Thanks for taking the time to respond!

Yes, I could code the OnClick event of each of the 200+ buttons but that's what
I am trying to avoid!
 
Rachel said:
I have a form with over 200 toggle buttons and I am looking for a way for all
the buttons to share the same event procedure.

In doing research to find a solution I found that VB has what is called Control
Arrays where a group of the same type of controls share the same event
procedure. Unfortunately VBA does not support Control Arrays. Does anyone have
any ideas on how to create the same functionality in VBA?
Click to expand...

200 toggle buttons. Awesome. Why?

Insert a class module. Cut/Paste the following.

<clsTogButt>
Option Compare Database
Option Explicit

Private WithEvents togbutt As Access.ToggleButton

Public Sub Init(tb As ToggleButton)
Set togbutt = tb
togbutt.OnClick = "[Event Procedure]"
End Sub

Private Sub Class_Terminate()
Set togbutt = Nothing
End Sub

Private Sub togbutt_Click()

' change the vbRed, vbBlack , etc. colors to suit your taste
Select Case togbutt.value
Case True
togbutt.ForeColor = vbBlack
Case False
togbutt.ForeColor = vbRed
Case Else
'.togbutt.ForeColor = vbFlourescentOrange
End Select

End Sub

</clsTogButt>


In the form's code module.

<Form Module Code>

Private Sub Form_Open(Cancel As Integer)
Dim oTB As clsToggleButton
Dim ctl As Control
Set tButtons = New Collection

For Each ctl In Me.Controls
If ctl.ControlType = acToggleButton Then
Set oTB = New clsToggleButton
oTB.Init ctl
tButtons.Add oTB
End If
Next

Set oTB = Nothing
End Sub

Private Sub Form_Close()
Set tButtons = Nothing
End Sub

</Form Module Code>
 
Rachel said:
I have a form with over 200 toggle buttons and I am looking for a way for all
the buttons to share the same event procedure.

In doing research to find a solution I found that VB has what is called Control
Arrays where a group of the same type of controls share the same event
procedure. Unfortunately VBA does not support Control Arrays. Does anyone have
any ideas on how to create the same functionality in VBA?
Click to expand...

Try to stick to one thread Rachel. It's getting confusing
trying to keep track of the replies to this question in
different threads.

The answer to this specific issue is "No, But". You can
simulate it a couple of ways. Using the uniform naming
technique others have mentioned, you can have a common event
function as I posted in another thread. Alternatively, you
place a very large transparent command button over the top
of the toggles and use its MouseDown/Up event to get the
mouse coordinates and translate those into the control that
the mouse was over when the mouse event occurred.
 
I have a form with over 200 toggle buttons and I am looking for a way for all
the buttons to share the same event procedure.

In doing research to find a solution I found that VB has what is called Control
Arrays where a group of the same type of controls share the same event
procedure. Unfortunately VBA does not support Control Arrays. Does anyone have
any ideas on how to create the same functionality in VBA?
Click to expand...

you can set the event to a function in a global module thus:

=MyToggleButtonEvent()

then in a module:

Public Function MyToggleButtonEvent()
Dim ctl As Control
' This tells you which toggle button
' won't work for MouseMove event :-)
set ctl = Screen.ActiveControl
... do something
End Function
 
You can place a function in the on-click event, and thus NOT have to open up
the code editor for each event.

In fact, you can select 30 controls, and then type in the name of the
function for the on-click event, and it will be applied to all 30 controls.

However, we don't have control arrays like in VB. 200 toggle buttons sounds
like a lot. Perhaps you should create a related table, and 3 fields:

QustionAir_id QuestionName Yes/no


The above design would allow unlimited number of yes/no questions, and you
could add new questions without having to modify the table design, or the
form design. More importantly is being able to easily summarise data for
particular questions. With 200 check boxes, your design is going to be a bit
hard to work with.
 
Bob,

Thanks for taking the time to respond!

Yes, I could code the OnClick event of each of the 200+ buttons but
that's what I am trying to avoid!
Click to expand...

No need for you to code the OnClick event. You can set the onclick event
programatically from code at form initialization from custom class
module. Someone else posted some sample code already.

Bob
 
You can place a function in the on-click event, and thus NOT have
to open up the code editor for each event.
Click to expand...

I didn't know that.
What happens to the returned value? must it be Null, does it pass to the
control's value, or is it discarded?

It's a good day. I learned something.

Thanks
Bob
 
Back
Top