• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing validated values remix


New Member
Hello Chandoo! It's been awhile since I had a problem I can't solve but I know you're all smarter than I am with vba.

I need to create a dynamic data validation array where the cell value changes as the source does. Source is on one page, data validation on another, however the named range I use is not a single column and data validation area can vary in size of columns.

I've found other vba options here but can't seem to make any of them work as I need for multiple columns/rows as opposed to the single columns they are intended for. I'm not sure if I should link those examples or try to start fresh.

As the master data changes, the compiled names will change as well. After selecting the name in the validation drop-down, is it possible to change the chosen value within that entire range?


I haven't looked at your attachment yet, but from your description it sounds to me as though you need to create a module in the source worksheet named
Private Sub Worksheet_Change(ByVal orgT As Excel.Range)
The variable name of the argument can be whatever you want, but the rest of it must be exactly as shown, and it has to be in the code module of the source worksheet. Whenever the operator makes a change to that worksheet, Excel detects the "Worksheet_Change" event and runs this Sub. Upon execution, the variable you name as the argument points to the cell—or conceivably cells, but usually just one—that the change occurred in. Normally you would write your program to look first at the location of the cell that was changed, and Exit Sub if it isn't in a location you care about. Then you can have your program decide how to react, for example to place a resulting value elsewhere in the workbook.

I have a workbook I use to track the hours I charge to my clients. When I type in a client code on a new row, it checks to be sure I did it in the Client column, then adds today's date to the leftmost cell in that row and the starting time (ie Now) in the start-time column.

There are other things you can do. I taught that same worksheet eventually to spot when I double-click on a cell, note whether the column is for the start time or end time, fill in the current time and make other calculations accordingly.

In another worksheet that I wrote for a client, there are a lot of rows with file names, email addresses, names etc. If you click <Delete> in a particular column—this, too, is detected as a change even if there was nothing in the cell previously—the program starts up, checks to be sure that it was in the Date column (it exits if not), then creates a boilerplate email to that target with the file attached, and fills in today's date in the Date column. Saves a lot of trouble when emailing 400 workbooks to various victims.[COLOR]


New Member
Most of the other solutions I found use that. I've been trying to mold them into what I need but can't seem to replicate it across a larger area than one cell. Attached to this is the sheet with code, should've just posted that first. It's not working as I'd hope but maybe it's a start.


Multiple cells is more complex, but only in the first part where you have to figure out what changes are relevant. Your program looks at VAC.Row, which will give you the first row of the range that's been changed. Some helpful hints (or so I hope):

VAC.Cells.Count tells you how many cells are in the range that it's handing you. If it's just one, great. If it's more than one, you have to look through the range and figure out which cell(s) you have to process and which you can ignore.

Assuming you're concerned only with changes to certain parts of the worksheet, you can use the Intersect method to figure out how much of VAC is in that range, like this (and please make allowances for my coding style):
Set owb = ThisWorkbook
Set ows = owb.Worksheets("VALIDATION")
Set ocs = ows.Cells
Set org = Range(ocs(3, 4), ocs(6, 8))
set org = Application.Intersect(VAC, org)
' Now org has just the changed cells that are within the area you're interested in; you can use org.Cells.Count to
' see whether there's anything there.
Once you know which cells have been changed and that you want to process, you can look through those cells handling each one individually:
For each oc in org.Cells
  'process the cell
  Next oc


New Member
Assuming you're concerned only with changes to certain parts of the worksheet
Yeah that's exactly what this is. The named range contains the names+vacation type, which can change based on what the source data does, but the range is only used in the validation.

I'll play with the cells.count and see what I can do.
I meant to add that there are also methods you can use to figure out the first and last row and column in the range. I think org.Rows.Count and org.Columns.Count tell you how many (but you should try it to be sure; I may be mistaken). And of course org.Cell(1).Row and ...Column tell you the top row and left column. I forget what you do to find the right column and bottom row, but of course you can always look at the address of the last cell, which might be org.Cells(org.Cells.Count).