By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Create Drop-down List in Excel with Python

P: 34
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 >>

Expand|Select|Wrap|Line Numbers
  1. Sub Macro2()
  2. '
  3. ' Macro2 Macro
  4. '
  5.  
  6. '
  7.     Sheets("Sheet2").Select
  8.     Range("A1:A5").Select
  9.     Range("A5").Activate
  10.     ActiveWorkbook.Names.Add Name:="ValidStatus", RefersToR1C1:= _
  11.         "=Sheet2!R1C1:R5C1"
  12.     Sheets("Sheet1").Select
  13.     Columns("I:I").Select
  14.     With Selection.Validation
  15.         .Delete
  16.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  17.         xlBetween, Formula1:="=ValidStatus"
  18.         .IgnoreBlank = True
  19.         .InCellDropdown = True
  20.         .InputTitle = ""
  21.         .ErrorTitle = ""
  22.         .InputMessage = ""
  23.         .ErrorMessage = ""
  24.         .ShowInput = True
  25.         .ShowError = True
  26.     End With
  27. End Sub
  28.  

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.

Expand|Select|Wrap|Line Numbers
  1. ws1 = wb.Worksheets(1)
  2. ws2 = wb.Worksheets(2)
  3.  
  4. ws2.Cells(1,1).Value = "GREEN"
  5. ws2.Cells(2,1).Value = "YELLOW"
  6. ws2.Cells(3,1).Value = "RED"
  7. ws2.Cells(4,1).Value = "WHITE"
  8. ws2.Cells(5,1).Value = "NOT SURE"
  9.  
  10. ws1.Columns("I").Validation.Add(Type = "xlValidateList", xlValidAlertStop, xlBetween, "=ValidStatus")
  11.  
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
Mar 3 '08 #1
Share this Question
Share on Google+
2 Replies


P: 34
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...
Mar 3 '08 #2

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

Expand|Select|Wrap|Line Numbers
  1. ws1 = wb.Worksheets(1)
  2. ws2 = wb.Worksheets(2)
  3.  
  4. ws2.Cells(1,1).Value = "GREEN"
  5. ws2.Cells(2,1).Value = "YELLOW"
  6. ws2.Cells(3,1).Value = "RED"
  7. ws2.Cells(4,1).Value = "WHITE"
  8. ws2.Cells(5,1).Value = "NOT SURE"
  9.  
  10. wb.Names.Add("ValidStatus", "=vl!R1C1:R5C1")
  11. ws1.Columns("I").Validation.Add(3, 1, 1, "=ValidStatus") ##Type = xlValidateList/3, AlertStyle = xlvalidAlertStop/1, Operator=xlBetween/1, Formula1="=ValidStatus"
  12.  
  13.  
Mar 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.