Solved: Manage similar events with textboxes

[nst1107]

I'm trying to use Bob's class module example (http://www.vbaexpress.com/forum/showthread.php?t=24799) to handle a large number of textboxes on a userform. I'm having trouble with the _Enter() _Exit() events. I've changed

[VBA]Public WithEvents mButtonGroup As MSForms.CommandButton[/VBA]

to[VBA]Public WithEvents mButtonGroup As MSForms.TextBox[/VBA]but the events don't appear in the class module.

I've never used class modules before, so I have little understanding of how they work. Is there a way to capture the _Enter() _Exit() events using Bob's method?

[CreganTur]

You have to write in the events you want, they do not appear on their own.

[VBA]Private Sub mButtonGroup_Enter()
'stuff to do
End Sub
Private Sub mButtonGroup_Exit()
'stuff to do
End Sub [/VBA]

HTH

[nst1107]

I tried that idea. The events never fire.

[Kenneth Hobs]

See http://www.ozgrid.com/forum/showthread.php?t=99046

[nst1107]

I don't see any discussion of the _Enter() and _Exit() events. Another thing that puzzles me: some of the properties associated with textboxes aren't supplied in the popup list after I type, say, "mButtonGroup." Is there some loss of functionality when using class modules like this?

[Kenneth Hobs]

The link that I referenced used the Change event. Apply like concepts for the other 2 events that you want to add.

Were you wanting to use one code for the Enter event for all textbox controls and another for the Exit event for all textbox controls or 2 separate codes for each event?

If you can explain what you want to happen for each event for which controls and supply a short example xls, we can help you a bit easier.

[nst1107]

The _Change() event I have no trouble with. It appears in the procedure combobox in the top-right of the VBE (is there a name for that box?). I'm struggling with the other two I mentioned that don't appear there and that I haven't been able to get my VB editor to recognize as events for mButtonGroup.

Yes, I want to use one code for the _Enter() event for all textboxes and another for the _Exit() event. I'll post a sample of the workbook.

And, by the way, thanks for that link earlier. I found a link to Pearson's site in that thread. I didn't know his site existed.

[Kenneth Hobs]

Can you explain what you want to happen when something is done?

I gather you have more than 4 editbox controls for your production version of your userform?

If I just had 4 controls to act on, it is easy enough to write a sub and pass the that control to it for each event for each control.

Sometimes, a class solution is more trouble than it is worth.

[nst1107]

Actually, this is more of a test run to see if this idea will work with a different, much larger project. So, yes, the small number of textboxes does look kind of ridiculous, but the small number also keeps the confusion low for me.

Here is what I have in mind.
1.)When a textbox is entered, its current value is stored in a variable.
2.)The user edits the contents of the textbox, but is restrained from entering non-integers through use of the _KeyPress() event.
3.)The _Change() event updates the above variable. In case the user pastes data to the textbox, the _Change() event provides further screening and will revert the textbox value to the value stored in the variable if bad data is pasted.
4.)In the larger project, I will eventually use the _Exit() event to facilitate immediate updating of the worksheet instead of waiting until the Userform_Close() event as in this project.

[nst1107]

I'd like to bring this up again. Is there a solution?

[Kenneth Hobs]

I will work an example today. With the up and down status of the forum I forgot about this and I was out of the office.

I will just be appying the concepts of the thread that I referenced.

[nst1107]

Thank you, Mr. Hobs.

[Bob Phillips]

The _Change() event I have no trouble with. It appears in the procedure combobox in the top-right of the VBE (is there a name for that box?). I'm struggling with the other two I mentioned that don't appear there and that I haven't been able to get my VB editor to recognize as events for mButtonGroup.

Yes, I want to use one code for the _Enter() event for all textboxes and another for the _Exit() event. I'll post a sample of the workbook.

And, by the way, thanks for that link earlier. I found a link to Pearson's site in that thread. I didn't know his site existed.

Some events are inherited from the container object, the UserForm here.
Within the events class, you are effectively trapping a
control's events outside of that container, so there is no access to
inherited events (they aren't exposed by that control). AfterUpdate is one
of these.

[nst1107]

So, the answer is no, I can't do this through a class module. If that's so, it sucks, but at least I still can trap the _Change() and _KeyPress() events. Thanks for your explaination, Bob.

In case there's more to this, I'll wait for your update, Mr. Hobs, before I mark this as solved.

[Kenneth Hobs]

XLD knows more about this than I do. So, as he says, we can not do it by the Exit and Enter events in the class. You are limited to those events in the drop list as he said.

However, you can do the Change and Key press events that you wanted. I modified that one class for change events in all text boxes to this code.

In class named cTextboxes:
[vba]Private WithEvents tbx1 As msforms.TextBox

Sub cSetTextbox(ByVal tbx As msforms.TextBox)
Set tbx1 = tbx
End Sub

'Integers only in all textboxes
Private Sub tbx1_Change()
With tbx1
On Error Resume Next
If .Value <> vbNullString Then
If CInt(.Value) <> .Value Or InStr(.Value, ".") Then
On Error GoTo 0
.Value = vbNullString
MsgBox tbx1.Name & " change event set value to nothing."
End If
End If
End With
End Sub[/vba]
In your userform:
[vba]
Dim cTextBox() As cTextBoxes

Private Sub UserForm_Initialize()
Dim obj As msforms.Control, i As Long

For Each obj In Me.Controls
If TypeName(obj) = "TextBox" Then
i = i + 1
ReDim Preserve cTextBox(i)
Set cTextBox(i) = New cTextBoxes
cTextBox(i).cSetTextbox obj
obj.Tag = i
End If
Next
End Sub[/vba]
However, all is not lost. You can use a macro to write the macros in one of 2 ways. (1) Use VBComponents or (2) write what you need to a sheet and then cut and paste that to your userform. Obviously, for these methods, each change event could pass the control to one Sub which would make each textbox event's Sub, a 3 liner.

Here is a method that I used to write temporary code. See Chip's site if you want further details.
[vba]Sub Test()
'Dim the_Calc As String
'the_Calc = "Range(""B13"").Formula = ""=SUM(D1214)"""
'CommandAsString the_Calc
CommandAsString Range("B5").Value
End Sub

'For other tips, see Chip Pearson's, http://www.cpearson.com/excel/vbe.aspx

Sub CommandAsString(cmdString As String)
'Add: Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
Dim MyComponent As VBComponent
Set MyComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

Dim MyCodeString As String: MyCodeString = "Public Sub VBACommandAsString" & vbCrLf
MyCodeString = MyCodeString & cmdString & vbCrLf
MyCodeString = MyCodeString & "End Sub" & vbCrLf

MyComponent.CodeModule.AddFromString MyCodeString

Application.Run "VBACommandAsString"

'Debug.Print ThisWorkbook.VBProject.VBComponents.Count

ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents.Item(MyComponent.Name)
End Sub[/vba]

[nst1107]

Okay. I get what you're saying. I might give that a shot. Maybe the next release of Excel will support this "exposing" of "inherited events".

Edit: Thanks!!

[GTO]

Hi fellas,

Not sure if this can get tied into the existing code, as I have only tried a simplified example on its own. Anyways, in case of any value, I did locate an example by Colo, where RaiseEvent and two events are used (all of which are well beyond me, and I am hoping someone takes pity and provides a dumbed-down & detailed explanation).

While awfully mysterious to me, appears quite nifty, maybe of use?

See Colos example at post #2

http://www.mrexcel.com/forum/showthread.php?t=66773

I was too curious to resist, so here's a simplified example wb.

Have a great weekend!

Mark

[Bob Phillips]

Okay. I get what you're saying. I might give that a shot. Maybe the next release of Excel will support this "exposing" of "inherited events".

Not a chance. The only changes to VBA will be in the area of supporting new functions in Excel.

I have some code somewhere that emulates the Exit event trapping keyup and so on.

I will see if I can dig it out.

[nst1107]

GTO, you're right, that is some neat code. Looks like I'll have to change my approach a little, but if I can find a way to fit it in, it just might do the trick. I was a little leery about the continuous looping, but it doesn't look like it will slow anything down.

[GTO]

Hi Nate,

Re the looping: Yea, I hate it when I can't step thru stuff, cuz its the way most stuff starts making sense to me.

You might want to wait a bit to see if the good Mr. Phillips can find the code he's looking for. Personally, I'm hoping (subliminal hint) Bob wants to explain a bit about the scary dark world of classes...

Mark