Helllllllllpppp Problem with macros and lists.
Eliminate Pivot Table Annoyances
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!!

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
  •  

 

 
DMCA.com