+ Reply to Thread
Results 1 to 4 of 4

Dynamic Data Validation in VBA

  1. #1
    Registered User
    Join Date
    09-17-2012
    Location
    Ripon, WI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Dynamic Data Validation in VBA

    I'm looking to write a VBA code to dynamically apply a "list" type data validation constraint to a range of cells. I recorded a macro to try to reverse engineer it to do what I want, and I'm running into trouble. This is my current code:

    Please Login or Register  to view this content.
    My problem line is:
    Please Login or Register  to view this content.
    I'd like to be able to dynamically assign this range (something like this):
    Please Login or Register  to view this content.
    Excel does not like this syntax.

    Does anyone know how I can tweak this to allow dynamic range selection for the Formula?

    THANKS IN ADVANCE!!!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Dynamic Data Validation in VBA

    Something like this might work. Build the address as a sting:

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Dynamic Data Validation in VBA

    Not meaning to hijack this query, but is it possible instead of using a list resident on a sheet for the "Formula1" parameter, to use the contents of a VBA array variable?

    Thanks

    Postscript:

    Solved this one myself by instead of using a VBA array variable as originally intended, using a VBA string variable which by looping I made into a comma separated list, then used that string variable as "Formula1" and it works. Which is a novelty for me!
    Last edited by greencroft; 09-18-2012 at 12:56 PM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Dynamic Data Validation in VBA

    @greencroft: Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

    It's strange how so many say something like: Not meaning to hijack ... , then go right ahead and do it anyway!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1