userform combo box buggy

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
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
2,962
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
379
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
2,962
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
379
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
379
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
2,962
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
379
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
2,962
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,213
Members
416,230
Latest member
jdaitchman

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