Question re: VBA ComboBox with many items

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, can anyone suggest the best way to code a ComboBox in VBA that would potentially have a few hundred items in it?? It will be for users to select a year, starting say with 1930 and going as high as 2230.

I'm just not sure whether 'array' or 'additem' would be better or maybe I should be looking at some other alternative, like a text box with a spinner on it???

Hoping for ideas! Thanks!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
okay, I can do that, I changed the order (not) thinking that it would make a difference and I also have cell E9 formatted for long-date (month/day/year) so I thought it would work from a logical perspective. Sorry about that.

Should I change that setting back to whatever it was before (can't remember)...???

I will try your suggestions and report back! Thanks for your patience.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Have you tried DateSerial?
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
I changed the line of code to "Dob = DateSerial(Me.YearComboBox, Me.MonthComboBox, Me.DayComboBox)"

Still getting the run-time 13 type-mismatch error when I try and use the form...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,425
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is it that line that is causing is getting highlighted?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Fluff

I'm sorry if I've missed something - since the changes to the site all I can see when I first click on the thread are posts #1. #21, #22, #23 and #24
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,425
Office Version
  1. 365
Platform
  1. Windows
@Fluff yes, it is highlighted in yellow...
Do all three combos have a value & do they equate to a real date, ie you haven't select month 2 & day 31
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Fluff sorry for being such a NOOB... I get the error when I try and launch the userform so no, the combos are empty (other than the 'items' that will show in them when the user clicks the down-arrow. I can't even get to them to select a date. Is this what you mean??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,425
Office Version
  1. 365
Platform
  1. Windows
How and when is that code being run?
 

Forum statistics

Threads
1,137,353
Messages
5,680,994
Members
419,948
Latest member
Sbakker1

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