Solved: Manage similar events with textboxes

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

Quote:

---

[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?

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:thumb

I tried that idea. The events never fire.

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

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?

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.

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.

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.

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.

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

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.

Thank you, Mr. Hobs.

Quote:

---

Originally Posted by 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.

---

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.

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.

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(D12:D14)"""
'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]

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!!

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! :thumb

Mark

Quote:

---

Originally Posted by 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".

---

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.

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.

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

Quote:

---

Originally Posted by GTO

Personally, I'm hoping (subliminal hint) Bob wants to explain a bit about the scary dark world of classes...

---

I have been trying to write a decent paper on that subject for years now ...

And I have been waiting to read a decnt paper for even longer.

However, this usage of classes is not an example of the 'real' usage of classes IMO, which is to create a custom object type.

People don't understand them (object classes), and thus do not use them (my brother still doesn't 'get' them, even after 10 years of me badgering him about them), but they have nothing to do with ranges, userforms or controls per se.

Any discourse on classes would be primarily about object classes. The usage of classes to manage control arrays is using classes because classes are the only way to use a variable WithEvents. An object (class) might interact with such controls, but it should be generic in its interface, the front-end should handle those matters.

...and my big yap gets me in trouble again... no wonder I was scared!

Quote:

---

Originally Posted by xld

However, this usage of classes is not an example of the 'real' usage of classes IMO, which is to create a custom object type.

---

Okay, here is where the youngster who's just barely trusted to do valve jobs is listening to an experienced race engine builder casually mention the effects of cam phasing and overlap on calculated compression ratio vs effective... Ya lost me at the term "custom object type".

Quote:

---

Originally Posted by xld

People don't understand them (object classes), and thus do not use them (my brother still doesn't 'get' them, even after 10 years of me badgering him about them)

---

Well... of course it remains frustrating (for me I mean) when one is trying to expand knowledge in something that appears to have some potential, but least I don't feel so glitched...

Quote:

---

Originally Posted by xld

..., but they have nothing to do with ranges, userforms or controls per se.

Any discourse on classes would be primarily about object classes. The usage of classes to manage control arrays is using classes because classes are the only way to use a variable WithEvents. An object (class) might interact with such controls, but it should be generic in its interface, the front-end should handle those matters.

---

The first part of this is making sense to me in that using the class as an aid to the userform (controls) is not the primary goal of classes (though even the WithEvents is a bit abstract to me), but a custom object type is where I go utterly void.

Besides the help file topics of course, would you have any suggestions as to internet searches (keywords), books, or other sources for me to gain at least a workable knowledge of WithEvents and examples of a Custom Object Type?

As always, thank you so much for your time and help Bob,

Mark

Quote:

---

Originally Posted by GTO

.Okay, here is where the youngster who's just barely trusted to do valve jobs is listening to an experienced race engine builder casually mention the effects of cam phasing and overlap on calculated compression ratio vs effective... Ya lost me at the term "custom object type".

---

There is nothing complicated about it. There are objects, or things, everywhere. An object is just something that has attributes (properties in the definition), and can do things or have things done to it (methods). A car is an object, a person is an object, a seat is, and so on. Even Excel is an object, an application object. But, and here is where it gets good, properties of an object can be objects within their own right. So a car, which has properties (wheels, seats, etc.), has object properties too, a wheel has spokes properties, has a tyre property, has an inflate/deflate method, and so on. Excel too has the same, the application object has workbook properties, the workbook has worksheets and so on. You should be able to see where we are going with this, we are describing an object model, a full description of the object, its properties, and its methods.

That is what a custom object is, it is the description of a thing that we need to address. In code terms, when you want to create an object, you create a class and describe the object within that class.

Quote:

---

Originally Posted by GTO

The first part of this is making sense to me in that using the class as an aid to the userform (controls) is not the primary goal of classes (though even the WithEvents is a bit abstract to me), but a custom object type is where I go utterly void.

---

As I said above, the class describes the object. The developer determines what properties and methods of the object (class) are exposed. As a (trivial) example you could have a house object, which has a number of chimneys, but if you don't expose that properties, the code that interfaces with the class cannot set the number of chimneys, nor query the class to tell it how many chimneys the house has.

Quote:

---

Originally Posted by GTO

Besides the help file topics of course, would you have any suggestions as to internet searches (keywords), books, or other sources for me to gain at least a workable knowledge of WithEvents and examples of a Custom Object Type?

---

WithEvents examples will normally just be examples to provide event control that Excel has not provided (examples of not exposing aspects of the class that I mentioned above), such as application events, control arrays and so on. There are plenty of examples of these, but custom objects is another story, there is no good write-up that I have seen.

Quote:

---

Originally Posted by xld

I have been trying to write a decent paper on that subject for years now ...

And I have been waiting to read a decnt paper for even longer.

---

I'd love to read the paper if you ever find/write one.

@XLD:

Greetings Bob,

Sorry I did not acknowledge your answer in a more timely manner. I wanted to read through and think about it, and well... the thinking part was a bit slow and then I got busy.

As I have only really used a class to access the app's events, I certainly believe your explanation helps in increasing my ability to conceptualize it a bit better. As you noted, examples of where classes can be used very effectively seem a bit sparse, and I hope you won't mind having to beat it into my head a bit as I continue to learn. To me leastwise, the first hurdle of conceptualizing objects is not nearly as high a hurdle as the "I get it" hurdle of when/how/what object could be created (wherein said would be advantageuous).

Thank you so much for your time and help :-)

Mark

Quote:

---

Originally Posted by GTO

To me leastwise, the first hurdle of conceptualizing objects is not nearly as high a hurdle as the "I get it" hurdle of when/how/what object could be created (wherein said would be advantageuous).

---

Exactly, that is precisely the problem.

There are a myriad of examples out there of a person class, where you create such a class and populate the proerties (name, gender, DoB, etc.) and they are very good at describing the mechanics of the class. But to quote my dear brother, but I can do all that without a class with just modular code so why a class?

The best answer IMO is that it helps conceptualise the design of the application, you think better of the structure of the code. You can have any number of ways of designing the class, but by getting a good clean design, with a good interface, you abstract the elements of the business model (and every application has a business model as its heart) and can break the problem into easy manageable chunks; you create a good testing model; you provide an straightforward extensibility model.

To me they are essential. But even then I forget just how much. I am just building an application using Excel styles, and I have got into a number of problems in managing them all across many workbooks. On reflection, I think I could have done it better with classes (I think I didn't spot this originally because I was just tapping into Excel objects, why replicate that), and am going to try this today.

I've also had a very hard time learning about classes in the past. I also bugged Bob, asking him to explain classes to me :)

I've recently found a book that I think gives the best description of classes that I've ever read. It also takes you through very real examples of creating classes; complete with objects, properties, and methods. It is Visual Basic 2005 Express Edition Starter Kit from Wrox. It is a Visual Basic Express book, not a VBA book, but if you know VBA, then you pretty much know Visual Basic. And since you can download Visual Basic 2005 Express for free from MSDN, the book would be the only real cost involved.

If you're serious about learning more about classes and want to stretch yourself from an office dev to an office and applications dev, then you should take a look at this book.

@Bob: I can't wait to read your paper, if you ever get it finished.

Greetings Randy,

Thank you so much for the suggestion. Not sure if I'll ever get into app development, but I like learning and can certainly spring for a book.

Thank you again,

Mark