Copying Combo Boxes on table

fcontreras

New Member
Joined
Jul 17, 2017
Messages
1
Hi all!

I have made a table that will be used for scheduling purposes, I have combo boxes that will be used to choose the clients name & select a time & have vlook up formulas to enter their information below. My issue is that I would like to replicate my combo boxes all through out the table's columns and rows with out having to insert new ones over & over again. Not sure how to do this...
I have tried recording a macro but I did not record the properties of the combo box..
I don't know much VBA at all but I tried using this code I found on a forum:
( I would like to include the ComboBox1.DropDowm code to all new combo boxes)
I have thought about using data validation but sometimes we add people to the schedule that aren't in our original list.

Private Sub ComboBox1_Change()
ComboBox1.DropDown
End Sub

Sub AddFormsComboBoxes()
'assumes 1 combo box named 'Drop Down 1' has
'been placed on the sheet and set up with
'the ListFillRange information so all that is
'needed to do is copy that control and
'change the Link cell address
'
'we can control everything needed for the
'process here
'these describe the LinkedCell address
'for the first/source control
Const sourceControlName = "ComboBox1" ' change as required
Const linkCellCol = "X"
Const firstLinkRow = 6 ' row for original control
'control how many copies to make
Const copiesToMake = 10 ' original + 99 = 100
'you can make this a positive number to
'add spacing between the new controls
'as set to 0 the controls will be
'placed very tightly on the sheet
Const vSpaceBetweenControls = 3
'variables needed to perform the copying and positioning
Dim leftPosition As Single
Dim topPosition As Single
Dim ctlHeight As Single
Dim linkCellRow As Long
Dim LC As Long

ActiveSheet.Shapes.Range(Array(ComboBox1)).Select
leftPosition = Selection.Left
topPosition = Selection.Top
ctlHeight = Selection.Height
linkCellRow = firstLinkRow

For LC = 1 To copiesToMake
topPosition = topPosition + ctlHeight + vSpaceBetweenControls
linkCellRow = linkCellRow + 3 'would like new combo boxes pasted 3 rows down if possible'
Selection.Copy
ActiveSheet.Paste ' new control becomes selected
With Selection
.Top = topPosition
.Left = leftPosition ' aligned vertically
.LinkedCell = linkCellCol & linkCellRow
End With
Next

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top