Pop up userform when value selected from dropdown menu

[Excel_wonder]

I'm a VBA beginner and need some help.

I have a dropdown menu for A, B, C, D. If option A is being selected, userform will pop up. Otherwise if B, C or D is being selected, nothing will show.

Thanks a lot for all your help.

 

[Logit]

.
Paste macro in Sheet Level Module :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ltr As String
Dim UForm As Object
Dim i As Integer
i = 0


ltr = Sheet1.Range("F1").Value


For Each UForm In VBA.UserForms
        UForm.Hide
        Unload VBA.UserForms(i)
        i = i + 1
Next


Select Case ltr
    Case "A"
        UserForm1.Show
    Case "B"
        UserForm2.Show
    Case "C"
        UserForm3.Show
    Case "D"
        UserForm4.Show
End Select


End Sub

Create a drop down in F1 with four selections : A, B, C, D

Or edit the macro to match the cell location of the drop down and the UserForm Names

 

[NehVy]

.
Paste macro in Sheet Level Module :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ltr As String
Dim UForm As Object
Dim i As Integer
i = 0


ltr = Sheet1.Range("F1").Value


For Each UForm In VBA.UserForms
        UForm.Hide
        Unload VBA.UserForms(i)
        i = i + 1
Next


Select Case ltr
    Case "A"
        UserForm1.Show
    Case "B"
        UserForm2.Show
    Case "C"
        UserForm3.Show
    Case "D"
        UserForm4.Show
End Select


End Sub

Create a drop down in F1 with four selections : A, B, C, D

Or edit the macro to match the cell location of the drop down and the UserForm Names

I try to run the code. But only userform1 is appearing . Rest of the userform are not. In every case B,C and D it is following Userform1. I am pretty new to the VBA macro. Can you guide me here.

 

[Logit]

.
See download : Userform Select DropDown.xlsm

 

[NehVy]

.
See download : Userform Select DropDown.xlsm

Thank you. It worked.

 

[Logit]

You are welcome.

 

[NehVy]

Thank you. It worked.

If the above code is extended for the range of cells (instead of only F1) . Let say F1 to F10. Will it be enough changing the range ("F1:F10"). As I try to change the range but it is giving type mismatch error.

 

[Logit]

The list may be expanded by editing the DATA VALIDATION LIST specs.

You can find that on the DATA tab, DATA TOOLS / DATA VALIDATION ... click on Data Validation again.

A small form appears and toward the bottom you will see : =M1:M4 ... in the Source Field.

The SOURCE FIELD is very finicky ... do not enter spaces or try to delete anything. You'll end up with gibberish.
Simply highlight the number FOUR. Then type 10. Click OK.

Now in the Column M, you can extend the letters down the Column to J.

Add the new letter to the Select Case statement and be certain to add the corresponding new forms.

 

[NehVy]

The list may be expanded by editing the DATA VALIDATION LIST specs.

You can find that on the DATA tab, DATA TOOLS / DATA VALIDATION ... click on Data Validation again.

A small form appears and toward the bottom you will see : =M1:M4 ... in the Source Field.

The SOURCE FIELD is very finicky ... do not enter spaces or try to delete anything. You'll end up with gibberish.
Simply highlight the number FOUR. Then type 10. Click OK.

Now in the Column M, you can extend the letters down the Column to J.

Add the new letter to the Select Case statement and be certain to add the corresponding new forms.

The Code below: giving type mismatch error when I am trying to give range C2:C5..

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ltr As String


Dim UForm As Object
Dim i As Integer
i = 0



ltr = Sheet1.Range("C2:C5").Value


For Each UForm In VBA.UserForms
UForm.Hide
Unload VBA.UserForms(i)
i = i + 1
Next


Select Case ltr

Case "Closed"
UserForm1.Show
Case "Ongoing"
UserForm2.Show
Case "Onhold"
UserForm3.Show
Case "Early discussions"
UserForm4.Show

End Select

End Sub

Sub test()

Run "Sheet1.Worksheet_Change", Range("C2:C5")

End Sub

 

[Logit]

I suspect the macro may not be in the SHEET module for SHEET1 ?

The easiest method to get to that module area is to right click on the SHEET1 tab and select VIEW CODE. Then the large window on the right is
where you would paste the macro. Also, there is no need for the 2nd macro "Sub test()" .

If you can post your workbook to a download site, then post the url here it would make it much easier to trouble shoot with accuracy.