CODEWITHSUNDEEP
vbaexcel
p0tta
p0tta

Reputation: 1651

Dynamically add a list (table) to a specific cell (column) in Excel using VBA

I am trying to have the L column in Excel populate a Drop Down List with values (now Table?) in Excel and here is my code. I have manually created a Table called Table1 with some dummy values. I can hardcode the values inside the Table so that's not an issue. I just need to run some conditions and for some specific rows, I need to add a dropdown at the L column in Excel. Any help would be appreciated. Also, is there an easy way to color specific rows in Excel? 

Sub button_click()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet2")
arrColumnNames = i.ListObjects("Table1")

Dim d
Dim j
d = 1
j = 13

Do Until IsEmpty(i.Range("K" & j))

If i.Range("K" & j) = "Y" Then
d = d + 1
e.Rows(d).Columns("A:E").Value = i.Rows(j).Columns("A:E").Value
i.Rows(j).Columns("L") = arrColumnNames
End If
j = j + 1
Loop
End Sub

Upvotes: 0

Views: 2325

Answers (3)

Viktor West
Viktor West

Reputation: 574

About coloring a row Interior.ColorIndex can be used.

Sub colorIt()
    Dim rng As Range
    Set rng = ThisWorkbook.ActiveSheet.Rows(2)
    rng.Interior.ColorIndex = 6
End Sub

Upvotes: 0

Ian Felton
Ian Felton

Reputation: 91

This is another way to add a drop-down list to a table.

Public Sub AddDropdownListColumnToTable()

    Dim sht As Worksheet
    Dim colCount As Long

    Set sht = ThisWorkbook.Worksheets("MySheetName")

    colCount = sht.ListObjects(1).ListColumns.Count + 1

    sht.ListObjects(1).ListColumns.Add (colCount)
    sht.ListObjects(1).ListColumns(colCount).Name = "Criteria"
    With sht.ListObjects(1).ListColumns(colCount).DataBodyRange.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Item One, Item Two, Item Three, Item Four"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Upvotes: 0

KyloRen
KyloRen

Reputation: 2741

I think I see what you are after. This will create a Validation List and populate the list with values of you choice.

Dim myRng As String

myRngr = Range("A1:A10").Address

With Range("B2").Validation
    .Delete
    .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, Formula1:="=" & myRngr & ""
End With

To amend what range to what source data you need, you just need to change this line,

myRngr = Range("A1:A10").Address

Then you could use a loop to run through the L column and if a criteria is met add a validation list.

Hope that helps.

Upvotes: 1

Related Questions