Excel / VBA Combo Box

[Disturbedguy]

Hi all,

Me again, thanks for the help last time, much appreciated, however, new problem time.

I have a ComboBox on a form that will list a number of options, I was hoping / would like it so that when one of the options is selected it opens the relevant form either automatically or on the press of a button.

Have been searching this morning and my google-fu has been weak as such I haven't been able to find anything yet.

Bit of background / information - I was originally going to have buttons to open each form, but soon realised that having the number of buttons I would require for the sites we have (about 115) would look messy so it was decided a dropdown could be an idea.

We did look at the idea of having a search function on the form, users then enter either the depot number (3 digit) or depot name, click the search button and it opens the relevant form, but I am completely clueless in this regards.

Again, appreciate any help I receive.

[Disturbedguy]

Nevermind guys, after re-alignign my chi, I found what i was looking for -

Private Sub DepotSelect_Change()
Select Case DepotSelect.Value
Case "Ford"
frmFord.Show
Case "Leatherhead"
frmLeatherhead.Show

End Select

End Sub

And that works, I select an option and wallah the form opens

[Disturbedguy]

*sigh* only slightly resolved.

It appears, unless I am being stupid that there is a limit to the number of items I can have on a line on VBA with a combo box before it drops to a new line and no longer recognises the data I am inputting as part of the same combo box, therefore meaning I can't get the combo box to list all the items I have.

Does anyone know if there is a limit and if so, is there a way around this?

[scaryjim]

how do you mean number of items on a line? A combo box should theoretically be able to hold as many items as you throw at it. How are you setting up the combo box?

[Disturbedguy]

Hmm sorry I didn't really explain myself properly, when adding items intot he combobox within the VBA, it appears I am limited to how much I can put on a single line, before it jumps to the next, therefore breaking what I have entered so at the moment I can't add all the items to the combo box correctly.

DepotSelect.List = Array("780:Aberdeen", "AvonmouthPA", "984:Balfour Beatty", "158:Barking") - There is more than this, this is just a selection

[Disturbedguy]

Fixed. Issue was length of function exceeded max, used the line continuation thing ( _) - space followed by underscore to break up the lines, working fine.

[TheAnimus]

try something like

DepotSelect.List = Array("780:Aberdeen", "AvonmouthPA",  _
"984:Balfour Beatty", "158:Barking")

What you are fighting is the word wrapping behaviour. 'Grown up' languages often use a ; to terminate a line, this makes it very easy to know when one ends. Historical reasons make newline (often \n or \r\n depending) tricky, so VBA squeels like an italian PM in a whorehouse should you try. the _ char means I'll continue on a new line.

[Disturbedguy]

try something like

DepotSelect.List = Array("780:Aberdeen", "AvonmouthPA",  _
"984:Balfour Beatty", "158:Barking")

What you are fighting is the word wrapping behaviour. 'Grown up' languages often use a ; to terminate a line, this makes it very easy to know when one ends. Historical reasons make newline (often \n or \r\n depending) tricky, so VBA squeels like an italian PM in a whorehouse should you try. the _ char means I'll continue on a new line.

Thanks for the explanation.

I remember seeing and using the ; when messing with js / node.js a little recently-ish...really is something I need to sit down and put my backside into learning.

[TheAnimus]

I'll drop you the invoice later today

[Disturbedguy]

I'll be sure to pass it onto my boss