userform combo box buggy

[orsm6]

Hi all - have recently built a userform containing combo boxes that i have turned into a drop down list.

the way it works is that on my sheet named 'lists' i have a named range. Then in the properties of the combo box i have typed the name of the named range in field RowSource.

the problem is that sometimes it will show me all of the contents of the named range and sometimes it wont.
I have extended the ListRow field to 30 and it still won't consistently work.

is there something I am missing or a better way to get my drop down list on the userform?

TIA

 

[orsm6]

maybe just easier to make a list box rather than a combo box.,,,

 

[Akuini]

Well, do not use Rowsource:
This is from VBA for smarties How to fill a Combobox / Listbox
It says:
"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Without Rowsource, you can insert the range directly into the listbox like this:
First, clear Rowsource property of the combobox, then:
Combobox1.List = Sheets("Sheet1").Range("A1:A10").Value
or using a named range:
Combobox1.List =Range("myRange").Value

 

[orsm6]

Well, do not use Rowsource:
This is from VBA for smarties How to fill a Combobox / Listbox
It says:
"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Without Rowsource, you can insert the range directly into the listbox like this:
First, clear Rowsource property of the combobox, then:
Combobox1.List = Sheets("Sheet1").Range("A1:A10").Value
or using a named range:
Combobox1.List =Range("myRange").Value

Thanks Akuini..... still struggling a little.

how should the code sit within the combo box? the value is also copied through to a sheet once a list item is selected. I tried this but getting it wrong still.

Sub ComboBox2_Change()

ComboBox1.List = Range("Action1").value

Dim ws1 As Worksheet
Set ws1 = Worksheets("PCR Form")

With ws1
    .Cells(107, 3).value = Me.ComboBox2.value
End With

End Sub

 

[Akuini]

It should be in "Sub UserForm_Initialize":

Private Sub UserForm_Initialize()
ComboBox1.List = Range("Action1").Value

End Sub

but do you have second combobox?

.Cells(107, 3).value = Me.ComboBox2.value

 

[orsm6]

It should be in "Sub UserForm_Initialize":

Private Sub UserForm_Initialize()
ComboBox1.List = Range("Action1").Value

End Sub

but do you have second combobox?

No - this same box ... originally combobox2 was to have the drop down list... then when selected an item from the list, the value of the same box goes to that cell on sheet

 

[orsm6]

It should be in "Sub UserForm_Initialize":

Private Sub UserForm_Initialize()
ComboBox1.List = Range("Action1").Value

End Sub

but do you have second combobox?

OK.... i have finally gotten this to work so far. let me play a bit more.

thanks heaps for your help

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[orsm6]

You're welcome, glad to help & thanks for the feedback.

OK so I'm still not quite there...... I didn't think i needed to mention it but theres some constraints so to speak.

on the user form, it's about assigning actions.... so
combo1 list looks at department name
combo2 list will list the names of the people who belong to that department.

if i choose a department in combo1 then a name.... but decide i want to change department then the name list will not change unless i exit the form and go back into it. hope that makes sense

 

[Akuini]

Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.