Initialize UserForm after selecting from List

[tdm1103]

I have an Excel Workbook, in which I have a dropdown list. I would like a userform to initialize if certain choices are selected from the list. Is this possible? And if so any help with code would be great.

Thank you.

 

[PCWL]

Private Sub UserForm_Initialize
Dim CELLREF as String
Dim cType as Range
Dim ws As Worksheet

CELLREF = Sheets("Sheet1").Range("A1")
Select Case CELLREF

Case CASE1
For Each cType in ws.Range("RANGE1")
With Me.COMBOBOX1
.AddItem cType.Value
End With
Next cType

Case CASE2
For Each cType in ws.Range("RANGE2")
With Me.COMBOBOX1
.AddItem cType.Value
End With
Next cType

End Select
End Sub

Replace anything that is in all caps with the appropriate ranges

 

[jakuza]

I have an Excel Workbook, in which I have a dropdown list. I would like a userform to initialize if certain choices are selected from the list. Is this possible? And if so any help with code would be great.

Thank you.

Mouse right click on dropdown list (I use ComboBox in example below) and select View code. Now you are in VBA in a Sub where you define what is happening when you change value. Here you need to put a part If... ... End If. Also you need to put a certain value of choice you want, I put word Two. YourUserFormName is the name of the form you want to initialize.

Private Sub ComboBox1_Change()

If ComboBox1.Value = "Two" Then
YourUserFormName.Show
End If

End Sub

 

[tdm1103]

Mouse right click on dropdown list (I use ComboBox in example below) and select View code. Now you are in VBA in a Sub where you define what is happening when you change value. Here you need to put a part If... ... End If. Also you need to put a certain value of choice you want, I put word Two. YourUserFormName is the name of the form you want to initialize.

Private Sub ComboBox1_Change()

If ComboBox1.Value = "Two" Then
YourUserFormName.Show
End If

End Sub

Thank you for the help, this seems very easy, but I don't see "View Code" when I right click on the list. I created the list using Data Validation, where I allowed a "list" and sourced the allowable options from a defined range. Any idea why I am not seeing "View Code" when I right click on cell?

 

[tdm1103]

PCWL, Thank you for the code. I don't see where it opens up my UserForm. I replaced COMBOBOX1 with UserForm1, but when I select "Blue" from my list (Blue is what used for "RANGE1") nothing happens.

 

[jakuza]

You don't see View code because it is not an object. You need to create ComboBox object from Control Toolbox Pane. If this Pane is not shown make it visible: View > Toolbars > Control Toolbox. Find and create ComboBox object somewhere in your sheet and while you are still in Design mode (you have a button in Control Toolbox for that) mouse right click and select View code. You can put here the above code a represent to you.
Also what you need is to put some Values in your ComboBox. It is little tricky but one way to to that is:
When you are in VBA (you clicked View code, or pres Alt+F11) explore ThisWorkbook in Microsoft Excel Object, then on the right window (where you write a code) select Workbook from first ComboBox and then SheetActivate from second one. Here you put this code:

Private Sub Workbook_SheetActivate(ByVal sh As Object)

If ActiveSheet.CodeName = "Sheet1" Then 'CodeName is the name of Sheet that is not in bracket
 Sheet1.ComboBox1.ListFillRange = "C6:C11" 'Here I add value from column C, rows 6 to 11 to the ComboBox1
 Sheet1.ComboBox1.LinkedCell = "J6" 'J6 is the cell where I put values from ComboBox selection
End If

End Sub

Above code will add List of certain values to ComboBox1 every time you activate it (clicked it).
That is all.
Do not forget to use Data Validation on cell "J6", on my example, (cell where you put values from ComboBox) to disallow input in this cell, so it can be only changed via ComboBox-that is what you want. You can to this using Data Validation and choosing that cell value must be equal to for example number "-123123". I suppose that it is minimal chance that someone enter that number, or what ever you want.

Tell us if this is working for you.
Regards,
-JAKUZA-

 

[PCWL]

I think what jakuza is suggesting is more what you want. I'm pretty sure I misread what you wanted (I was thinking you wanted to initialize comboboxes in a userform based on certain inputs from another source). Also, I was thinking you were using a combobox from another userform for the initial dropdown list, but thinking back, I'm guessing you were using the data validation method to populate a list on a cell.

As an aside for your interest though, I can at least explain what I was doing:
I thought you were changing the value in cell A1 via another userform, so CELLREF would become the value of whatever is in cell A1 (e.g. "Blue")
COMBOBOX1 is the name of the combobox in your userform (e.g. cmbCarColour)
You can then select different cases (e.g. CASE1 = "Blue", CASE2 = "Red") based on what value CELLREF has taken on.
So for CASE1, you would be populating the combobox with items from a named range (e.g. RANGE1 = BlueCars). The advantage of named ranges is that they can be from any worksheet, so you can create the list on a sheet protected from the user.

Hope that helps, but I do think jakuza covers what you wanted.

 

[tdm1103]

I feel like I am closer to figuring this out. In Excel 2010 under the developer tab - insert, I chose combo box. I drew box size and then pressed "View Code" on the developer tab. It gave the following below, in which I inserted the code you provided earlier, changing it to match my test. In this code I get Compile Error 424 - Object Not Found.

Sub DropDown5_Change()
If ComboBox1.Value = "Blue" Then
UserForm1.Show
End If

End Sub

Then in VBA, under Workbook I used the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.CodeName = "Sheet1" Then  
Sheet1.ComboBox1.ListFillRange = "P1:P9" 
Sheet1.ComboBox1.LinkedCell = "B2"  
End If

End Sub

I have a list of 9 colors in Sheet1 in cells P1:P9. When I click on the ComboBox, it is blank. There is nothing to choose from.

 

[jakuza]

Look in code carefully when you choose View code...

It shows: Sub DropDown5_Change(). This mean that object you added is NOT comboBox (or maybe it is in excel 2010, you can see it certainly to be sure). The object name is DropDown5.
Change ComboBox1 everywhere with DropDown5 and it should be working.
But I am not sure why the name is DropDown. Why this is important? Because I am not sure if the code a present to you will work. You can check it when you are writing a code e.g. when you write DropDown5 and enter . (dot) it should list you all possible functions of it, there should be ListFillRange and LinkedCell if they are not listed probably it won't work.
Also check that the CodeName of your sheet is Sheet1. CodeName is one that is not in a bracket, and it is in VBA just above Workbook.
Try it and tell as.

Regards,
-JAKUZA-

 

[jakuza]

I looked now in excel 2010 and I can not figure it out how you created DropDown object.
To create ComboBox you choose Developer's Tab > Insert, then in ActiveX Controls choose ComboBox (second one from left in a first row). When you mouse right click on it you will see left from formula bar that it shows the name e.g. ComboBox1 or ComboBox2...
Maybe above will work even with DropDown but it is better to be ComboBox from ActiveX Controls.

Regards,
-JAKUZA-