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 | | Embirath | 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
| | ChasAA | 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
| | Embirath | 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
| | Embirath | 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). | | ChasAA | 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
| | Embirath | 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
| | Embirath | 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
| | Embirath | 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 | | ChasAA | 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
| | Embirath |
|
index > Visual Basic for Applications (VBA) > worksheet_change and data validation