• 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

    Hui...

  • 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

JujitsuBunny

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?
 

Attachments

  • validation tests.xlsx
    20.7 KB · Views: 1
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
Code:
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]
 
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.
 

Attachments

  • V Test.xlsm
    29 KB · Views: 3
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):
Code:
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:
Code:
For each oc in org.Cells
  'process the cell
  Next oc
 
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).
 
So I have made some progress but it still fails when I scale it up to the giant working sheet. Can't figure out what I'm doing wrong.
 

Attachments

  • validation tests (1).xlsm
    38.3 KB · Views: 2
Well, don't make me guess—what does it do wrong?

By the way, if you don't already know about this, you should experiment with the <F8> key, which lets you step through your program one statement at a time. While that statement is highlighted in general, the program is stopped and you can look at variables to see their values, thus understanding what's happening. Very handy for debugging.
 
I expanded the source tab to what the working schedule actually uses. I can make this work in smaller scales like one of the other posted books but when I bring it to the actual size that I need, it won't change the validated choices no matter what I tweak. I feel like I'm close and maybe my variable references are just skewed but after messing with it so long, I thought I would come back.
 
No, wait—I didn't ask what it doesn't do, I'm asking what it does do. To diagnose what's going on, we need error messages or specific erroneous outputs. Better yet, tell me that "when the program gets to 'Intersect(Target, Range("O4:R" & lr))', it says "Invalid Method". Thats' just an example, of course; the point is that it's specific. At what point (exactly) in the program does the logic go wrong, and in what way?
 
Because it's code behind the sheet and not a module, I can't step through the operations. I haven't been able to debug anything worthwhile before or after scaling it up.
 
I can't tell whether you're confused or I am. You say "code behind the sheet", which sounds like a module to me, and then you add "and not a module". Maybe we'd better drill down.

When I open your workbook I see three worksheets. Then I go to the VBE, that is, the Visual-Basic editor, to look at the code. To do that, I can click on "Developer" in the ribbon and then select "Visual Basic". Or (my more usual way) I just hit <Alt-L><V>.

It's possible that "Developer" isn't one of the options on your ribbon; it isn't when Excel comes from the factory. To enable it, go to File, Options and select "Customize Ribbon". In the right column you'll see a number of checkboxes; find the one that's labeled "Developer" and set it. When you return to the main Excel window, you should see that "Developer" is now on your ribbon, if it wasn't before.

Once you have the VBA editor showing, you can look at the program code and, as I described above, step through it one statement at a time. You can also see the program statements for yourself and, perhaps now and certainly after some experience and coaching, figure out what each of them do.
 
I understand you're trying to get me to know where the change is taking place, errors that might be preventing it, etc., but, F8 does nothing, because this is not a module. It is a "private sub" in the background of the Source sheet itself and not executable code that could be assigned to a button. It's active all the time looking for value changes in the range I defined Range(O4:R & lastrow). I need lastrow because the number of rows can vary from 15-1200 depending on where this is being used.

If Target.count > 1, changing more than one cell at a time should error and reset the sub.

Or intersect(Target, range(O4:r & lastrow) is nothing , if the target selected is empty, will give error "could not find a value to replace", meaning the person's name wasn't in the validated area in the first place, no big deal there.

For i = 1 to 18, variable block. 15-18 are my named variables. If target is less than 15, it will take First name, Last name, (seniority#), in order to match the value and these values stay the same when replacing

range(xx:xx & lastrow).replace oldvalue, new value, xlpart replaces old values wi changed values from source.

While working through it and typing this, I did find an error in the variable block and it's currently halfway working. It will correctly replace agent name and seniority number, with the corresponding variable, as things change in the source.

What it's NOT doing, is it's deleting anything following the variable, IF a variable is already there. This is a new problem that I didn't have before, one variable works correctly but adding/removing a second or third will remove the other value.

Disregard the new problem. Variable in production was an asterisk. Deletes everything except what I told it. Everything is working as intended. Thank you. Just need to vent my problems and they figure themselves out.
 
Last edited:
LOL, it works that way for me, too: Very often I start an email to ask an expert why I'm not getting the results I want, and in the process of describing as exactly as possible what I'm doing and what it's doing in response, I see what I did wrong and discard the email; oh well, never mind.

Meanwhile, I realize I was a bit slow on the uptake with that stuff about <F8>. Yes, since your Worksheet_Change routine is event-driven, you cannot fire it up using <F8>. But you can still use <F8> to step through the program one statement at a time, this way: Before you change something in the worksheet (thus triggering the routine), create a sort of stopping point early in the program. That way when you change something in the worksheet, and the program starts to run automatically, it'll get to that stopping point and pause, at which point you can look at the program and then use the debug keys, including <F8>, to examine the process. You can create this "stopping point" in either of two ways:

1) Mark one or more lines in your program as a "breakpoint". Breakpoint lines are reverse-highlighted as white text on burgandy background (although I think you can change that in your editor settings), and when program execution gets to one of those lines it pauses execution while you look and decide whether and how to proceed. You can set a breakpoint using the <F9> key, or by clicking on the margin to the left of that line in the editor.

2) Just write a Stop statement anywhere in your program.

When I'm trying to figure out what's going on in an event-driven program, I usually just click on the margin next to the Sub statement itself. But if it's a long program and I'm thoroughly comfortable with the first part of the code, I stop the program later on, where I'm not sure what's going wrong. Then I can use <F8> (and the other keys) to debug.

Meanwhile, I gather you have everything working correctly now? No, wait, you still need to know how to find the last row in the worksheet, is that right?
 
Ok, I'm back. To find the last "inhabited" row in a worksheet I use one of two functions, depending on the circumstances:
Code:
' Return the last used row in a sheet.  Supply the sheet object, the number of
' header rows and the column that will be unbroken from top to botton, and
' I'll return the row number of the last used row.  This should work with no
' header rows, too.  If nHdr=0 and no data, returns 0.
Function LastRow(ows, Optional nHdr = 1, Optional cIdx = 1)
  Set oc = ows.Cells(nHdr + 1, cIdx)
  Select Case True
    Case oc.Value = "": LastRow = nHdr
    Case oc.Offset(1, 0).Value = "": LastRow = oc.Row
    Case Else: LastRow = oc.End(xlDown).Row
    End Select
  End Function
LastRow assumes that there's a column in the worksheet that is unbroken from top to bottom, that is, no cell in that column is empty. The function does an <End><Down> in that column, and where it stops is the last row. I call it using "rZ = LastRow(ows)", where "ows" is the worksheet object I'm interested in. LastRow assumes there's exactly one header row at the top, and that the "unbroken" column is column 1, but I can feed it optional arguments to change those defaults.

If I know my target worksheet has no unbroken column in it, the alternate function simulates <Ctrl-End>, which takes you to the bottom of the used range. But sometimes (if you used to have more used rows at the bottom and then deleted them) that method takes you below the last row with data in it; so after doing the <Ctrl-End>, LastRowBrk then does an <End><Up> in the first column (or another column if I tell it so) to get to the actual last inhabited row:
Code:
' Return the last used row in a sheet even if your index column isn't unbroken from top
' to bottom, using SpecialCells(xlCellTypeLastCell).  Supply the sheet object and the
' index column.
Function LastRowBrk(ows, Optional cIdx = 1, Optional bUnhide = True)
  If bUnhide Then ows.Rows.Hidden = False
  LastRowBrk = ows.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
  LastRowBrk = ows.Cells(LastRowBrk, cIdx).End(xlUp).Row
  End Function
I see I'm unhiding the worksheet first off; I must have discovered that <Ctrl-End> doesn't work on hidden worksheets.
 
Back
Top