Hmm, I'll have to double check my own proof of concept. I used a for each loop to iterate through the collection to check that the values were different, and I assume your form's okay button is just a me.hide, so... I don't know. Just in reading the code, you've approached it generally the same way I did. Let me check tomorrow... Maybe it's not so straight forward as I thought.
Or maybe there's something funky about using an object variable? Did you check the locals window to examine your collection, and see if the collection seemed to have different items there?
Jason,
Actually I didn't have an OK button just the two text boxes and killing the instance of the form with the "X," but it doesn't seem to matter. Adding the the "OK" button with me hide has the same result.
When the first item is added to the collection. It is in fact a form object, it appears as Item 1 with two items itself. However when the next userform is added to the collection, it seems to destroy the first usererm. The collection gets a new item 2 (which has two items) but Col item one is dead. Still there but with no itmes itself.
Let me revised that a bit. It does seem to matter. Killing the userform makes all the difference. I've added the OK button to hide the form and changed the code as shown and it seem to work nicely.
[VBA]Sub GetCourseInfo2()
Dim oFrmInput As UserForm1, oObjForm As Object
Dim bIWantSomeMoIWantSomeMo As Boolean
Dim oCol As Collection
Dim lngIndex As Long
Set oCol = New Collection
bIWantSomeMoIWantSomeMo = True
Do While bIWantSomeMoIWantSomeMo
Set oFrmInput = New UserForm1
oFrmInput.Show
oCol.Add oFrmInput
If MsgBox("Do you wnat to add another course?", vbYesNo, "Add Course") = vbNo Then
bIWantSomeMoIWantSomeMo = False
End If
Loop
For Each oObjForm In oCol
Debug.Print oObjForm.Item(0).Text
Debug.Print oObjForm.Item(1).Text
Set oObjForm = Nothing
Next oObjForm
lbl_Exit:
Exit Sub
End Sub
[/VBA]
I'm assuming the set oObjForm = Nothing
is taking then killing the form instance right?
Actually, I think it's the use of the Red X without adjusting the QueryClose event of the userform. Clicking the Red X is basically the same as triggering an unload. I don't think your oObjForm = Nothing is actually killing the instance inside the collection, just the use of that variable in that loop.
The following works just fine for me... userform with a single text box called txtTest and a single command button called cmdOK.
[VBA]
Sub FormsInACollectionDemo()
Dim colForms As Collection
Dim f As UserForm1
Dim i As Integer
Set colForms = New Collection
'create as many instances of the form, as long as user is filling in data
Do
Set f = New UserForm1
f.Show
If f.txtTest.value <> "" Then
colForms.Add f, f.txtTest.value
End If
Loop Until f.txtTest.text = ""
'iterate throught yhe collection one way
For i = 1 To colForms.Count
Set f = colForms(i)
Debug.Print f.txtTest.text
Next
'and another way
For Each f In colForms
Debug.Print f.txtTest.text
'try these different items... don't see to have an effect on the collection
'Unload f
'Set f = Nothing
Next
End Sub
[/VBA]
And within my userform, I have the following events (which it may be helpful for anyone exploring this and the order of events)...
[vba]
Private Sub UserForm_Initialize()
Debug.Print "Initialize"
End Sub
Private Sub cmdOK_Click()
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Debug.Print "QueryClose"
If CloseMode = 0 Then
Debug.Print "Red X pressed"
'change Cancel value, to avoid the unload
Cancel = 1
'wouldn't normally put a hide here... I'd normally put a cmdCancel_Click
'but this is a form without a cmdCancel button, so this will do
Me.Hide
ElseIf CloseMode = 1 Then
Debug.Print "Unloading the form"
End If
End Sub
Private Sub UserForm_Terminate()
Debug.Print "Terminate"
End Sub
[/vba]
Notice you'll get a bunch of terminate events at the end of the procedure (when the collection gets garbage collected), but you'll only trigger the QueryClose event if you hit the Red X or you use Unload in the calling routine. Setting the form object to nothing doesn't trigger a query close, just a terminate... and it seems that it doesn't even do that if you set the form object to nothing within a for each loop (the iterator becomes nothing, but the item in the collection is still there).
I was re-evaluating my approach to forms based on someone else's post recently, and so I re-learned some stuff I used to know, and learned some new stuff I don't think I ever knew (the differences between Unloading a form and setting a form variable to nothing).
In any event-- regarding the OP... some of the above may be useful as well, if you're interested in exploring something other than the array/UDT approach.
Okay while I am wrapping my head around this new approach I have just one question, posed a few ways.
In the original coding, while it is very cumbersome, the user can see the information that they have entered, go back and edit it as well. How can this be accomplised using collections and arrays?
Can the input be shown visually? Can it be reacalled if the user has entered 4 'modules' since the mistake.
If the user enteres the information for 12 modules, exits the userform then realizes there is a mistake, do they have to reenter all the information again to correct the one mistake on the last module.
Not trying to insult the end users of our products but, they will most likely have to edit something, and they will only realize it after they have entered all the information and are ready to send it off.
Regardless of the answer(s), I will look more into the UDT, collection and arrays.
Yes, you can do all of the above and more. You can store the data wherever you want, display it whenever you want and recall it whenever you want.
There is nothing intrinsic about strVar1 and strVar2 being easier to display or not than aryStrings(1) and aryStrings(2). It's simply a question of how you organize the data. And obviously an array is a heck of a lot easier to add an additional item to, rather than having to go back to the code and add strVar3.
That's the only conceptual lesson here... everything else is actually relatively easy, with the bonus being that as long as you get 1 "lesson" right (I hate to use "modules" since that can get confusing in terms of coding), you can have 3 "lessons" or 12 "lessons" or 50 "lessons" -- it's just a question of how you display them.
In fact, you *could* actually display your form exactly the same way, if that was your desired manner of doing the input (horizontal pages up to a maximum of 12), the difference behind the scenes would be that you don't actually individual controls on individual pages... you simply use the "page change" event to store the data from the controls and then clear out the data of the controls (if you're going forward). If you were going backward, then you would retrieve the previously stored data and put those values into the controls.
However, I wouldn't recommend this UI design, but ultimately you are the decider of how you want your user interface to work, since you are the one that determines what is best for your end-user's.
The only thing I'm saying you absolutely should not do, is the analogy of having:
strVar1 As String
strVar2 As String
when you can have:
aryStrings() As String
Now, you don't *have* to explore the UDT/Collection/Arrays/Classes stuff too much, we can help. I would just say that if you can separate out your two forms and simplify them further so that you aren't using pages (can always bring them back), then it will be relatively easy to help. Don't worry about navigation or any of that, just the data you want to capture from the end-user in a logic order.
And you can take a look at Greg's template, as that seems to (in my cursory overview) be doing some of what we're talking about here.
I think you just need a proof of concept out of this thread, which you can then start to do designing on.
But, if I were project managing you as a programmer, I would say the development order would be something along these lines:
1. Create your frmMain as the user form which grabs all relevant data from the end-user, but no class-specific data.
2. Create your frmClass as the user form which captures all relevant data about a specific class.
3. Create a function which takes all data from frmMain and dumps it into a table at the top of a document, with the first row containing the title "Main Data" -- make sure this function checks to see if a table with the first row containing "Main Data" already exists in the document, and if so, it wipes out all rows by the first one, and repopulates with new data.
4. Create a function which takes all data from frmClass and dumps into a table with the name of the Class (whether the name is "Module1" or whatever else). Make sure it works just like the Main Data function, in that it checks to see if that table already exists.
5. Create a function which grabs data from the Main Data table and populates frmMain with it.
6. Create a function which, when passed a specific "Class Data" table, populates the frmClass with the relevant data.
That's sort of how I'd approach... from there, it will be trivial to adjust your main form to allow you to "navigate" between existing classes, delete classes, etc etc. You wouldn't be obligated to storing your data in Word tables, but it would help you visualize the functionality. From there, you could decide to store the data in Document Variables instead of actually in the document. It really depends on how you want your end-users to interact with the document/macro template
Thanks, I beleive that I have a better understanding of what you are talking about now. I did this after my last post and it makes sense, I will look at greg's stuff tomorrow.
The way this one is now, when you add a 'lesson' it opens infront of the previous 'lesson'. I have the check buttons working now, will mess with the hours tomorrow. try to disable the check boxes depending upon what the user selects as the hours for each course, i.e. building, fire, etc.
Thanks again, this is acutally fun. I really get a kick out of learning something new.
That's really great progress. Couple of quick comments:
1. Turn on Option Explicit as a default (this is under Tools > Options > Editor). The default is to have Auto Syntax Check turned on, and Require Variable Declaration turned off. You should change these to have Auto Syntax Check turned off, and Require Variable Declaration turned on. The first one will stop the constant squawking from VBA that you've typed something wrong, the second will add Option Explicit to the top of all of your code modules. Option Explicit requires you to explicitly declare variables. It saves you from doing something like this:
Dim strMyString As String
strMString = "Hello I'm a totally different variable"
Also -- get used compiling your project often. This is a habit I do as much as saving my work while coding. It's under the Debug menu, but I always add that particular command to a toolbar.
2. I've made a couple of modifications to your project. I wouldn't incorporate this with what you're doing yet, but I think you can begin to see how it would go.
3. The QueryClose events are very important, because otherwise an end-user can mess everything up with a Red X.
4. Set the Cancel property on any Cancel buttons to True-- this allows the end-user to hit the ESC key to essentially get out of the form.
Note: I'm not using a "Cancel" button because it plays havoc with the text field Exit procedure which is intended to validate and require and entry. I did discover that even if the user "X" and kills the form and the oCol.Add statemenet errors the count in the oCol is still upticked. Seems odd.
I haven't had a chance to look at your latest, Greg, but you don't need a cancel button necessarily. You do, however, need to know why the form is now hidden. Since I don't like using Unload in my form code, I typically set up a public constant in my calling module a la
Public USERFORM_CANCEL As String = "Cancelled"
And then in the cancel button (or directly in QueryClose) set the .Tag property of the form to that constant a la
Me.Tag = USERFORM_CANCEL
From there, in my my main routine I check the .Tag property if the userform to determine whether I should do anything with the form. Relying on error trapping won't work, as the form won't be unloaded via the Red X if you chance the Cancel parameter of the QueryClose event.
Fumei, that looks to me like you're spamming. We'll have to report you to the administrators.
Why do you think hitting the Red X is an ungraceful way to exit a userform? The Red X is ubiquitous in the Microsoft environment, and anyone with a computer generally knows that clicking a Red X is a "get me out of here" function. If we could *hide* the Red X in Word VBA (the way you can in Access, if I recall correctly), I could go along with hiding it, but since we can't hide it... I'd rather not have a button which simple appears not to work over programming it to work the way a user would expect it to work (do *something* rather than *nothing*).
My philosophical approach to User Interface design is to, whenever possible, approximate the functionality and design of built-in microsoft dialogs. And the two most basic userforms I come back to are the Format Paragraph dialog (a modal form) and the Insert Cross References dialog (a modeless form). I also like the Find dialog in its approach to the "More" button concept (turning a simple form into a complex form for some uses).
The more similar a custom userform is to a built-in dialog, in terms of functionality, the less you have to explain to a trained user (and untrained users will need the explanation anyway-- with the added bonus that if they learn the custom dialog, then they will be closer to knowing how built-in dialogs work). And even though we can tell users not to click on the Red X, they will still do it... if only because we may not have an opportunity to "train" them away from this behavior.
And thus my argument has always been that a visible Red X should always work just like a Cancel button. But either making it work like a Cancel button or disabling it still requires custom coding (by putting code for the QueryClose event).
What is your argument for disabling the functionality of it? Habit? Or something more?
I think the argument for using the form to unload itself can go both ways. My argument against having the form unload itself goes something like this:
1. Forms need to be called by something (the "calling routine").
2. Garbage collection in a single place is always easier to deal with that garbage collection in multiple places.
3. Except for modeless forms, I like to minimize the amount of "doing" code in a userform, preferring to simply have "get user input" code in the userform, and have "doing" code in the calling routine. Modeless forms obviously have to unload themselves, since the calling routine doesn't stop processing, but even then I just have to make a slight adjustment to allow the Red X to be used the same as the Cancel button (which, on a modeless dialog, I typically name "Done").
The following code assumes the following: userform with a single button called cmdCancel. The .Cancel property of that button is set to True.
When that userform is a modal userform, this would be my "standard" code.
Calling Routine -- modal dialog
[VBA]
Sub DemoFormUsage()
Dim f As UserForm2
Set f = New UserForm2
f.Show
With f
If .Tag = "USER CANCEL" Then
GoTo l_exit
End If
'do stuff, using the info gathered in the userform
End With
l_exit:
Unload f
End Sub
[/VBA]
Form code - modal dialog
[VBA]
Option Explicit
Private Sub cmdCancel_Click()
Debug.Print "Cancel click"
Me.Tag = "USER CANCEL"
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Debug.Print "Initialize"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Debug.Print "QueryClose"
'point the Red X to the cancel button, so all exits of the form are the same
If CloseMode = vbFormControlMenu Then
Cancel = 1
cmdCancel_Click
End If
End Sub
Private Sub UserForm_Terminate()
Debug.Print "Terminate"
End Sub
[/VBA]
Calling Routine - modeless dialog
[VBA]
Sub DemoFormUsage()
Dim f As UserForm2
Set f = New UserForm2
f.Show
End Sub
[/VBA]
Form code - modeless dialog
[VBA]
Option Explicit
Private Sub cmdCancel_Click()
Debug.Print "Cancel click"
Unload Me
End Sub
Private Sub UserForm_Initialize()
Debug.Print "Initialize"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Debug.Print "QueryClose"
'point the Red X to the cancel button, so all exits of the form are the same
If CloseMode = vbFormControlMenu Then
cmdCancel_Click
End If
End Sub
Private Sub UserForm_Terminate()
Debug.Print "Terminate"
End Sub[/VBA]
The main difference between the Red X and the cmdCancel_Click event is the order in which they happen (if you click the Red X, QueryClose happens and then cmdCancel_Click happens, if you click Cancel or ESC, cmdCancel_Click happens, and then QueryClose is triggered during the garbage collection of the Unload method-- or if you simply exit the calling routine). But they both, I *believe* are relatively "graceful" exits of the userform, from the end-user perspective.
EDIT: yes, the .Tag property disappears when the form is completely unloaded... but the order of when the form is unloaded can get a little screwy in the Modeless example above, if the Red X is pressed (which triggers QueryClose followed by cmdCancel_Click, followed by the end of QueryClose, followed by Terminate) -- this may be the kind of scenario Fumei is talking about with an ungraceful exit. I'm not sure why Microsoft designed it this way, but my approach only encounters the strangeness of this design in modeless dialogs, which are considerably less common.
Reply With Quote
