MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

capture textbox events from multiple textboxes in one place

  Asked By: Chisisi    Date: Dec 10    Category: MS Office    Views: 3356

I've got a form with a 4x16 grid of textboxes. When I leave a textbox,
a subroutine is run. Currently I put a Call to the subroutine into the
textbox_exit event for each textboxes. I'd be nice to be able to
capture the exit event on a more global scale so that I don't need to
do the Call from each textbox_exit event (Ok, so it's only a couple
lines of code...)

This is for (don't laugh) Excel 97.



8 Answers Found

Answer #1    Answered By: Elisa Larson     Answered On: Dec 10

I have not been able to achieve this. If you get no response it is not
because we don't care but because we all can't think of a way to do it.

Answer #2    Answered By: Alessio Smith     Answered On: Dec 10

Is this (possible) inability to do the task
specific to Excel 97 or generally to ExcelVBA?

Answer #3    Answered By: Sammy Anderson     Answered On: Dec 10

Nope. Not just '97. It's across the board and is "by design". In VB you may
be able to use a change event  within a control array.. Dunno.. But VBA
doesn't support arrays of controls.

In VBA the closest you can come to a control array is to use controls with
similar names and loop through the complete control set picking up what you

This would involve some tricky coding... First you would have to save the
state of all the controls and then loop through them all when *any* of them

Not worth it.

The best, and only... solution I've found in VBA so far is to make one Sub
that does what you want and stick it in the change/before update/after
update ... As appropriate ... event of *each* copntrol you're looking at.

Answer #4    Answered By: Sammy Brown     Answered On: Dec 10

be able to use a change event  within a control array.. Dunno.. But VBA
doesn't support arrays of controls.

You are correct. In VB you can have a control array, with just one set of
event handlers for the whole array. I found out you are not able to do this in
VBA, however.

Answer #5    Answered By: Archer Smith     Answered On: Dec 10

This may not be much use to you as I don't know whether this also
applies to XL97, however, John Walkenbach, in his 'Microsoft Excel
2000 Power Programming with VBA' and his 'Excel 2002 Power Programming
with VBA' explains how to do it with a class module. I did a google for
"multiple buttons, one event  handler"
with the the quote marks, and found a useful pdf not distantly related
to the above.

Answer #6    Answered By: Ida Andrews     Answered On: Dec 10

That's a really nice technique Pascal... It may even prompt me to use more

Answer #7    Answered By: Lionel Thomas     Answered On: Dec 10

Unfortunately, it doesn't appear to work with
textboxes (at least to XL2003) - at least, no procedures are listed in
the dropdown list. The technique works with most of the other
controls, though.

The link to Walkenbach's 'Power Programming with VBA' pdf file did
come in handy for a couple of other questiuons I had, though. Thanks

Answer #8    Answered By: Alejandro Smith     Answered On: Dec 10

I got textbox  events to work with John Walkenbach's technique
described here: http://j-walk.com/ss/excel/tips/tip44.htm (which
incidentally implies that it might work with XL 97) by converting it
to work with textboxes  (code below) BUT, it appears that three events
associated with textboxes, viz. BeforeUpdate, Enter and Exit, are not
available since: "Exit isn't an event  of the textbox. It is an event
of the control (the container for the textbox)." ref:
which page also may contain something you can use.

All the other events  are available:

Perhaps you can work round it by using one of these?

Perhaps someone knows how to set up a class module for the control
(the container) rather than the textbox?

For what it's worth, the code  for, in this case, the change event:
In a class module called tbClass:

Public WithEvents TextBoxGroup As MSForms.TextBox
Private Sub TextBoxGroup_Change()
MsgBox "Hello there!"
End Sub

In a standard code module:

Sub ShowDialog()
End Sub

In the UserForm1 code module:

Dim TextBoxes() As New tbClass
Private Sub UserForm_Initialize()
Dim TextBoxCount As Integer
Dim ctl As Control
' Create the TextBox objects
TextBoxCount = 0
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then
TextBoxCount = TextBoxCount + 1
ReDim Preserve TextBoxes(1 To TextBoxCount)
Set TextBoxes(TextBoxCount).TextBoxGroup = ctl
End If
Next ctl
End Sub