PDA

View Full Version : Adding comboboxes in a loop



strato
08-01-2017, 10:13 AM
Hello
I am using Excel 2010 and wish to add comboxes to a spreadsheet from a form.
It populates the combobox with 4 members but I only get one combobox so I assume there is a misfire with my loop.
I'm wanting to have a cb in every line for column A
Thanks for any help!



Sub CreateComboBox1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim ViewMode As Long

With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False


Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=0, Top:=15, Width:=70, _
Height:=20)
With .Object
.AddItem "BC"
.AddItem "RC"
.AddItem "Steve"
.AddItem "C shift"
End With
End With
End If
End With
Next Lrow
End With
End Sub

mdmackillop
08-01-2017, 10:31 AM
With Top:=15 you're putting them all in the same place! Try Top:=.Top

strato
08-01-2017, 02:40 PM
Thank you that works really nicely!
Is there a way I can get it to skip the first row?
That was the reason I had the top:=15

Thanks again!

Bob Phillips
08-01-2017, 03:12 PM
Try

Top = .Top + 15

mdmackillop
08-01-2017, 03:32 PM
Firstrow = .UsedRange.Cells(1).Row +1

strato
08-06-2017, 07:50 AM
Thank you that worked very well!
Only problem I have is there is one extra row for a combobox with no other data in it.
Not sure if that is what mdmackillop was trying to address with his code ... Firstrow = .UsedRange.Cells(1).Row +1
that he posted

mdmackillop
08-06-2017, 07:58 AM
Firstrow = .UsedRange.Cells(1).Row +1
Correct. That will omit the Combo from Row 1

strato
08-06-2017, 08:39 AM
It already skips the combo from the first row I believe the code ... Top:=.Top + 15 does this
but it is putting an extra combo at the very bottom that is not needed.

mdmackillop
08-06-2017, 08:49 AM
Use Top:=.Top and Firstrow = .UsedRange.Cells(1).Row +1
Top:=.Top + 15 moves everything down by one row giving the "extra" combo.

snb
08-07-2017, 12:37 AM
1. It's not a good idea to create ActiveX-controls on the fly.
2. You'd better create them in design mode
3. If necessary you can make them invisible; and visible again when entering the sheet/a certain colum/a certain cell
4. Populating a combobox/listbox is best performed by .List


Sub M_snb()
for each it in activesheet.columns(1).specialcells(2)
ActiveSheet.OLEObjects.Add("Forms.ComboBox.1", 0, 0, 0, it.top, 70, 20).list=array("BC","RC","Steve","C shift")
Next
End Sub

strato
08-07-2017, 11:28 AM
Not sure this is the right approach I'm applying multiple suggestions


Sub cBox()
For Each it In ActiveSheet.Columns(1).SpecialCells(2)
ActiveSheet.OLEObjects.Add("Forms.ComboBox.1", 0, 0, 0, it.Top, 70, 20).List = Array("BC", "RC", "Steve", "C shift")
Next
End Sub


Sub CreateComboBox1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim ViewMode As Long

With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False


Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


'We loop from Lastrow to Firstrow (bottom to top)
Firstrow = .UsedRange.Cells(1).Row + 1
For Lrow = Lastrow To Firstrow Step 1
With .Cells(Lrow, "A")
Call cBox
' If Not IsError(.Value) Then
' With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
' Link:=False, DisplayAsIcon:=False, Left:=0, Top:=.Top + 15 And Firstrow = .UsedRange.Cells(1).Row + 1, _
' Width:=70, Height:=16)
' With .Object
' .AddItem "BC"
' .AddItem "RC"
' .AddItem "Steve"
' .AddItem "C shift"
' End With
' End With
' End If
End With
Next Lrow
End With
End Sub

But now I'm not getting anything, no combos or any changes

mdmackillop
08-07-2017, 11:52 AM
Sub CreateComboBox1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim ViewMode As Long

With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Firstrow To Lastrow
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=0, Top:=.Top, Width:=70, _
Height:=.RowHeight)
With .Object
.AddItem "BC"
.AddItem "RC"
.AddItem "Steve"
.AddItem "C shift"
End With
End With
End If
End With
Next Lrow
End With
End Sub

mdmackillop
08-07-2017, 11:59 AM
Just a thought, would Data Validation not suffice?

strato
08-09-2017, 10:00 AM
Not sure I understand about Data Validation

mdmackillop
08-09-2017, 10:29 AM
See attached