Create Drop-down List in Excel with Python

[jld730]

Hello All,

I wrote Python code to write data to an Excel worksheet1, and also add 5 status types to worksheet2 to be used to make a dropdown list for one column in worksheet1. Could someone help me with the code to do this in Python? I recorded the macro >>

Sub Macro2()
'
' Macro2 Macro
'
 
'
    Sheets("Sheet2").Select
    Range("A1:A5").Select
    Range("A5").Activate
    ActiveWorkbook.Names.Add Name:="ValidStatus", RefersToR1C1:= _
        "=Sheet2!R1C1:R5C1"
    Sheets("Sheet1").Select
    Columns("I:I").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=ValidStatus"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
 

I have tried a few things (like below), but am stumped and can't find much documentation out there. I need to declare the name of the 5 cells on worksheet2 as 'ValidStatus' and get column I on worksheet1 to be a list with ValidStatus as the choices.

ws1 = wb.Worksheets(1)
ws2 = wb.Worksheets(2)
 
ws2.Cells(1,1).Value = "GREEN"
ws2.Cells(2,1).Value = "YELLOW"
ws2.Cells(3,1).Value = "RED"
ws2.Cells(4,1).Value = "WHITE"
ws2.Cells(5,1).Value = "NOT SURE"
 
ws1.Columns("I").Validation.Add(Type = "xlValidateList", xlValidAlertStop, xlBetween, "=ValidStatus")
 

Any help would be greatly appreciated. And, if anyone could refer me to some good documentation on Python and Excel, well that would be great!

Thanks,

jenn

[jld730]

Add-on -->

I finally (been looking/asking for months) ran across a method to get at the value of Excel constants for use in Pyhton scripting.

From http://aspn.activestate.com/ASPN/Coo.../Recipe/528870 ...

To get the value of Excel Constants such as xlEdgeLeft (7) or xlThin (2)
type e.g. Debug.Print xlEdgeLeft in the Immediate window of the VBA editor and press enter.

This helps for general knowledge, but still have not gotten my script to work...

[jld730]

I figured it out myself after many hours!!! Solution is:

ws1 = wb.Worksheets(1)
ws2 = wb.Worksheets(2)
 
ws2.Cells(1,1).Value = "GREEN"
ws2.Cells(2,1).Value = "YELLOW"
ws2.Cells(3,1).Value = "RED"
ws2.Cells(4,1).Value = "WHITE"
ws2.Cells(5,1).Value = "NOT SURE"
 
wb.Names.Add("ValidStatus", "=vl!R1C1:R5C1")
ws1.Columns("I").Validation.Add(3, 1, 1, "=ValidStatus") ##Type = xlValidateList/3, AlertStyle = xlvalidAlertStop/1, Operator=xlBetween/1, Formula1="=ValidStatus"