userform combo box buggy

orsm6

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

orsm6

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows
maybe just easier to make a list box rather than a combo box.,,,
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows
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.


Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It should be in "Sub UserForm_Initialize":
VBA Code:
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

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows
It should be in "Sub UserForm_Initialize":
VBA Code:
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

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It should be in "Sub UserForm_Initialize":
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
428
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,630
Messages
5,838,463
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top