Consulting

Results 1 to 19 of 19

Thread: Data validation pull down not recognized

  1. #1
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location

    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]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    What version of Excel?

  3. #3
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    Excel 2003

    Does the version really make a difference?

  4. #4
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Why aren't you using the sheet1_selection_change or sheet1_change event ?

    Charlize

  5. #5
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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.

  6. #6
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Speedracer,
    Please use the VBA tags when you post code. Just select your code and click the VBA button
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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...

  8. #8
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  9. #9
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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

  10. #10
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  11. #11
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Option Explicit forces you to declare variables. Typos etc. will be identified when you try to run the code.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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

  14. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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

  17. #17
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  18. #18
    VBAX Regular
    Joined
    Dec 2006
    Location
    Orlando, FL
    Posts
    39
    Location
    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

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •