make a combo box repeat for entire column?
 

Hi there,

I'm wondering if there is a way on my spreadsheet I can make the same combo
box (ComboBox1) appear on each row of an entire column through VBA. I
currently have ComboBox1 in row 1 with the list items from VBA code.

thanks, JJ

make a combo box repeat for entire column?
 

All 64k rows (or 1MB rows in xl2007???).

Have you thought about using Data|Validation instead?


jjordan wrote:

Hi there,

I'm wondering if there is a way on my spreadsheet I can make the same combo
box (ComboBox1) appear on each row of an entire column through VBA. I
currently have ComboBox1 in row 1 with the list items from VBA code.

thanks, JJ

--

Dave Peterson

make a combo box repeat for entire column?
 

That's what I started with, actually a list with a combo box on top of it so
the user could start to type the value, but I don't really like how it's
working. I was just wondering if it could be done. As for the amount of
rows, no, I could probably start it off with just 200 and then increase in
increments if need be.



"Dave Peterson" wrote:

All 64k rows (or 1MB rows in xl2007???).

Have you thought about using Data|Validation instead?


jjordan wrote:

Hi there,

I'm wondering if there is a way on my spreadsheet I can make the same combo
box (ComboBox1) appear on each row of an entire column through VBA. I
currently have ComboBox1 in row 1 with the list items from VBA code.

thanks, JJ

--

Dave Peterson

make a combo box repeat for entire column?
 

I'm not sure I'd use the combobox from the control toolbox toolbar for so many
cells. Too many of these controls can cause nasty things to happen to the
workbook. Lots of them scare me.

But if you want...

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim OLEObj As OLEObject

With Worksheets("Sheet1")
Set myRng = .Range("b1:B20")
End With

For Each myCell In myRng.Cells
With myCell
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, displayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
OLEObj.LinkedCell = .Cells
'hide the value in the cell??
.Cells.NumberFormat = ";;;"
OLEObj.ListFillRange _
= Worksheets("sheet99").Range("a1:a10").Address(exte rnal:=True)
OLEObj.Object.Style = fmStyleDropDownList
OLEObj.Object.MatchEntry = fmMatchEntryComplete
End With
Next myCell

End Sub

====================
Instead, I'd use on combobox and show/hide it whenever you select a cell in that
range.

Create a combobox from the control toolbox toolbar and set it up exactly the way
you want it (size, listfillrange, etc). Call it Combobox1.

Then rightclick on the worksheet tab and select view code. Then paste this code
into the code window that you see:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'hide it to start
Me.ComboBox1.Visible = False

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then
Exit Sub
End If

Me.ComboBox1.Top = .Offset(0, 1).Top
Me.ComboBox1.Left = .Offset(0, 1).Left
Me.ComboBox1.Visible = True
Me.ComboBox1.LinkedCell = .Address(external:=True)

End With
End Sub




jjordan wrote:

That's what I started with, actually a list with a combo box on top of it so
the user could start to type the value, but I don't really like how it's
working. I was just wondering if it could be done. As for the amount of
rows, no, I could probably start it off with just 200 and then increase in
increments if need be.

"Dave Peterson" wrote:

All 64k rows (or 1MB rows in xl2007???).

Have you thought about using Data|Validation instead?


jjordan wrote:

Hi there,

I'm wondering if there is a way on my spreadsheet I can make the same combo
box (ComboBox1) appear on each row of an entire column through VBA. I
currently have ComboBox1 in row 1 with the list items from VBA code.

thanks, JJ

--

Dave Peterson


--

Dave Peterson