Share via

adding data validation via VBA when the name of the named range is a variable instead of hard coded

Anonymous
2011-02-23T16:38:55+00:00

Greetings all -- I'm trying to programatically generate a data collection workbook.  It needs to include several lookup lists that will drive the acceptable choices in data validation for various columns.  The lookup data changes over time, so I can't just hard-code everything.  (FWIW, I'm generating this workbook from Access.)  Where I'm having trouble is creating a data validation where the Formula1 parameter is assigned to a variable, instead of hard coded the way the macro recorder handles this.   

The macro recorder produced the following: Selection.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=MyLookupList".

Trying to make this generic lead me to the following:

dim xlbk as Excel.Workbook

dim xlsht as Excel.Worksheet

dim strRangeName as string

dim rng as Excel.Range

'* xlbk and xlsht set upstream.  named ranges established upstream

set rng = xlsht.columns(1)

strRangeName = "=MyLookupList"

rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,Formula1:=strRangeName  '* this gives "error 1004" - first thing I tried

rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Chr$(34) & strRangeName & Chr$(34) '* this runs OK, but doesn't give desired result -- second thing I tried

The second approach gives me a validation, but the acceptable value list is the literal string "=MyLookupList", instead of referring to the named range MyLookupList.

Any suggestions?  I'm at my wit's end.  Thanks... Mark

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-02-23T19:12:43+00:00

    Your code works for me:

    Dim strRangeName  As String

    Dim rng As Excel.Range

    strRangeName = "=BLAH"

    Set rng = ActiveSheet.Columns(1)

    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

                       Operator:=xlBetween, Formula1:=strRangeName

    "rng" is a valid range.  I can get a 1004 error if I use an invalid range name, so you might want to check that out.

    Tim

    2 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-02-23T19:40:38+00:00

    I've found the answer.  To correctly create the named range in code, I had to use

    xlsht.Range(strRangeAddress).Name = strRangeName, where strRangeNameis the name for the new named range, and strRangeAddress is the address of the cells.

    Once that was done, the validation created just fine.

    Thanks for the reply.  You confirmed that I was headed in the right direction for creating my data validation.  Just had to get the prerequisites right.

    Mark

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-02-23T19:31:10+00:00

    Funny you should mention an invalid range name.  I was looking into the named ranges in my workbook.  They are built in code upstream of the logic that builds the validations.  

    In the code below, strRangeName is the name for the new named range, and strRangeAddress is the address of the cells.  (ItemType and $A$2:$A$45,respectively, in this case).

    xlbk.Names.Add Name:=strRangeName, RefersToR1C1:="=" & xlshtLookups.Name & "!" & strRangeAddress

    The expression "=" & xlshtLookups.Name & "!" & strRangeAddress resolves to =Lookups!$A$2:$A$45, which I can create through the Excel UI no problem.  However, running the sample line give me a formula error.

    I tried 

    xlbk.Names.Add Name:=strRangeName, RefersToR1C1:=chr$(340 & "=" & xlshtLookups.Name & "!" & strRangeAddress & chr$(34)

    This runs, but the RefersTo for the range becomes ="""=Lookups!$A$2:$A$45""", which is not valid for defining a validation.

    Looks like I'm back to the problem of assigning these parameter values from a variable instead of a hard coded string.  

    Any thoughts?  Thanks... Mark

    0 comments
  3. Anonymous
    2011-02-23T19:03:00+00:00

    Unfortunately, it didn't work -- same problems as before.  

    FWIW, whether I use set rng = xlsht.columns(1) or set rng = xlsht.columns(1).Cells, the address of the resulting range is $A:$A.  

    I tried 

    Set rng = xlshtInventory.Range( Cells(1, conInvtItemTypeCol).Address & ":" & Cells(xlsht.Rows.Count, conInvtItemTypeCol).Address)

    This results in a range address of $A$1:$A$1048576 (lots of cells in that range), and have the same problems.

    Any further thoughts?  Thanks...

    0 comments
  4. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2011-02-23T17:20:49+00:00

    Am 23.02.2011 17:38, schrieb AirborneNGLO:

    Everything looks good, but not this line:

    set rng = xlsht.columns(1)

    The return range from columns/rows is not a "real range", because it as no cells. I know this sounds crazy and sorry, I can not explain why it is so.

    Try this line

      set rng = xlsht.columns(1).Cells

    strRangeName = "=MyLookupList"

    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,Formula1:=strRangeName  '* this gives "error 1004" - first thing I tried

    and this both lines too and it should work.

    Andreas.

    0 comments