+ Reply to Thread
Results 1 to 9 of 9

run 1 macro automatically after any value chosen from drop down

Hybrid View

  1. #1
    Registered User
    Join Date
    05-01-2008
    Posts
    29

    run 1 macro automatically after any value chosen from drop down

    I found the code below and tried to alter it for my use but was unsuccessful. Could someone please alter it or provide new code for me?
    The code below runs 1 of 3 macros based on the item chosen from a drop down list. My needs are to run 1 macro ("SortCourse") no matter what item is chosen from the drop down list.
    Many thanks in advance. Cindy

    Private Sub Worksheet_Change(ByVal Target As Range)
    
      Dim Addx As String
      Dim Rng As Range
        
        Addx = Target.Validation.Formula1
        Set Rng = Range(Right(Addx, Len(Addx) - 1))
        
        If Target.Address = "$E$1" Then
          Select Case Target.Value
            Case Is = Rng.Cells(1, 1)   'First Drop Down Item
              Call MacroA
            Case Is = Rng.Cells(2, 1)   'Second Drop Down Item
              Call MacroB
            Case Is = Rng.Cells(3, 1)   'Third Drop Down Item
              Call MacroC
          End Select
        End If
        
    End Sub
    Last edited by VBA Noob; 05-14-2008 at 04:54 PM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "E1" Then
    Call SortCourse
    End If
    
    End Sub
    Change E1 to your cell

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    05-01-2008
    Posts
    29

    Thank you so much

    Thank you very much. That works beautifully. Sorry I didn't get the Code wrap right the first time.
    Cindy

  4. #4
    Registered User
    Join Date
    05-01-2008
    Posts
    29

    not working anymore

    Ok I'm stumped. This was working beautifully and now it's not. There were 3 sheets that were calling macros based on their pull down value. Now none of them work and the code is still there???? Nothing is happening when I choose something from the pull down. I was playing with new code to email sheets to various addresses (which I need to get help on later) - could that have done something? Any insight would be much appreciated. Thanks, Cindy

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    The other code may be a issue.

    Post a sample file (take out any info you don't want to see)

    VBA Noob

  6. #6
    Registered User
    Join Date
    05-01-2008
    Posts
    29

    file attached

    VBA Noob,
    Thanks for your help. The other code is there also that I need help with. I will post another thread if you tell me too. The other code was working also but now it isn't either. That sent me an email of the active sheet nicely. What I was trying to add was a range of people based on the drop down list results. I was trying to have it reference a range where the emails would be located and couldn't get that working.
    I removed TONS of info to get the file size down and then I still had to zip it.

    Thanks again,
    Cindy
    Last edited by VBA Noob; 05-15-2008 at 04:04 PM.

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    The sheet code for Employee has the macro named Mail_ActiveSheet

    This is not an event macro so should go in a normal module. BTW you have lots of empty modules. To get rid of an empty module

    Right click > then select remove module

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1