Trouble with my VBA code

Help Studio
, ,
1

I’m trying to invoke a VBA code to add a drop down list to every row in a column. It is Column R, also named Mandatory Selection. The drop down options are Agree, Disagree, and Agree with Fee. This is my code, it is not running and I keep receiving an error.

Sub CreateDropDown()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

' Set the worksheet where you want to create the drop-down lists
Set ws = ThisWorkbook.Sheets("Sheet1")

' Set the range in Column R where you want to create the drop-down lists
Set rng = ws.Range("R2:R" & ws.Cells(ws.Rows.Count, "R").End(xlUp).Row)

' Add drop-down list to each cell in the range
For Each cell In rng
    With cell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Agree,Disagree,Agree with Fee"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
Next cell

End Sub

2

@mojo001

Looks like it should xldown instead of up

Also first did you try running it on excel directly to check the issue?

Cheers

3

I did and it highlighted these two lines

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“Agree,Disagree,Agree with Fee”

4

I feel like this belongs on a VBA forum perhaps?

The Invoke VBA works properly etc so this doesnt seem like a UiPath issue.
I’d suggest getting your VBA to work outside of UiPath and using the appropriate forums to do so and coming here if then running it UiPath doesnt work.
Otherwise the type of help given here becomes rather blurry.

Also saying that ‘it errors’ without specifying the errors is like going to the doctor and saying ‘it hurts’, if you want help you also need to explain the issue clearly and state the error.

5

Seems there is an error with the syntax. could you please use the below code

Sub CreateDropDownLists()
’ Set the worksheet where you want to create the drop-down lists
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)

' Set the range in Column R where you want to create the drop-down lists
Dim rng As Range
Set rng = ws.Range("R2:R" & ws.Cells(ws.Rows.Count, "R").End(xlUp).Row)

' Add drop-down list to each cell in the range
Dim cell As Range
For Each cell In rng
    With cell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Agree,Disagree,Agree with Fee"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
Next cell

End Sub

1 Like