worksheet_change and data validation

Hi there

I have an Excel spreadsheet in which one column has data validation set. So the cells in this column have drop-down lists with the pre-set allowed values. Now I want the cell in the neighboring column to be set to some default value when the user selects an item from the drop-down list.

This seemed like it would be easy, using the Worksheet_Change event handler. When a cell in the first column is changed, it initiates a function which sets the value in the neighboring column to some default value:
Target.Offset(0,1).Value = "default".

The problem is that it doesn't work when using the drop-down list. If the user types in the value manually, or if a cell in the first column is deleted, it DOES fill in the neighboring column. But when using the drop-down list, it doesn't work.

The problem is NOT that Worksheet_Change doesn't recognize the change. I saw in some earlier articles that in pre-Excel-97, changes made through drop-down lists are not recognized by the WorkSheet_change function. Well, I'm using Excel-2003, and the change IS recognized (which I know because the MsgBox pops up), but the value in the neighboring cell is simply not changed.

Here is my function:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("changeCAS")
If Not Intersect(Target, VRange) Is Nothing Then
MsgBox ("change recognized!!")
Target.Offset(0, 1).Value = "this is the default"
End If
End Sub

Any input would be greatly appreciated!!!
Thanks in advance.
Emma

Answer this question

worksheet_change and data validation

rmcmullan
Hi Chas

Thanks so much for your quick response. I'm not sure what you mean by "this list is populated by an area elsewhwere"...

Just to make a simple example: Say the cells in the range A1:A10 have a dropdown list of 3 possible values: "country", "state", or "city". If A1 is set by the user to "country", I want the cell B1 to be set to some default value (say "USA"). So when the user makes a change to the range A1:A10 (which I called "changeCAS" in my function), the cell to the right of the Target cell should be set to "USA".

I can tell that when I make a change to a cell in A1, my function IS invoked, because the message box pops up. BUT, the value in B1 is not updated. It doesn't matter what its value is set to before, it is not changed with my function.

BUT, if I simply delete the contents of A1, B1 IS updated with the default value. Or if I type in a value in A1 manually, without using the drop-down list, B1 is also updated. But not when I update A through the drop-down list.

Does this make sense Thank you so much for taking a look at this!
Emma
Don Isenor
An interesting side note...

In my "haunted" spreadsheet, when I make a change which triggers the neighoring cell to be updated (such as deleting the contents or entering text manully), the button in the message box is "highlighted". Then, as soon as I click "ok", the content in the neighboring cell is filled in as we want it to.

On the other hand, when I make a change by selecting an item from the drop-down list, the message box pops up, but the button looks different. It is missing that dashed outline which makes it look "highlighted".

I doubt this sheds much more light on the problem... but I thought I'd share just in case it does... :-)

Thanks a lot for your time on this. If you have any other ideas, please let me know!
Thanks
Emma
DotNetDoctor
Hello Juan,

The Volatile statement in a function, causes the function to be called when anything changes on a sheet (or before). The way to fix this is to omit the volatile statement and within the procedure that you are running, call the function manually.

If you could post your code, it will help me understand the problem you are having.

Chas
Sudhakar.hcitek
Hi Chas

I have sent you a simplified spreadsheet that illustrates the problem. Please let me know if you need anything else.

Thank you!
Emma
Peter McEvoy
Hello Emma,

This is what I have done:

A1..A10 is defined as range changeCAS

Cell G1="Country"

Cell G2="State"

Cell G3="City".

Cell A1 has a drop down list which is created by:

Data -> Validation -> Settings and then from the "Allow" box dropdown list "List" is selected, then range G1..G3 is selected.

Then I have typed your piece of code in the Worksheet Code Module.

It works,

Each time a change is made to cell A1 either via drop down or manually, cell B1 is updated with "USA" (I changed line in your code from "this is default" to USA).
akshah
This is all so strange. If I open Excel from scratch, and create a new workbook with the identical information that you have in yours, it does work. But if I then open my other workbook and have it running in the background, the NEW workbook no longer works. It seems my original workbook is haunted.. If I close my old workbook, the new one works again.

The "symptoms" are the same as before. When my old workbook is open in the background, the new workbook, which is identical to yours, doesn't work if I use the drop-down menu. But it does work when I type it in manually.

Any ideas what kind of settings etc in the old workbook could cause this I have searched the old workbook, and I don't have another "worksheet_change" function hidden anywhere.

Emma
bohiti

I am having the same problem. I don't know how to fix it. Should I put application.volatile(false) Is there another way to solve this I am trying to clear a range. For example range("A1").clearcontents. The event is reconginzed but the code doesn't perform.

Any help will be appreciated.
Dan Lingman
Btw, if you can think of another method to use to accomplish the same thing, please let me know. I just can't seem to get around this problem. I believe there must be a bug in Excel or VBA somewhere, because this problem just doesn't make sense. :(

Thank you for all your help.

Emma
Alexander Rust

Chas,

Thank you very much for you help.

I simplified the code for you. The event is working fine (I tested with a MsgBox), but it does not clear the range. Also, the same the code is working fine in another excel version of my application. I don't know if it is a setting that should be adjusted.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = wksClient.Range("inpName_Client").Address And _

wksClient.Range("SetNewClient") = False Then

wksClient.Range("setClearInputs1").ClearContents

End If

End sub
Arthropleura
Hello Emma,

I'm not sure if I am understanding you correctly but with the following it works.

I am assuming your first column is A. Cells A1,A2,A3,A4 etc have drop down validation lists. This list is populated by an area elsewhere on the sheet.

ChangeCAS is range A1..A10 or so.

What is the value of Target when you enter the function

Chas
SDerix
Emma,

This sheet is attached to the email which I returned to your email address

Chas
Hisham Jaber
Oops I think I may not have answered your question.

It doesn't matter what the value of Target is at the time I enter the function. It can be set to some value already ("country", "state", or "city"), or it can be blank. Either way, when I use the drop-down menu to change it so some other value, the message box pops up, but the value in B1 is NOT updated.

So, I have to type in the contents of A1 manually to make it work. It almost seems like a VBA/Excel bug. But you are saying that it works on your computer What version of Excel are you using I'm using Excel 2003.

Emma
Synced
Hello Emma,

Thank you for sending me your sheet.

The reason why you are having this problem is that the statement "Volatile" is, if you will excuse the pun, very "volatile"!.

The line where you write to the adjacent cell is never executed because the function is called before the line is executed and it never returns to that line.

One way around this problem is (and I am sure there are many others) is to take the application.volatile statement out and actually call the function.

I am attaching a the sheet with some changes.

Chas

PS In the function you have to turn events off or it will keep repeating itself, and then in the change event turn events back on.

Hope this has helped you and I have not changed the functionality of the function. Let me know if it okay.
Zadoras
This is so strange and I'd like to find out the cause.

Perhaps you could send me your "haunted" worksheet and I'll try and see if I can find the culprit.

My email address is charleschand@aol.com

Chas
worksheet_change and data validation

Answer this question