Creating & deleting multiple comboboxes

[Mightyginger]

Hi, first post guys so thanks in advance for any help you mgiht be able to give. As with all things, easy if you know the answer.

Need to create and delete comboboxes within a loop and have control over what the combobox is refered to e.g. Sheet1.ComboBox?

Current code

'Do Until counter = Sheet1.ComboBox3.Value
' Sheet1.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
' DisplayAsIcon:=False, Left:=1200, Top:=fromtop, Width:=58, Height:=15). _
' Select
' fromtop = fromtop + 30
' counter = counter + 1
'Loop

Where I've just hacked some macro recorder code. This creates multiple comboxes but I don't have any real control over what they're called (numbered).

Code to delete
ActiveSheet.Shapes("ComboBox50&quot.Select
(this would be in a loop)

Main problem I have is that instead of referring to the object as "ComboBox50" I want to be able to it as ComboBox + some number which I can increment.

Any help would be appreciated (hope the post isn't too long).


Neil.

 

[Loomah]

Hi
Not 100% sure what you're trying to do here but to name your combos you could try adding this after your Add line

Selection.Name = "MyLittleCombo" & counter

This will increment the names as the counter value goes up. Likewise delete them using the same increments.

And no, your post isn't too long!! In fact you may need to give a little more info if I haven't helped you here!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Mightyginger]

Cheers mate. Here's what I'm doing. I making a bit on my spreadsheet to do multiple graphing. The user has an initial pull-down box that goes between 0-10. They select the number of instruments they want to graph (so they all get graphed on the same graph). So if they pick 5 say my code then generates 5 pull-down boxes below and fills them with all the available instruments. They select what they want to graph and hit a button.

Struggling with being able to generate a set number of comboboxes and delete them (since if they go from 5 to 2 I either need to delete all of them and generate new ones or delete the extra ones - think I'd prefer to delete all of them).

What I really need to do, to get to the point is be able to manipulate this statement
Sheet1.ComboBox? where ? is some counter that I can change so I can directly reference the combo box and use .delete etc., to add and remove. So, just generally how can I manipulate the .ComboBox bit to pass in a variable instead of having to put .ComboBox2 etc. so I can do Number=2 .ComboBoxNumber - long story for a short question.


Neil.

 

[Loomah]

OK
I'm not in a good position to test this but this is how I would try to complete this!

1. Name your initial combo properly - it'll help in the long run - something like 'cboMain'

2. Use either the code you have already posted or a For..Next loop to add and name the combos. The code I gave will allow you to do this eg

 for i = 1 to cboMain.value
'add combo
Selection.Name = "cboNextSelection" & i
next

3. Where are the combos going? On a for or a worksheet. Either way you'll have to consider where to position them. An example of positioning on a sheet is available in this thread thread707-467400. This and positioning on forms requires changing the from top properties by adding the height of the previous box.
4. Go with the delete all idea. Delete all except cboMain by looping therough the combo collection in the sheet/form. Can't remember exact code by for For Each...In...Next construct!

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[Mightyginger]

Cheers mate. Figured out the top and left properties already but thanks for point them out. It's to do on the worksheet.

Struggling with the rest though - not used to using naming properties. How do I go about referring to Selection.Name = "cboNextSelection" & i in my loop?


Neil.

 

[Loomah]

Hi
I'm pushing my luck a bit here so here's some code. Put it into the module for the worksheet containing the combo with values 1-10 in it. Call that combo cboMain and try selecting different numbers. After, go to design view ad select one of the new combos, you'l see that they are named as the code implies, cboSelection then the number 1 to whatever number you selected. You can then reference these combos by that name.

Private Sub cboMain_Change()
Dim iCnt As Integer
Dim cb 'As ComboBox

For Each cb In Sheet1.OLEObjects
    If Not cb.Name = "cboMain" Then cb.Delete
Next
For iCnt = 1 To cboMain.Value
    Sheet1.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=cboMain.Left, Top:=cboMain.Top + (cboMain.Height + 5) * iCnt, _
        Width:=cboMain.Width, Height:=15).Select
    Selection.Name = "cboSelection" & iCnt
Next
[a1].Select
End Sub

I hope this helps! I don't think I'll get another chance to help in detail today.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Mightyginger]

You're a star and have really done lots - so I appreciate it. I'll work with what you've put and see how it goes.

I'll post what I do later in the day so you can have a look.


Thanks,


Neil.

 

[Mightyginger]

Not to be too cheeky but having problems with this line
For Each cb In Sheet1.OLEObjects
Just getting an error that it's a type mismatch. This is the key epxression that I need as I need to be to cycle through the cb's.


Neil.

 

[Mightyginger]

Got it working. Thanks mate.

 

[Mightyginger]

Next question is how do I assign values to the combobox? What I did previously is I have some code that generates an array called pulldown and assigns that as the values for the box using a command like
Sheet1.ComboBox1.List = WorksheetFunction.Transpose (Pulldown)

I had thought replacing the above line (which works) with this but it doesn't work. How can I manipulate cb?

For Each cb In Sheet1.OLEObjects
If Not cb.Name = "cboMain" Then
cb.Value = WorksheetFunction.Transpose(Pulldown)
End If
Next

 

[RobBroekhuis]

Without testing (OLEObjects do get tricky sometimes), I think the following may work:

cb.object.list=WorksheetFunction.Transpose(Pulldown)
Rob

 

[Mightyginger]

Works a treat. Thank you as well. Should be fine from here on I think - got enough to put it all together. Thanks for your help everybody.


Neil.