Excel Combo Box query

peterattheboro · 10 Sep 2008 at 10:41

Is it possible to create a macro which will create a combo box in cell A1, insert the entries for the menu and then go on to do the same with A2, A3, A4 right upto A450?

I can create this macro but I just can't get it to continue onto the next row into the position that I want it. Also the box needs to enter a number into a cell on the same row.

So A1 will enter a number into $P$1 depending on who is selected from the menu.
A2 will enter $P$2
A3 will enter $P$3

Thanks.

Apologies if it's a little confusing

SimonCHere · 10 Sep 2008 at 14:58

The following should add comboboxes to each cell in the range A1:A4 they are populated from the range J1:J5

As to getting the combos to do something...
I can't seem to find anything about AddHandler or EventHandlers so I don't know how to do that bit in VBA.

Code:

Private Sub doit()
Dim i As Integer
i = 0

For Each c In Range("A1", "A4")
    i = i + 1
    c.Select
    
    Worksheets(1).OLEObjects.Add "Forms.ComboBox.1", Width:=63, Height:=16.5
    
    For Each s In Worksheets(1).OLEObjects
        With s
            If .Name = "ComboBox" & i Then
                .ListFillRange = "J1:J5"
                .Top = ActiveCell.Top
                .Placement = xlMoveAndSize
            End If
        End With
    Next
Next
                
End Sub

Bill Door · 10 Sep 2008 at 16:23

You could use the linked cell property to insert the value in the P column.

If you insert the following line immediately before the 'End If' line in SimonCHere's code it should link the combobox value to the respective cell in column P.

.LinkedCell = "P" & i

peterattheboro · 10 Sep 2008 at 16:33

Thanks guys. I'll have a go with this in the morning as my head is farting right now and it's nearly home time!

SimonCHere · 10 Sep 2008 at 16:54

It's not the best code in the world, but it should work with Bill Door's (isn't that a skeletons name?

) extra bit.

Simon

peterattheboro · 11 Sep 2008 at 09:01

I've tried this code but it's an OLE object and I can't work with it. Can't do anything with the box once it's been placed.

This is the code I am trying to work with:

Code:

'

'
    ActiveSheet.DropDowns.Add(0, 114.75, 67.5, 15.75).Select
End Sub

That's just a standard combo box being placed on the sheet.

Bill Door · 11 Sep 2008 at 10:27

A bit of hack and slash to the original code fits around your code OK...

Code:

Private Sub doit()
Dim i As Integer
i = 0

For Each c In Range("A1", "A4")
    i = i + 1
    
    ActiveSheet.DropDowns.Add(0, 114.75, 67.5, 15.75).Select
        With Selection
                .ListFillRange = "J1:J5"
                .Top = c.Top
                .LinkedCell = "P" & i
        End With
Next
                
End Sub

The only side note is that you'll have to set the row height of the cells in the sheet to 15.75 or more to avoid the boxes overlapping.

Oh, and yes I took my name from Terry Practchetts Death character.