Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Helllllllllpppp Problem with macros and lists.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please can someone help me.

    I have created a list of 14 things using data validation/ lists, but now I want to asign a macro to each of the 14 options, so that when I select an option from my drop down list a macro will automatically run, and if I chose another option, another macro would run. Perhaps I need a different way of creating my list? Any advice would be greatly appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well hello,

    Try this, right click on the sheet tab that your answer appears on, and select View Code. Now paste this in:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    If Worksheets("sheet1").Range("b1").Value = "a" Then
    Run ("whateveryourmacroiscalled")
    End If

    End Sub

    This code says that whenever "a" appears in B1 of Sheet 1 then it runs the macro. So you can just tailor this to suit your requirements

    Hope it helps

    Audiojoe
    Whisky and loving, and wild, wild women

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Audiojoe, THANKYOU you are a life saver, I can stop banging my head against this brick wall now!

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Me again......

    The code seems to be running in a loop. How do I get it to only do the code once. It must be to do with getting it to count or something... My visual basic is not good!

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You would probably need to change the first line to: -

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    so it doesn't keep running.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It doesn't seem to run at all now....

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it still in break in VBA? Try going in and pressing the stop button, then try it again

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nope... I've exited VBA and it still doesn't run. Can I not use an if, then, elseif? to say if the result is already there then dont continue to do it? (if so so how I do that, my attempt didnt work!)

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Diane, Audiojoe's original code would run whenever you changed cells. My amendment will only run if you actually change the value of a cell. Try changing it to something different, exiting the cell, then change it back to the value which would run your macro.

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guys,

    Thanks for your help, It is still not working properly (no doubt my ineptness rather than your advice!) What happens now is that if I change my option in the drop down menu, nothing happens, but if I change of of the parameters which feed into my macro, then the code works, but continuosly, whether I have the selection in the first line or not! I have copied exactly what I have writen below in case it helps!

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Worksheets("E. Surrey (Peak)").Range("o9").Value = "Triangular" Then
    Run ("macro2")
    End If
    End Sub

    MACRO 2

    Range("O17").Select
    ActiveCell.FormulaR1C1 = "=RiskTriang(R[-7]C,R[-6]C,R[-5]C)"
    Range("O18").Select
    End Sub


    (the risktriang comes from @risk, excel add in which does monte carlo analysis)

    Cheers!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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