Opening userforms from other userforms 
Author Message
 Opening userforms from other userforms

Hi,

I use a number of userforms to control input into a worksheet. Userform1
(called frmStart) needs to open a next form depending on the type of input
chosen.

The code I use from a textbook doesn't work:
frmStart contains a frame with optionbuttons to determine what type of input
the users wants.

Dim myOption As Control
Dim myInput

For Each myOption In grpInput.Controls
    If myOption.Value = True Then
        myInput = myOption.Caption
    End If
Next myOption

The problem with this piece of code is that I don't get Value in the 2nd
line of the If-structure in the pop-up box. It only lists visible. It's
listing the possibilities for the frame grpInput instead of the properties
for the optionbuttons.

I also need a way to run the chosen userform when the if-structure has
determined which optionbutton is active.

Any ideas???

Thanks

Jeroen Bouwman



Wed, 18 Jun 1902 08:00:00 GMT  
 Opening userforms from other userforms
Jeroen, I copied the code below and created a frame (named grpInput), and
put 3 option boxes in it. It worked fine.

I see what you mean about the property "value" not being listed in the
intellisense, but that's because at this point in time, the code does not
know what object it will be looking at. I guess that all the properties
listed in the intellisense are common to all form objects - then VBA
wouldn't crash by trying to reference a property which doesn't exist for
that object. (e.g. a "label" object doesn't have a "value" property, but in
theory this object could be contained in your frame).

As to how you show which user form to open depending on which option button
is TRUE, there must be a way using the userforms collection object, but I'm
not sure what it would be.

Hope this sort of helps,

Rog

Quote:

> Hi,

> I use a number of userforms to control input into a worksheet. Userform1
> (called frmStart) needs to open a next form depending on the type of input
> chosen.

> The code I use from a textbook doesn't work:
> frmStart contains a frame with optionbuttons to determine what type of
input
> the users wants.

> Dim myOption As Control
> Dim myInput

> For Each myOption In grpInput.Controls
>     If myOption.Value = True Then
>         myInput = myOption.Caption
>     End If
> Next myOption

> The problem with this piece of code is that I don't get Value in the 2nd
> line of the If-structure in the pop-up box. It only lists visible. It's
> listing the possibilities for the frame grpInput instead of the properties
> for the optionbuttons.

> I also need a way to run the chosen userform when the if-structure has
> determined which optionbutton is active.

> Any ideas???

> Thanks

> Jeroen Bouwman



Wed, 18 Jun 1902 08:00:00 GMT  
 Opening userforms from other userforms
You could put the name of the corresponding UserForm you want to call in the
Tag property of each optionbutton.

Then loop thru each UserForm to see which name matches the tag
(unfortunately, you have to load them before you can loop thru them):

Private Sub CommandButton1_Click()
    Dim myOption As Control
    Dim myTag As String
    Dim i As Integer

    For Each myOption In grpInput.Controls
        If myOption.Value = True Then
            myTag = myOption.Tag
            Exit For
        End If
    Next myOption

    Load UserForm2
    Load UserForm3

    For i = 0 To UserForms.Count - 1
        If UserForms(i).Name = myTag Then
            Me.Hide
            UserForms(i).Show
            Me.Show
            Exit For
        End If
    Next

End Sub

Depending on how many UserForms you have, it may be better to use Select
Case:

Private Sub CommandButton3_Click()
    Dim myOption As Control
    Dim myTag As String

    For Each myOption In grpInput.Controls
        If myOption.Value = True Then
            myTag = myOption.Tag
            Exit For
        End If
    Next myOption

    Me.Hide

    Select Case myTag
        Case "UserForm2"
            UserForm2.Show
        Case "UserForm3"
            UserForm3.Show
    End Select

    Me.Show
End Sub

HTH


Quote:
> Hi,

> I use a number of userforms to control input into a worksheet. Userform1
> (called frmStart) needs to open a next form depending on the type of input
> chosen.

> The code I use from a textbook doesn't work:
> frmStart contains a frame with optionbuttons to determine what type of
input
> the users wants.

> Dim myOption As Control
> Dim myInput

> For Each myOption In grpInput.Controls
>     If myOption.Value = True Then
>         myInput = myOption.Caption
>     End If
> Next myOption

> The problem with this piece of code is that I don't get Value in the 2nd
> line of the If-structure in the pop-up box. It only lists visible. It's
> listing the possibilities for the frame grpInput instead of the properties
> for the optionbuttons.

> I also need a way to run the chosen userform when the if-structure has
> determined which optionbutton is active.

> Any ideas???

> Thanks

> Jeroen Bouwman



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Sending UserForms to others

2. Can't open workbook with userforms running

3. Code to Close Open Userforms in the VB Editor

4. opening userforms

5. UserForms & User responses

6. passing values between userforms

7. E-mailing Excel files that contain UserForms

8. cancelling userforms

9. irratic behaviour with userforms

10. 2 UserForms, one always on top


 
Powered by phpBB® Forum Software © phpBB Group