Data validation pull down not recognized

I am trying to start a macro (that does various things) each time data is changed in a cell (or any cell in a range of cells)

In cells a1:a3 I am using a pull down list for validation, and getting the list from an adjacent range of cells.

When I enter a value on the list manually (type it in) and then hit enter, the macros all run fine. However, when I use the pull down to enter the value (regardless of hitting enter or not), the macros do not run.

Why?

Here is the code and the workbook is attached.

[VBA]
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1:A3"
' If the Activecell is one of the key cells, call the
' Test2 macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub

Sub KeyCellsChanged()
'performs the data comparison and format changes to reflect changes
'in cell range a1:a3
'Result: appears to work just fine
Dim Cell As Object
Dim r, rprev, cprev, c, ccompr, rcompr As Integer

'Starting counter number for previous name dataset,
'corresponding cell row
rcompr = 7
'Starting counter number for previous name dataset,
'corresponding cell column
ccompr = 6
' If none of the names in A1:C1 are "NO SERVER"
For Each Cell In Range("A1:A3")
If Cell <> "NO SERVER" Then
'Find the cell with the matching name and store
'the row number as r
r = Worksheets("Sheet1").Columns(3).Find(Cell).Row
'Find the cell with the matching name and store
'the column number as c
c = Worksheets("Sheet1").Columns(3).Find(Cell).Column

'If the new name and old name are not equal...
If Worksheets("sheet1").Cells(rcompr, ccompr).Value <> Cell Then
'find the row number of the matching previous name
'and store as rprev
rprev = Worksheets("Sheet1").Columns(3).Find(Worksheets("sheet1").Cells(rcompr, ccompr).Value).Row
'find the column number of the matching previous name
'and store as cprev
cprev = Worksheets("Sheet1").Columns(3).Find(Worksheets("sheet1").Cells(rcompr, ccompr).Value).Column
'turn the old name to color none
Worksheets("Sheet1").Cells(rprev, cprev).Interior.ColorIndex = xlNone
'turn the new name to color 3 on the color chart
Worksheets("Sheet1").Cells(r, c).Interior.ColorIndex = 3
'Store the new name in the correpsonding position of previous values
Worksheets("sheet1").Cells(rcompr, ccompr).Value = Cell
'add one to old name list column number
ccompr = ccompr + 1
End If
End If
Next Cell
End Sub
[/VBA]

What version of Excel?

Excel 2003

Does the version really make a difference?

Why aren't you using the sheet1_selection_change or sheet1_change event ?

Charlize

Mainly, because I didnt know about those. Where can I find info on the syntax for that? Perhaps you might give me an example? I only want it to run if any one of a certain range of cells changes. Also, I am not sure this would fix the problem. It recognizes changes in the cell when you type it in and hit enter. It does not recognize the change any other way however. I just tried entering a value and then using the mouse and arrow keys to leave the cell, and the macros do not initiate. So it appears that it requires the user to enter the data manually, and then hit the enter key. Any other method of entering dat into the cell does not trigger the macro sequence. So I am not sure if using the event you mention will make any difference. I am willing to give it a try of course.

Hi Speedracer,
Please use the VBA tags when you post code. Just select your code and click the VBA button
Regards
MD

So I guess nobody could answer this question huh?

I noticed nobody had an answer to my problem.

Oh Crud.

I am posting another question I am guessing there wont be a solution for...

If you use the Sheet Change event this works - see the attached sample. When you go into the VBE window, double-click on Sheet1 in the Project Explorer and you'll see the events.

Quote:

---

Originally Posted by geekgirlau

If you use the Sheet Change event this works - see the attached sample. When you go into the VBE window, double-click on Sheet1 in the Project Explorer and you'll see the events.

---

It looks like you added some other things here that are alien to me. I am an admitted rookie here. Learning quickly and my methods are sometimes crude, but please tell me all of the changes you made. If its not a terrible bother, please tell me what you did and why...etc. Pretend I know nothing at all.

How did you delete my other modules. You can probably see I was learning through experimentation there as well. I dont see any way to delete an entire module. I guess there are all kinds of things I have to learn.

Thanks for the help. I seem to be running into one frustration after another.

SRM

Hi SpeedRacer,

Actually I didn't do a great deal.

New procedure:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
KeyCellsChanged
End If
End Sub[/vba]

This code automatically runs when you change cells on Sheet1. The procedure itself is very similar to what you already had, where it checks to see which cell has changed. The "Target" parameter gives you the range that was changed automatically, so you just need to check that the range is within A1:A3.

Deleted Procedures:
"Auto_Open" and "DidCellsChange" - these are no longer required, as the Sheet Change event handles this. To delete a procedure or function, you can just select the text and press Delete - it's just treated as text in the VBE window. If you wanted to delete an entire module, right-click the module in the Project Explorer on the left and select "Remove".

Other Changes:
The only other change is within the "KeyCellsChanged" procedure. At the top I've added
[vba]Application.EnableEvents = False[/vba]

and at the bottom of the code

[vba]Application.EnableEvents = True[/vba]

Because your code changes cells on Sheet1, this would normally trigger the change event again. By setting EnableEvents to false, any changes your code makes don't trigger the Sheet1 macro until your code has finished.

Quote:

---

Originally Posted by geekgirlau

Hi SpeedRacer,

Actually I didn't do a great deal.

New procedure:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
KeyCellsChanged
End If
End Sub[/vba]

This code automatically runs when you change cells on Sheet1. The procedure itself is very similar to what you already had, where it checks to see which cell has changed. The "Target" parameter gives you the range that was changed automatically, so you just need to check that the range is within A1:A3.

Deleted Procedures:
"Auto_Open" and "DidCellsChange" - these are no longer required, as the Sheet Change event handles this. To delete a procedure or function, you can just select the text and press Delete - it's just treated as text in the VBE window. If you wanted to delete an entire module, right-click the module in the Project Explorer on the left and select "Remove".

Other Changes:
The only other change is within the "KeyCellsChanged" procedure. At the top I've added
[vba]Application.EnableEvents = False[/vba]

and at the bottom of the code

[vba]Application.EnableEvents = True[/vba]

Because your code changes cells on Sheet1, this would normally trigger the change event again. By setting EnableEvents to false, any changes your code makes don't trigger the Sheet1 macro until your code has finished.

---

Thanks

That does acheive the problem I was looking to solve. There is still a little bug when you change the value in A3. It doesnt behave the same way as when you change A1 and A2. But I can run that down no problem.

I do have one other rookie question: What does the "option explicit" part do that is in testbook2b.xls?

Just curious.

SRM

Option Explicit forces you to declare variables. Typos etc. will be identified when you try to run the code.

Thanks again for all your help, but I have run into a snag. I was simply trying to apply the previous problem you helped me with to a little bit more detailed workbook. I was using the simple version we were discussing to test a way to do what I wanted. Since it worked, I copied the code, and then changed the according references (sheets, ranges...etc.)

Now when I run it I get a "runtime error 91" object variable not defined error when I go to set the rprev value. I noticed that the find method would not work (giving me the same error), even though I could see that the value to be found was there. I found this odd. As it turned out, the list source for the cells in the c3:c22 in Floorplan was referring the ServerTallies sheet. It didn't recognize the value when it went to look in the F row in Floorplan, even though it was there! Once the list source was changed to the same sheet, it had no problem. Why would this be, if the values are the same, why should it matter? I dont get it.

Anyway...I am getting the runtime 91 error when it hits the rprev now. I dont get it. Does the value to find have to be an object? I am confused.

This code is in module 6 in the attached workbook.

[VBA]For Each Cell In Range("C3:C22")
If Cell <> "NO SERVER" Then
'Find the cell with the matching name and store
'the row number as r
r = Worksheets("FloorPlan").Columns("F").Find(Cell).Row
'Find the cell with the matching name and store
'the column number as c
c = Worksheets("FloorPlan").Columns("F").Find(Cell).Column

'If the new name and old name are not equal...
If Worksheets("datatest").Cells(rcompr, ccompr).Value <> Cell Then
'find the row number of the matching previous name
'and store as rprev
rprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest).Cells(rcomp r,ccompr)).Row
'find the column number of the matching previous name
'and store as cprev
cprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr)).Column
'turn the old name to color none
Worksheets("FloorPlan").Cells(rprev, cprev).Interior.ColorIndex = xlNone
'turn the new name to color 3 on the color chart
Worksheets("FloorPlan").Cells(r, c).Interior.ColorIndex = 3
'Store the new name in the correpsonding position of previous values
Worksheets("datatest").Cells(rcompr, ccompr).Value = Cell
'add one to old name list column number

End If
rcompr = rcompr + 1
End If
Next Cell

Application.EnableEvents = True
End Sub[/VBA]

Thanks again...I really want to get this finished up.

SRM

Try Cell.Value as in
[vba]r = Worksheets("FloorPlan").Columns("F").Find(Cell.Value).Row[/vba]
also, you might want to set your LookIn parameter.

Try
[VBA]Option Explicit

Sub KeyCellsChanged()
'performs the data comparison and format changes to reflect changes
'in cell range a1:a3
'Result: appears to work just fine

Dim Cell, testvar1, testvar2 As Object
Dim r, rprev, cprev, c, ccompr, rcompr

Application.EnableEvents = False

'Starting counter number for previous name dataset,
'corresponding cell row
rcompr = 2
'Starting counter number for previous name dataset,
'corresponding cell column
ccompr = 2

' If none of the names in A1:C1 are "NO SERVER"
For Each Cell In Range("C3:C22")
If Cell <> "NO SERVER" Then
'Find the cell with the matching name and store
'the row number as r
r = Worksheets("FloorPlan").Columns("F").Find(Cell).Row
'Find the cell with the matching name and store
'the column number as c
c = Worksheets("FloorPlan").Columns("F").Find(Cell).Column

'If the new name and old name are not equal...
If Worksheets("datatest").Cells(rcompr, ccompr).Value <> Cell Then
'find the row number of the matching previous name
'and store as rprev
rprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr), LookIn:=xlValues).Row
'find the column number of the matching previous name
'and store as cprev
cprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr), LookIn:=xlValues).Column
'turn the old name to color none
Worksheets("FloorPlan").Cells(rprev, cprev).Interior.ColorIndex = xlNone
'turn the new name to color 3 on the color chart
Worksheets("FloorPlan").Cells(r, c).Interior.ColorIndex = 3
'Store the new name in the correpsonding position of previous values
Worksheets("datatest").Cells(rcompr, ccompr).Value = Cell
'add one to old name list column number

End If
rcompr = rcompr + 1
End If
Next Cell

Application.EnableEvents = True
End Sub
[/VBA]

BTW
[VBA]Dim Cell, testvar1, testvar2 As Object[/VBA]
does not dim each item. You must dim each in full as in
[VBA]Dim Cell as Range, testvar1 as Object, testvar2 As Object[/VBA]

Quote:

---

Originally Posted by mdmackillop

Option Explicit forces you to declare variables. Typos etc. will be identified when you try to run the code.

---

r is working fine. Its rprev and cprev that I cant get to work.

I just tried this, and its not working either.

[VBA]rprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr).Value).Row[/VBA]

So I am baffled. Oh yeah, and "Cell" works just fine without the ".value" part.

Does the find item need ot be an object, or can it be an expression or a reference? What's the criteria?

Thanks

SRM

I also changed these lines
[VBA]
rprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr), LookIn:=xlValues).Row
'find the column number of the matching previous name
'and store as cprev
cprev = Worksheets("FloorPlan").Columns("F").Find(Worksheets("datatest").Cells(rcom pr, ccompr), LookIn:=xlValues).Column

[/VBA]

This whole Excel VBA thing really has me frustrated. I just re-opened this workbook to make the changes you suggested (and thanks again by the way) and it worked just fine as is. Amazing. I ran it a dozen times. Worked great. I just dont get it. I made the changes you gave me anyway, as they worked too, and I trust your experience.

It just seems odd to me that it seems like a fairly inexact science. I guess I have thought of programming to be purely logical. I keep running into things that dont seem logical. One minute it works, and doesnt the next, with no apparent changes made on my part.

Thanks again though. It appears to be working fine now. I need to know how how to analyze eror messages though to find the root of the problem and then create a solution.

SRM

Worksheets("datatest").Cells(rcompr, ccompr) is really a range, not the contents of the range, so Excel is defaulting to some preset paramater. This may not always be the correct one for your purpose. Adding, .Value, .Text or even .Formula removes the ambiguity. Similarly for the cells you are searching, are you looking in the Formula or Result.
Check out the parameters used by Find in the Help file. First get used to using them until you fully understand what is being done, then you might consider removing them to simplify/shorten your code.