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
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