Message Box and Verify selection in a multiselect listbox issues.

[daustin]

I have Word 2010 runing on Win7. I am new to VBA and I am haveing trouble with two things:
First: this code returns two message boxes everytime Me.txtFireCredit.value = 0;
[VBA]Private Sub CKFire1_Click()
If Me.txtFireCredit.Value = 0 Then
MsgBox "You have selected 0 credit hours for Fire. If you wish to change this please do so on the Course Information Page."
CKFire1.Enabled = False
CKFire1.Value = False
GoTo NrmFire1
Exit Sub
End If
NrmFire1:
If CKFire1.Value = False Then
Fire1.Visible = False
ElseIf CKFire1.Value = True Then
Fire1.Visible = True
End If
End Sub
[/VBA]
Second, I have a list box in a frame that is enabled by the check boxes above name goes CKFire1 = Fire check box, Fire1 = Fire Frame with a list box, LBFire1 = listbox inside the frame. I am having no problems getting the frames to show up but i can not make the check box enable again if noting is selected in the list box: here is the code :
[VBA]
Private Sub CKBld1_Click()
Dim i As Integer
Dim j As Integer
If Me.txtBldCredit.Value = "0" Or Me.txtBldCredit.Value = "00" Then
MsgBox "You have selected 0 credit hours for Building. If you wish to change this please do so on the Course Information Page."
CKBld1.Enabled = False And CKBld1.Value = False And Bld1.Visible = False
GoTo NrmBld1
Exit Sub
End If
NrmBld1:
For i = 0 To LBBld1.ListCount - 1
If LBBld1.Selected(i) Then j = j + 1
Next i
If j > 0 Then
CKBld1.Enabled = True
CKBld1.Locked = False
ElseIf j = 0 Then
CKBld1.Enabled = False
CKBld1.Locked = True
End If
If CKBld1.Value = False Then
Bld1.Visible = False
ElseIf CKBld1.Value = True Then
Bld1.Visible = True
End If
End Sub
[/VBA]
Thank you in advace, this has been bothering me all morning

[Frosty]

Have you stepped through all of the code? You need to use breakpoints and then work with the form... seeing what events are firing and when, especially if you're changing values. You're not showing enough code to actually do more than guess.

But it's always a dangerous practice to change values of controls within their own events. It's fine if you want controls to have relationships... but I wouldn't typically use the _Click event of a checkbox control to change the value of that same checkbox control to False. If I want that kind of behavior, then the control should already be disabled (probably based on the value of the txtFireCredit, using the _Change event, _AfterUpdate or something else.

[VBA]
Private Sub CKFire1_Click()

If Me.txtFireCredit.Value = 0 Then
MsgBox "You have selected 0 credit hours for Fire. If you wish to change this please do so on the Course Information Page."
'this kind of data validation should be in the _Change event of the txtFireCredit,
'rather than here. If your workflow requires additional validation, then it should
'happen when you dismiss your form entirely (like pressing the OK button)
CKFire1.Enabled = False
CKFire1.Value = False
End If

'this code is fine and makes sense-- create relationship of the value of this control to another
If CKFire1.Value = False Then
Fire1.Visible = False
ElseIf CKFire1.Value = True Then
Fire1.Visible = True
End If
End Sub
[/VBA]

As for the second part-- I don't know what you mean, I'm sorry. I don't understand the question well enough. Can you restate?

I think you might want to build a separate routine in the form called "ValidateData" and consolidate all of this functionality in terms of displaying message boxes based on values, and enabling/disabling certain controls. From there, you can call that ValidateData subroutine from various events (especially when you might decide the _AfterUpdate or _OnExit events are better for a specific control than the _Change or _Click event.

[daustin]

Thank you for the reply. When I say I am new, I really mean new. I have no training in VBA and am teaching myself how to write the code from scratch and reviewing forums. I know this pratice is dangerous but this is for a side project and I can take my time.
The bolded text, seems to have fixed the second issue: [vba]Private Sub CKBld1_Click()
Dim i As Integer
Dim j As Integer

If Me.txtBldCredit.Value = 0 Then
MsgBox "You have selected 0 credit hours for Building. If you wish to change this please do so on the Course Information Page."
CKBld1.Enabled = False
CKBld1.Value = False
GoTo NrmBld1
End If
NrmBld1:
If CKBld1.Value = False Then
Bld1.Visible = False
ElseIf CKBld1.Value = True Then
Bld1.Visible = True
End If
For i = 0 To LBBld1.ListCount - 1
If LBBld1.Selected(i) Then j = j + 1
Next i
If j > 0 And CKBld1.Locked = True Then
MsgBox "You have " & j & " items selected. You must deselect all the the chapters in the building code box before you can close it.", vbExclamation
End If
End Sub
Private Sub LBBld1_Change()
Dim i As Integer
Dim j As Integer
j = 0
For i = 0 To LBBld1.ListCount - 1
If LBBld1.Selected(i) Then j = j + 1
Next i
If j = 0 Then CKBld1.Locked = False
If j > 0 Then CKBld1.Locked = True
End Sub[/vba]
I am still having the same trouble with the message box appearing twice.I would really like the message box to appear when the user clicks the check box if no hours are entered in the txtfirecredit field. The bolded text above works well it just repeats twice, i.e. you have to click ok two times to get the box to dissapear.

I have this code for the txtFireCredit _Change:
[vba]Private Sub txtFireCredit_Change()
On Error GoTo Err_Fire
Dim pSpan
If Not IsNumeric(Me.txtFireCredit) Then
If Len(Me.txtFireCredit) > 1 Then
Me.txtFireCredit.Text = Left(Me.txtFireCredit.Text, Len(Me.txtFireCredit.Text) - 1)
Else
Me.txtFireCredit.Text = ""
End If
Exit Sub
End If
If Len(Me.txtFireCredit) > 0 Then
Me.txtFireCredit.Text = Left(Me.txtFireCredit.Text, 2)
End If
If Val(Me.txtFireCredit) > 12 Then

Me.txtFireCredit.Text = Me.txtCourseHours.Text
End If
lngFireCredit = Me.txtFireCredit
If lngFireCredit > Me.txtCourseHours Then
MsgBox "You can not have more hours for a certificate than requested for the class"
Me.txtFireCredit = Me.txtCourseHours
ElseIf Me.txtFireCredit < 0 Then
Me.txtFireCredit = 0
End If

'Resets the Check boxes if the value changes
CKFire1.Enabled = True
CKFire1.Value = False
Exit Sub
Err_Fire:
Me.txtFireCredit = 0
Resume
End Sub[/vba]

Thanks again for your help

[Frosty]

Try setting a breakpoint (F9) on a couple of the related events. When you click on controls in your user form, you'll be able to establish what events are firing when.

Forget the rest of the coding within your events, you just need to increase your understanding of when events fire (and what events).

Many many events fire when you "click" a checkbox on a userform. Whether you're using all of those events is a different thing, but without you posting the user form, I would suggest creating a dummy userform, with a couple of controls, and then creating all the events you can for those controls. Put a single msgbox line like "Click Fired" and "Change fired" and "After Update fired" etc... so you can see the order of events.

Then try putting in changing the value of the same control or an associated control, and see how many times the events fire.

It requires some patience and some learning, but you'll get there. You can really spin yourself in circles if you start changing values of controls within their own events, so you may need create work-arounds.

For example, you could utilize a private boolean variable to the user form to get around the double-firing issue specifically for the message box, but this would be a real kluge, and you'd just run into the same issue over and over again.

It would be better to learn how the events are firing and when. Then you'll start to learn which ones are best for the end-user experience you desire. It's not always going to be the _Click event (for example, the Click event for a checkbox ignores the end-user entering the checkbox via the TAB key and then changing the value via the SPACE bar).

[Frosty]

I can give you easy answers, but especially as you are new and learning this all, it would be better to avoid developing bad habits (and by that, I mean, not knowing what events are appropriate to what task, and thus always relying on the wrong event and a bunch of code simply because you chose the wrong event to do what you wanted to do).

The easy answer to the double messagebox is simply this... declare a private variable at the top of your form, like this (not in any procedure, but right near where Option Explicit is -- if you *don't* have Option Explicit at the top of your user form, you should).

Dim bAlreadyFired As Boolean

And then in the routine where you do the messagebox, you simply do...
[VBA]
If Me.txtBldCredit.Value = 0 Then
If bAlreadyFired = False Then
msgbox "yada"
End If
bAlreadyFired = True
Else
bAlreadyFired = False
End If
[/VBA]

But that is a really klugey workaround, and the more you end up having to use private variables to avoid multiple firings of events, the more likely it is that you are simply coding work-arounds to your lack of knowledge, rather than increasing your own knowledge.

Make sense?

Incidentally, I'm pretty sure there are easier ways to do what you're doing in LBBld1_Change.

The .ListIndex property of a list control will be -1 if nothing is selected (even on multi-select listboxes). So that code could be simplified to

If LBBld1.ListIndex = -1 Then
CKBld1.Locked = False
Else
CKBld1.Locked = True
End If

[fumei]

Just a point...

Me.txtFireCredit.Value = 0

.Value is whatever ANY control uses for default. If you mean to use what text value is in the control, use .Text, rather than .Value.

Me.txtFireCredit.Text = "0"

OR

Clng(Me.txtFireCredit.Text) = 0

Most of the time using .Value for a control is fine, but I think it is better to be explicit. If you mean to use .Text, then use .Text.

[daustin]

Thank you both for your assistance. I have the form working the way that I would like with one exception. When the user selects an item from the list box, then changes the corresponding credit hours to 0 the list box locks until the user deselects all the items in the list box. I would like it to hide the list box and lock the check box one the list box selected count is 0.

Or,is there a way to automatically do this when the user changes the credit hours to zero?


Your suggestions on placing the conformation in the _change() property worked out well.
Is there a better way to code the verification? Right now only the first 3 buttons for each(Building, Fire, Mechanical, Plumbing and Electrical) work like they are supopsed to.
Are there any suggestions/nudges in the right direction to make the coding look a bit neater.

Please remember I have no formal computer language training other than Fortran, and that was about 10 years ago. Only working on this for about a week.

[fumei]

I would say you are doing quite admirably, and you certainly are showing you are willing to listen and make some effort. Good on you.

Unfortunately I can not open your docm file to see your code. So I am afraid you are going to have to deal with Frosty...you are in excellent hands.

[daustin]

Fumei that is not a good thing, is it something with the document?

This is meant for a lot of people to look at and use.

Any thoughts on why someone couldn't open the document?

[Frosty]

Fumei can't read it because he doesn't have Word 2007 or Word 2010. Not because the file is corrupt.

However... you have a reference in that .docm file which is not a standard reference. You are using controls from the Microsoft Window Common Controls 2-6.0. I don't have that mscomct2.ocx on my computer. I attempted to load it and had an issue. I glanced at your form, but it is massively complex and would require a lot of time to glance through a lot of code, since I can't actually run it.

You may also have this issue with other users, if you are using a non-standard .ocx which is not typically distributed via Windows 7.

So... you have some pretty big decisions to make:
1. Where did you get this code? You didn't code this in the last week. Why are you using these special controls? Can you accomplish what you want without using mscomct2.ocx?

2. Can you provide a simplified mock-up document which has a userform that approximates what you want to have happen?

Otherwise, I'm afraid you can only get the most generalized help in terms of helping you solve your problem.

I also suspect you're going to have distribution problems of this macro, because of the reference problem.

How do you want to proceed? The best generalize help I can give you I've already given: start putting in break points at your various events to see when they fire. Sort out what you want to happen when, and try to avoid having events of specific controls change the values of those controls.

Typically... you select an option, and then you display/lock/disable/unlock/enable other controls... not change values of other controls (unless it is the context of disabling and setting a value at the same time).

[daustin]

I have removed the

Microsoft Window Common Controls 2-6.0. I don't have that mscomct2.ocx

from the controls and everything is working.
My issues at the moment are:

• I would like it to hide the list box and lock the check box one the list box selected count is 0.
• Or,is there a way to automatically do this when the user changes the credit hours to zero?
• I can't for the life of me figure out how to start my save buttons yet. one thing at a time.

I have also zipped it and put it into 97-03 template form. If anyone else was having trouble with it.

I wrote most of my code, well wrote one section then copied and pasted it. I used websites, to figure somethings out, like the phone number, spin buttons and long list. Greg Maxey has one, I can not currently post links since this is only my 4th post. I used that one for the phone number, some of the spin buttons and how to do the long list.

Thanks again for the help.

[Frosty]

Greg is a regular poster on this board, so you don't need to post a link to his website.

To me, the biggest design flaw you have is the massive amount of copied code and controls you have in your form. It's a design flaw because it is a really unwieldy/unscalable solution. It's also a problem because you've copied a structure which you haven't fully fleshed out.

There are a number if quibbles I have about your overall form design (what's with the blue "tab here" text everywhere? But mostly I think the solution begins here: get rid of all the extra copied pages that are the 2-12 stuff. Simplify your form and code so that we can chat about how this will work.

Set up your tab orders, figure out what controls are supposed to be associated with other controls. Right now this thing is too complex to actually discuss. You've painted yourself into a corner, developmentally. I think you need to take a step back and nail down some conceptual approaches. One step backward to take two steps forward, kind of thing.

[daustin]

Thanks for the input. I will start a simpler one tomorrow. Now that I have an understanding on how to call the pages. It makes much more sense to work on a small form to figure out the few issues I have left.

Guess I got over enthusiastic about getting most of it to work. I lost sight of the little things.

Just an FYI the blue 'Tab Key' text was to let the user know they could hit the tab key. Was it to much?

[Frosty]

I don't know your end-users, so I can't tell you what is too much and what isn't. But since the TAB key as a way to progress through forms has been around for awhile, I think it's probably too much. For those who don't know that already, they're probably going to be looking at the mouse and back to the screen and then back to the mouse as they move from place to place on the form

For the other... it looks like you've got three main pieces of info to gather:
1. Curriculum info
2. Contact info
3. Course(s) info

I would really hash out those three things, get it all working just for a single course, and then it shouldn't be too tough to add multiples. I think you're probably going to want to use a main form and a sub form.

Main criteria to figure out (and maybe you already know these answers, but it's not apparent to me in perusing the form)

1. What are all of your limiters (i.e., if a 1 hour curriculum, can't have more than 1 60 minute course... if you haven't selected building, don't show building chapters list)
2. Where do you want the information saved (both in-progress, which is possible, and after the end-user has completed the form).
3. How does the end-user modify the data entry?
4. How are you planning on deploying this to these people... and by extension, how do you plan to upgrade when you have an issue which needs to be corrected?

That should be a good way to get you started. And stay in .dot format, that way Fumei (and others) may be able to weigh in a bit more easily.

Cheers!

[fumei]

Holy crap. That is a complicated form for very experienced VBA programmers. There are some convoluted series of logic gates. Good grief.

It will take me some time to try and work through it...so I will reiterate comments Frosty made. Get rid of the extraneous pages for now. Better yet make a simple version.

Answer Frosty's four questions.

Yikes. This is very ambitious, but good for you.

[daustin]

Okay, I have simplified the form a bit. 4 pages instead of 14.

To answer your questions:

Main criteria to figure out (and maybe you already know these answers, but it's not apparent to me in perusing the form)

1. What are all of your limiters (i.e., if a 1 hour curriculum, can't have more than 1 60 minute course... if you haven't selected building, don't show building chapters list)
2. Where do you want the information saved (both in-progress, which is possible, and after the end-user has completed the form).
3. How does the end-user modify the data entry?
4. How are you planning on deploying this to these people... and by extension, how do you plan to upgrade when you have an issue which needs to be corrected?

1. The only real limiters are just that- if they have not selected building it should not show. It is set now that they are able to go back and change the values which in turn enables or disables the check boxes for the buildng list. A 1 hour ciriculum however can have each of the courses in it, building,fire,electrical, mechanical and plumbing.
2. We would like the information to save while in progress and at the end (last module save button) save the document as CourseTitleSponsorNumberDate, and tell the user the title and where it was saved.
3. This one is a little tricky for me as, I thought the only method of entry was the user form. We would like the user to be able to open the saved document and edit the data at their will.
4. The deployment will be through internet download. Updates will be posted to the internet.

okay in this simplified one, Ithink some of the 'cluter' comes from my lack of vba knowledge , I have added in functions for some of the code that was repeated in the buttons.

[vba]'Makes the module pages visible
Private Function ModVisible()
Dim x As Integer
Dim y As Integer
x = MultiPage1.Pages(0).txtChours.Value
For y = 0 To x + 1
Me.MultiPage1.Pages(y).Visible = True
Next y
End Function
'Rsets the module pages if the course hours change
Private Function ModReset()
Dim x As Integer
Dim y As Integer
x = MultiPage1.Pages(0).txtChours.Value
For y = (((x + 2) - 2) + 2) To 3
Me.MultiPage1.Pages(y).Visible = False
Next y
End Function[/vba]

I have also solved the problem i had earlier

Or,is there a way to automatically do this when the user changes the credit hours to zero?

The red text fixed that:
[vba]'Disables the check boxes if 0 building credits are selected:
If Me.txtBldC = 0 Then
y = 0
Me.CKB1.Enabled = False
Me.CKB2.Enabled = False
' Checks to see if anything is selected in the Building chapter list box for Module 1:
If CKB1.Locked = True Then
x = 0
If y = x Then
MultiPage1.Value = 2
j = 0
For i = 0 To LBB1.ListCount - 1
If LBB1.Selected(i) Then j = j + 1
Next i
If MsgBox("You have " & j & " chapters selected in Module 1 under the Building Code." _
& " Click 'OK' to clear the selected chapters.", vbOKCancel) = vbOK Then
For i = 0 To LBB1.ListCount - 1
LBB1.Selected(i) = False
Next i
CKB1.Value = False
CKB1.Enabled = False
Else:
CKB1.Locked = False
CKB1.Enabled = True
Building1.Visible = True
Me.txtBldC = 0
End If
End If
End If
' Checks to see if anything is selected in the Building chapter list box for Module 2:
If CKB2.Locked = True Then
x = 0
If y = x Then
MultiPage1.Value = 3
j = 0
For i = 0 To LBB2.ListCount - 1
If LBB2.Selected(i) Then j = j + 1
Next i
If MsgBox("You have " & j & " chapters selected in Module 2 under the Building Code." _
& " Click 'OK' to clear the selected chapters.", vbOKCancel) = vbOK Then
For i = 0 To LBB2.ListCount - 1
LBB2.Selected(i) = False
Next i
CKB2.Value = False
CKB2.Enabled = False
CKB2.Locked = False
Else:
CKB2.Locked = False
CKB2.Enabled = True
Building2.Visible = True
Me.txtBldC = 0
End If
End If
End If
End If[/vba]

I think some of the problem is that I(beleive that I) need so many variables. I would love to write some functions that could take away from some of the code. I tried all day to get this working, thinking this small one would lead me to the larger ones:
[vba]Private Function NextVisible()
'Makes the next button invisible if the next page in the set is not visible.
Dim MyArray(1 To 3) As String
MyArray(1) = Me.cmdNextBtnMod1
MyArray(2) = Me.cmdNextBtnMod2
MyArray(3) = Me.cmdNextBtnMod3
Dim y As Variant
Dim x As Integer
Dim z As Variant
For x = 2 To 4
For y = LBound(MyArray) To UBound(MyArray)
z = "Me.cmdNextBtnMod" & y
If Me.MultiPage1.Pages(x).Visible = False Then
MsgBox z
z.Visible = False
ElseIf Me.MultiPage1.Pages(x).Visible = True Then
MsgBox z
z.Visible = True
End If
Next y
Next x
'original, this works in the code trying to get rid of these lines
'If Me.MultiPage1.Pages(3).Visible = False Then
' Me.cmdNextBtnMod1.Visible = False
'Else: Me.cmdNextBtnMod1.Visible = True
End If
End Function[/vba]
I am begining to think that you can not make a variable and use it as an object. It would always fail here: z.Visible = False , told me that I need an object but the msgbox read what it needed to say?/shrug

I have a habit of taking really big bites, but I didnt think the code was that complex just a lot of var's and pages. Which is why I started looking into the replacing an object with a variable in a loop.

It's the weekend, the Glenlivet Nardurra, will clear my head and back at it Monday morning.

[Frosty]

It's obvious you have programming experience. I think the main problem is that you don't have object-oriented programming experience. The problem, in a nutshell, is that this will be far far easier to accomplish (and modify later) if you start exploring a totally different data structure (and thus, a bit of a different user interface).

Before I really look at your code, I just want to explore the concept of UDTs with you, to see if this seems useful.

You have an instructional course, the details of which you are asking the end-user about. That course has a name, as well as several other properties. For explanation purposes, I'm going to keep it simple.

Right now, you think you need to dimension individual variables for each possible course (this is why you think you need so many variables), because you think you need to create a userform with as many pages as you might allow total number of courses, and thus all of those extra controls (since controls have to have unique names).

I know these aren't the actual variables, but this for demonstration purposes.
[VBA]
Dim strCourseName1 As String
Dim strCourseLocation1 As String
Dim strCourseName2 As String
Dim strCourseLocation2 As String
Dim strCourseName3 As String
Dim strCourseLocation3 As String
[/VBA]
etc.

However, you don't need to do this. Not in your main CallUF routine, nor in your CEForm.

There are several better approaches to this. I'm going to explain the one that is easiest to explain, although it is not the one that I would use myself.

You can use a UDT ("User Defined Type") as a repository for all of the information you have about a course.

Here's a brief demo of populating a couple of UDT instances, as well as adding them into an array. I've purposefully mixed and matched the start value of 1 and 0 so that you can see that it doesn't matter (although I wouldn't leave this kind of inconsistency in the final product, this is just for demonstration).

You should step through this code, as well as make sure to view the Locals Window and the Immediate Window within the VBA Editor (under the view menu):
[VBA]
Public Type ptInstructionalCourse
CourseName As String
CourseLocation As String
End Type
Sub DemoUDTs()
Dim oMyCourse As ptInstructionalCourse
Dim i As Integer
Dim aryUDTs() As ptInstructionalCourse

'initialize our array
ReDim aryUDTs(0)

With oMyCourse
For i = 1 To 2
Select Case i
Case 1
.CourseName = "Course 1"
.CourseLocation = "Course Location 1"
Case 2
.CourseName = "Course 2"
.CourseLocation = "Course Location 3"

Case Else

End Select
'add it to the array
ReDim Preserve aryUDTs(i - 1)
aryUDTs(i - 1) = oMyCourse
Next
End With

For i = LBound(aryUDTs) To UBound(aryUDTs)
Debug.Print aryUDTs(i).CourseName
Next
End Sub
[/VBA]

The other two approaches, which I think I mentioned previously, would be to create a custom class (serving the purpose of the UDT) and add that class to a collection (serving the same purpose of the array). *OR* you could simply utilize a separate user form for the purposes of getting the individual course info (where you are using additional pages on the main userform), and add the actual instances of the userform to a collection. This last is the most complicated to explain, but is the way I would approach it.

This is simply a re-organization of the way you're thinking about holding this data. Trust me when I say that if you do something like the above, a *LOT* of the rest of the coding will end up seeming easy.

Where to go from here: I think you should investigate using two separate simple forms with no pages-- just two simple user forms: 1 which gathers generic info (Pages 1 and 2 of your existing form), and another which gathers info about a specific course (Page 3 of your existing form). I would forget about the output, for the moment.

Don't need all this Next/Previous stuff... you just need some add/edit/remove buttons associated with the Course Names displayed in a list box on the main form. And then you can select a particular course, click the Edit button, and an instance of your "SpecificCourseInfo" subform pops up with the right settings, based on your UDT.

Can you give that a whirl? I know this feels like overly simplistic major steps backwards, but it will help in the end.

Enjoy the Glenlivet and the weekend!

[fumei]

Jason,

*OR* you could simply utilize a separate user form for the purposes of getting the individual course info (where you are using additional pages on the main userform), and add the actual instances of the userform to a collection. This last is the most complicated to explain, but is the way I would approach it.

you would have individual userforms (in a collection) for each course? Interesting. Yes, I would enjoy watching you explain that...

It is actually a - or could be - an very efficient method to the problem. Depending on the number of courses involved, how you handle the data flow would be most, hmmmm, amusing.


And I will sip my vintage (1948!) Armagnac...I only have about a sixth of the bottle left. So sad. Mind you, it has taken me almost two years to get through that much.

[Frosty]

Well, userforms are just classes. And they already hold all the data you care about. So rather than an array of UDT middle men or a collection of custom class middlemen, you just have a collection of individual instance userforms.

It's a hard thing to explain because the locals window would be so messy... But it's the most efficient way of doing it. Userform to hold the individual data, collection to hold as many as you need, and a regular procedure to grab the data and dump into a document. Maybe some autotext boilerplate stored so that the course info can be easily repeated, although we haven't discussed output yet.

But that is a tough series of concepts to explain without already understanding UDTs, I think.

[gmaxey]

Jason,

Sounds interesting, but I can't seem to be able to implement it. I've got a form with two text boxes "Course" and "Location." I display it for as many times as the user want to add courses. I'm trying to add each userform to a collection a they are completed. It seems that as each new form is added to the collection it makes a dog's breakfast out of the one preceeding it. Thereofore I am only able to output the last form added.

What am I missing? Thanks.

[VBA]ub GetCourseInfo()
Dim oFrmInput As UserForm1
Dim oObj As Object
Dim bIWantSomeMoIWantSomeMo As Boolean
Dim oCol As Collection
Dim i 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
Debug.Print oCol.Count
'For i = 1 To oCol.Count
' Set oObj = oCol(i)
' Debug.Print oObj.Item(0).Text
' Debug.Print oObj.Item(1).Text
'Next i
Set oObj = oCol(oCol.Count)
Debug.Print oObj.Item(0).Text
Debug.Print oObj.Item(1).Text

End Sub[/VBA]