Excel VBA: Using Multiple Pop-up UserForms Within a Primary UserForm

Question

Upfront, let me say I’m not a programmer and I have limited VBA knowledge.  But, I enjoy finding code that others have written and adapting it to work for my situation. 

First, let me describe my current code (well technically it’s borrowed code) that I’m using.  I have a UserForm1 that has:

          Frame1:  2 radial (option) button ob1 and ob2.

          Frame2:  ob3, ob4 and ob5.

          Frame3:  ob6, ob7 and ob8.

          Two textboxes: tb1 and tb2.

   If ob1 or ob2 is selected, no other mandatory selection required.

   If ob3 or ob4 is selected, then a selection must be made from Frame 3,

     either ob6, ob7 or ob8.

   If ob5 is selected, an entry is required in tb1.

   tb2 is used for remarks and its use is optional.

Currently, if the user selects ob3 for example, but does not make a selection from Frame3, when the hit the submit button, an If and And statements with message box pops-up reminding (forcing) the user to make a selection from Frame3 (ob6, ob7 and ob8), then it returns them to UserFrom1 to make a section.  The same would apply if the user ob5, but failed to make an entry in tb1.

I’m not sure if this is possible, but here’s what I’m trying to like:

If the user selected ob3 or ob4, another UserForm2 would

prompting the user to make a selection from Frame3 (ob6, ob7, ob8), then after dismissing that message box, the selection made in UserFrom2 would populate Frame3 in UserForm1.  The same would apply for the selection for ob5 and tb1.

If I’ve totally confused you, here’s a picture on with a mini description on how I would like things to work.

Is there a way that this can be done?  If so, I would greatly appreciate any assistance with the coding.

Answer 1

Why not dispense with UserForm 2 and have ob6, ob7, ob8 on the base form disabled unless the value of ob3 is true in which case they would be enabled and the selection can be made on the base form.  Similarly with ob5, have tb1 disabled unless the value of ob5 is true.  Then have appropriate code in the Submit button on the main form to validate that the necessary actions have been taken.

It's lot easier to give appropriate advice when the whole story is known.

Answer 2

Doug,

Thank for replying to my question.  I think your suggestion will work; however, first let me reiterate I have very limited experience writing code.  I attempted to write some code based on my understanding of your comments.  It does not work.  I don’t get an error on the code, it’s just that ob6, ob7 and ob8 are always enabled.  So if I select ob5, ob6-ob8 should be disabled but I can still select them.  I know it’s likely something in my code:

Option Explicit

Private Sub cmdSubmit_Click()

    If ob3.Value = True or ob4.Value = Ture Then

        ob6.Enabled = True And ob7.Enabled = True And ob8.Enabled = True

    Else

        ob6.Enabled =False And ob7.Enabled = False And ob8.Enabled = False

    End If

    End Sub

Some other code here………

End Sub

I would appreciate your comments on the code and insight why it’s not producing the desired results.  I'm sure I'm out in left-field with the code.  Again, thank you.

Answer 3

You cannot use a construction like

    ob6.Enabled = True And ob7.Enabled = True And ob8.Enabled = True

it would have to be

    ob6.Enabled = True

    ob7.Enabled = True

    ob8.Enabled = True

However, you can control the availability of the controls inside a frame by enabling of disabling the frame.

Private Sub ob3_Click()

If ob3.Value = True then

    Frame3.Enabled = True

    tb1.Enabled = False

Else

    Frame3.Enabled = False

End If

End Sub

Private Sub ob4_Click()

If ob4.Value = True then

    Frame3.Enabled = True

    tb1.Enabled = False

Else

    Frame3.Enabled = False

End If

End Sub

Private Sub ob5_Click()

If ob5.Value = True then

    Frame3.Enabled = False

    tb1.Enabled = True

Else

    Frame3.Enabled = True

End If

End Sub

Once again, it's lot easier to give appropriate advice when the whole story is known.