PDA

View Full Version : [SOLVED] Adding more drop down list with my macro?



b_rianv
10-06-2016, 04:28 PM
Good evening everyone,

I am having trouble adding more drop down list too my macro. I already have column (B) done, still need to do columns (C,D,E,F,G). I Also have my macro adding a new row at (A2) when I click my Active X button found in cells (K1,L1). So when I click my Active X button a New Row is added as well as a drop down list in cell (B2),I know you can't see my Active X button well but I can't get the text too show because it is a small button and want it that way,have tried everything in the properties area.

Can someone help me add these other drop down list commands to my macro for columns (C2,D2,E2,F2,G2)?

Here is my workbook

b_rianv
10-06-2016, 04:29 PM
Here is my code...

Private Sub CommandButton1_Click()

Sheets("Search").Range("A2").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Search").Range("A2:G2").Select
Selection.Borders.Weight = xlThin

Sheets("Search").Range("B2").Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=data_2!$A$2:$A$19"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub

b_rianv
10-06-2016, 04:30 PM
Thank you for your for your time

Paul_Hossler
10-06-2016, 06:22 PM
Not exactly sure make it is you're trying to do, so this is my best guess





Option Explicit

Private Sub CommandButton1_Click()
Dim i As Long
Dim r As Range
With Selection.EntireRow
For i = 1 To 6

With .Cells(1, i + 1).Validation
Set r = Worksheets("data_2").Cells(2, i)
Set r = Worksheets("data_2").Range(r, r.End(xlDown))
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=data_2!" & r.Address
.IgnoreBlank = True
.InCellDropdown = True
End With
Next I
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A:G").Interior.Color = xlColorIndexNone
Intersect(Target.EntireRow, Range("A:G")).Rows(1).Interior.Color = RGB(255, 255, 0)
End Sub

b_rianv
10-06-2016, 06:52 PM
Thank you Paul, but it isn't working plus my search stopped work as well. Please look at my code, my code allows me to
1.) Add a NEW ROW, from (A2,G2)
2.) Insert a Drop down list in to the cell (B2) which gets its data from (data_2) workbook, which is column ( A2)
I want to keep everything the way it is.
What I want to do is have my code ADD more drop down lists to the other columns (C2,D2,E2,F2,G2) with the data from data_2 worksheet, when I click the insert button as well

Paul_Hossler
10-07-2016, 05:51 AM
Sorry, I removed code that wasn't pertinent to the question, and made the incorrect assumption that you wanted to insert anywhere

Try this version



Option Explicit

Private Sub CommandButton1_Click()
Dim i As Long
Dim r As Range
Range("A2:G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Range("A2:G2")
.Borders.Weight = xlThin

For i = 1 To 6
With .Cells(1, i + 1).Validation
Set r = Worksheets("data_2").Cells(2, i)
Set r = Worksheets("data_2").Range(r, r.End(xlDown))
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=data_2!" & r.Address
.IgnoreBlank = True
.InCellDropdown = True
End With
Next I
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A:G").Interior.Color = xlColorIndexNone
Intersect(Target.EntireRow, Range("A:G")).Rows(1).Interior.Color = RGB(255, 255, 0)
End Sub

b_rianv
10-07-2016, 06:37 AM
That my friend is GREAT, Thank you so much!!