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

 

[phxsportz]

No Problem..

Cheers

 

[dave3009]

Before you get into long if statements it might be worthwhile trying to introduce Select Case from the start.In the case of an interactive userform it will prove much quicker especially as you add more and more options

Private Sub cbo_pay_meth_Change()
    Select Case cbo_pay_meth
        Case Is = "Installments"
            Cbo_Payment_count.Visible = True
            Txt_first_payment.Visible = True
            Txt_next_payment.Visible = True
            Cbo_First_payment_month.Visible = True
        Case Else
            Cbo_Payment_count.Visible = False
            Txt_first_payment.Visible = False
            Txt_next_payment.Visible = False
            Cbo_First_payment_month.Visible = False
    End Select
End Sub

 

[pvman]

You are a mind reader
Actually, I currently have 11 options and each requires a different set of controls.
In order to make it more flexible, I am now looking for an option to activate the "master" combo work with a code&description, so while the user selects an option based on a description he sees in the combo, what will actually triger the event will be the "code" of that option (1=Check, 2=Installments, 3=Cash, Etc.).
This way, the user can add more options without calling me to hard-code those options.
It should also allow the user to slightly modify the description of the existing options (without the need to change the macro).

Regardless, I will now start with the "Select-Case" and see how the project progresses. If you have any idea (that will not take too much of of your time, it will be great).

thanks again

 

[Norie]

Why not just create multiple userforms?

You could have one that allows the user to select the type of payment/whatever from a combobox.

It would also have a command button that when pressed would open the relevant userform.

 

[ChrisM]

Instead of using Select Case on the actual combobox values, you could:

1. Do it based off of combobox.ListIndex value. This of course requires that your combobox is consistently loaded the same way.

2. Make a multicolumn combobox, with the second column being hidden, and it contains an lookup number you'd pass to your Select Case. So when you initially load the combobox, give each loaded item a key number.

 

[pvman]

Why not just create multiple userforms?
You could have one that allows the user to select the type of payment/whatever from a combobox.
It would also have a command button that when pressed would open the relevant userform.

Thanks. That is a great idea, which I actually started to investigate because I have limited realestate on the form, and I want to have the relevant fields pop on and off on the same realestate.
What I just need to figure out is how to initialize those "sub-forms" on-the-fly, and how to migrate the data from multiple forms to the database.

 

[pvman]

Instead of using Select Case on the actual combobox values, you could:
1. Do it based off of combobox.ListIndex value. This of course requires that your combobox is consistently loaded the same way.
2. Make a multicolumn combobox, with the second column being hidden, and it contains an lookup number you'd pass to your Select Case. So when you initially load the combobox, give each loaded item a key number.

Thanks. This is a good starting point for me to figure out how to make it work.

 

[pvman]

You know, Guys, I just had a new idea.
What if I use a MultiPage control?
For each option the user selects in the master combo, a different sheet will come into focus, with the relevant fields to fill/select.

What do you think? Any leads/links how to start?

 

[ChrisM]

Yes you can do that. Set the page's style property to 2 = do not display tabs. Use the value property to flip pages. Again, tie the value property to a 2nd hidden column in your combobox.

So on combobox change, set multipage.value = to an index
nber

 

[Norie]

pvman

A multipage was the 2nd thing I was going to suggest.

I basically suggested the userform idea so you would avoid all the hiding/unhiding of controls.

That could turn into a nightmare.

If you think about it even with just your 11 options that means at least 33 controls to work with.

Then you also need to think about the design aspect - where are you going to locate all these controls?

Are you going to have multiple controls on top of each other etc...