Creating an interactive userform

[pvman]

Hello to all experts. Perhaps you can help me with the following problem:

I am a novice VBA user, with a more-than-basic knowledge.
I have created several userforms in the past, but they were all “static”, meaning, the user was allowed certain options from a variety of combo boxes, and after filling all the data, the result was migrated into a sheet (database).

Now, I am trying to create an INTERACTIVE userform in which certain combo boxes will become available based on a user’s selection in a previous combo in the same form.
For example:
If - in a “Payment” combo - the user will select “Check”, 3 new text boxes will appear (Date, Sum, Bank code) and the user will fill certain data related to his selection.
If, on the other hand, the user will select (in the “Payment” box) the option “Installments”, 3 other boxes will appear (Number of installments, first installment month, sum per installment). These boxes can either be text boxes of combo boxes, and here the user will select/fill the relevant data.

Naturally, the previous boxes, related to the “Check” option, will be made invisible.
Personally, I don’t care if ALL boxes are always visible, as long as they are activated or disabled (grayed out) based on user’s selection in the “Payment” combo.

The next step will be to migrate the selected/filled data from the form back into a sheet, where the data will be placed in different columns based on the final selection the user made in the form.

Can any one help me out with a sample file of a basic code that I can work with?
After reading here, I ran a search on "Generic Events" and "Class Modules", but I must admit that I did not really understand how to use the answers to suit my needs.

TIA

 

[pvman]

Thanks, ChrisM.
I'll try to make it work they way you suggested.

 

[pvman]

Norie,

You are correct about the all the hiding and unhiding.
I started already building this thing and it is not going to be easy...
Perhaps your idea to do this with forms (instead of tabs in a multipage) is much better after all, However, I have never tried initializing a userform from within a userform (there should be conbo boxes in the "sub form"), nor do I know how to "return" to the master form and then migrate ALL the data (from the two forms) to the same line in the database. This is why the multipage seemed like an relatively easy solution (simply becuase I think I know how to actually do it )...

BR

 

[Norie]

I think the multipage idea is perhaps the best way to go.

Opening/closing one userform to another etc is pretty straightforward but could get complicated, it really depends on how much flexibility you want.

If you know the names of the forms it would be as easy as something like this.

Private Sub cmdOpenForm2_Click
' command button on form to close it and show UserForm2
      Unload Me ' Me refers to the userform the code is in
      UserForm2.Show
End Sub

 

[pvman]

I see the end...

Okay... Almost everything is working as planned, but I have one last challenge with this project (I hope).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
In the “Installment” section, the user selects the number of payments AND the first payment month (and then clicks the “add record” button).<o></o>
Basically, the code should now create (in the database sheet) a set of records containing the information collected from the form.<o></o>
Normally, when dealing with a single payment, my code creates ONE record in the database.<o></o>
When dealing with payments, the code should create a number of identical records, based on the number of payments requested by the user (2-24). This I know how to do. What I DON’T know how to do, I how to assign the proper month for each record.<o></o>
<o></o>
One of the variables that the user selects (from a combo), is the “First payment month”. If the user selects January as the first payment month, and 4 is the number of payments (for example), the database records should show 4 identical record lines BUT under the Month’s column the name of the month should change (1<SUP>st</SUP> line should be January, 2<SUP>nd</SUP> - February, 3<SUP>rd</SUP> -March and 4<SUP>th</SUP> – April).<o></o>
If the first payment month is November, the same should happen but the 4 lines should show November, December, January and February.<o></o>
Also, in order to make life interesting, the YEAR column should now say 2009 for the first 2 payments, and 2010 for the last 2 payments.<o></o>
<o></o>
It sounds/feels like something that involves an array of month names and looping through it according to the number of payments (each time pulling the relevant month name), but this is well beyond the scope of my humble knowledge.<o></o>
Any ideas/leads will be highly appreciated.<o></o>
<o></o>
All the best and that you all, in advance

 

[Norie]

Re: I see the end...

Why do you need multiple 'records' for the installment part?

What are you actually trying to do with your 'database'?

 

[pvman]

Re: I see the end...

Why do you need multiple 'records' for the installment part?

What are you actually trying to do with your 'database'?

Well, if you think about it, this "payment" system is not different than writing checks for each month...
I have a Pivot that shows expenses by month. therefore, i need a record for each month, with its respective payment (expense).
My database actually collects (on a line basis) all expenses done, by date.

I hope I was able to clarify the problem/need.
What I started to so now is build a "table" with all the years and the months, and am now trying to find a loop that will identify the Year and the first month, and then start to loop through the table and pull the payment year and months back into the data sheet.
I don't know how to add an image here, so I can't shpw you the months's table I have created.