Wednesday 29 January 2020

microsoft excel - How can I add Drop Down List after inserting new row in VBA


I am trying to insert a drop down list with two items after adding a new row. This code below not working properly.


     Dim varUserInput As Variant
varUserInput = InputBox("Enter Row Number where you want to add a row:",
"What Row?")
If varUserInput = "" Then Exit Sub
RowNum = varUserInput
Rows(RowNum + 1).Insert Shift:=xlDown
With Sheet1.RowNum.listBox1
.AddItem "Paris"
.AddItem "New York"
End With

Answer



Try this:


Sub Macro1()

RowNum = InputBox("Enter Row Number where you want to add a row:", "What Row?")
If RowNum = "" Then Exit Sub

Range("A1").Offset(RowNum, 0).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="London,Sydney"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Note: I recorded most of this using the Record Macro tool on the Developer tab, then edited to use parts of your code. It's a useful method when you aren't sure the exact code required to insert something, such as this drop down validation list.


Edit: To add two lists at the same time:


Sub Macro1()
'ask user for row to insert data
RowNum = InputBox("Enter Row Number where you want to add a row:", "What Row?")
If RowNum = "" Then Exit Sub

'insert dropdowns in column A
Range("A1").Offset(RowNum, 0).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="London,Sydney"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

'inset second drop down in column E
Range("E1").Offset(RowNum, 0).Select '<-- change reference to E
'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '<-- line removed as don't need to insert twice

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="New York,Jakarta"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

and with many identical lists:


Sub Macro1()
Dim RowNum As Integer
Dim Lists As Integer

'ask user for row to insert data
RowNum = InputBox("Enter Row Number where you want to add a row:", "What Row?")

'insert row
Range("A1").Offset(RowNum, 0).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'ask how many drop down lists to make
Lists = InputBox("Enter number of drop down lists to make in this row:", "Number?")

i = 0

Do While i < Lists And i < 1000
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Hong Kong,Rome,Wellington,Cairo"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

'move across one cell
ActiveCell.Offset(0, 1).Range("A1").Select

i = i + 1
Loop


End Sub

No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...