Excel Com Drop Down List

[RAK]

I am using COM to populate a setup WorkBook in Excel. Into one of the sheets in the workbook named 'Index" - I am placing lists of items that will vary in row quantity for each user. I would like to use COM to define Drop Down select list in another sheet of the workbook using the columns form the Index sheet.

I am using a template and have defined them manually - but the row length will vary so the drop down list ends up with many blank items. Over time - indexes will change in size and content so I need the exe to do it for users.

How can I use COM to define the drop down list for a section of a column referencing fields like: "=Index!$F$2:$F$100"

I actually need 2 functions - one to define the list name / range as show above and one to assign it as field validation drop down list.

I could only find one item in the tech database that may? apply: (Article ID:   W16604)

oV = oXL.Selection.Validation
oV.Delete()
oV.Add(::Type=xlValidateList, AlertStyle=xlValidAlertStop, Operator=xlBetween, Formula1="DOMESTIC,IMPORT,TRUCK,UNK")
oV.IgnoreBlank = @True
oV.InCellDropdown = @True
oV.InputTitle = "Unit Type"
oV.ErrorTitle = ""
oV.InputMessage = "Please Select "
oV.ErrorMessage = ""
oV.ShowInput = @True
oV.ShowError = @True
oV=0

Over the years - this forum has been invaluable!
Thanks
Roy

[stanl]

Not sure if this helps. But assuming the template adjusts based on the user opening it...

One method would be to have the pick-list as a separate sheet, and the drop-down referencing a named range (in that sheet A1-A[n]). After setting up an initial sample named range, Have your WB code

1. select that sheet and issue .clearcontents
2. Populate the picklist for the 'user' in that sheet starting in cell A1
3. Set/re-set the range name to the new .usedrange

The dropdown will then reference the new pick list.

I have used this technique for manager<>agent pick lists... i.e. a manager logs in and the pick list is populated for that manager.

[Deana]

I often record a and Excel macro, manually reproduce the steps I want scripted, then convert the resulting macro into WIL code.

[RAK]

Not sure if this helps. But assuming the template adjusts based on the user opening it...

One method would be to have the pick-list as a separate sheet, and the drop-down referencing a named range (in that sheet A1-A[n]). After setting up an initial sample named range, Have your WB code

1. select that sheet and issue .clearcontents
2. Populate the picklist for the 'user' in that sheet starting in cell A1
3. Set/re-set the range name to the new .usedrange

The dropdown will then reference the new pick list.

I have used this technique for manager<>agent pick lists... i.e. a manager logs in and the pick list is populated for that manager.

""Set/re-set the range name to the new .usedrange"
This is what I am looking for, the WB code to reset field range from "=Index!$F$2:$F$100" to "=Index!$F$2:$F$45"

did a google vb search and FOUND IT!
         qt = itemcount(comHeads,',')
         clipput(strreplace (comHeads,',',cr))
         myRange = 'F2'
         oSelection=objWorkSheet.Range(myRange)
         oSelection.Select
         objWorkSheet.paste
         myRange = "$F$2:$F$":qt
         objWorkSheet.range(myRange).name = "comPaidSelect"

thanks!

[RAK]

I am now looking to assign the validation list (as a drop down) to a cell or better still; a range of cells  - tried this but no luck yet

      objWorkSheet = objawb.worksheets('Database Field Mapping')
      objWorkSheet.Activate
      myRange = "$B$2:$B$30"
objWorkSheet.myRange.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=comDueSelect"

tried below - keeps returning 'unknown name' error message on the last line- can anyone help with the syntax?
      xlValidateList = 3
      xlValidAlertStop = 1
      xlBetween = 1
      objWorkSheet = objawb.worksheets('Database Field Mapping')
      objWorkSheet.Activate
      myRange = "$B$2:$B$":30
      objWorkSheet.myRange.Validation.Add(::Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=comDueSelect")

also tried:
      col = objWorkSheet.Columns(2)
      col.Validation.Add(::Type:=xlValidateList, Formula1:="=comDueSelect")
returning err:  unsupported variant type

[Deana]

Try removing the leading colon from the individual parameters:

Code (winbatch) Select Expand

objWorkSheet.myRange.Validation.Add(::Type=xlValidateList, AlertStyle=xlValidAlertStop, Operator=xlBetween, Formula1="=comDueSelect")

[RAK]

YES!
thanks again Deana!