+ Reply to Thread
Results 1 to 8 of 8

Dropdown List value triggers macro.

Hybrid View

TJ Saulnier Dropdown List value triggers... 05-09-2013, 10:38 AM
Andy Pope Re: Dropdown List value... 05-09-2013, 10:47 AM
TJ Saulnier Re: Dropdown List value... 05-09-2013, 11:03 AM
MickG Re: Dropdown List value... 05-09-2013, 11:13 AM
MickG Re: Dropdown List value... 05-09-2013, 10:56 AM
TJ Saulnier Re: Dropdown List value... 05-09-2013, 11:07 AM
TJ Saulnier Re: Dropdown List value... 05-09-2013, 11:25 AM
MickG Re: Dropdown List value... 05-09-2013, 11:28 AM
  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    Moncton, NB, Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Dropdown List value triggers macro.

    I have a dropdown list at c19 with 4 options. I'm attempting to trigger a msgbox to prompt the user for a secondary piece of data if option 2 is selected.

    the following code snippit is what I'm attempting to modify to meet my needs but it doesn't seem to be working:

    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 = "$C$19" 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 Get_WO_NUM
            Case Is = Rng.Cells(3, 1)   'Third Drop Down Item
              Call MacroC
            Case Is = Rng.Cells(4, 1)   'Fourth Drop Down Item
              Call MacroD
              
          End Select
        End If
        
    End Sub
    Any assistance would be appreciated. When I pick an item from the list I get a compile error sub or function not defined.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Dropdown List value triggers macro.

    The obvious question then is do the macros MarcoA, MacroC, MarcoD and Get_WO_NUM exist?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-16-2012
    Location
    Moncton, NB, Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Dropdown List value triggers macro.

    I'm Sorry .. I did create the macros however they don't have any code in them as yet ... so the following code is in module1
    Sub Get_WO_NUM()
    
        Range("C47").Value = InputBox( "Please Provide the Work Order #")
            
    End Sub
    Sub MacroA()
    
    End Sub
    Sub MacroC()
    
    End Sub
    Sub MacroD()
    
    End Sub
    the goal is simply to prompt the user for a value and place it in C47 if they select option 2 from the list.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Dropdown List value triggers macro.

    Change the code as shown in red:-
    Private Sub Worksheet_Change(ByVal Target As Range)
    
      Dim Addx As String
      Dim Rng As Range
        
        
       If Target.Address = "$C$19" Then
          'The two lines below are moved below the line above, Or you code will run agin when the Input Box value is inserted.
         Addx = Target.Validation.Formula1
            Set Rng = Range(Right(Addx, Len(Addx) - 1))      
          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 Get_WO_NUM
            Case Is = Rng.Cells(3, 1)   'Third Drop Down Item
              Call MacroC
            Case Is = Rng.Cells(4, 1)   'Fourth Drop Down Item
              Call MacroD
              
          End Select
        End If
      End Sub
    
    Sub Get_WO_NUM()
    Range("C47").Value = Application.InputBox("Please Provide the Work Order #")
    End Sub

  5. #5
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Dropdown List value triggers macro.

    It works for me.
    As it says "Sub Or function not Defined" it would point to one of those Subs (Call ---) not existing , Are they ????

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    Moncton, NB, Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Dropdown List value triggers macro.

    As you've likely guessed I'm not a programmer .. having created the empty macros as defined above it now errors out on this line

    Set Rng = Range(Right(Addx, Len(Addx) - 1))
    Method 'Range' of object'_Worksheet' failed

    is the error message.
    Last edited by TJ Saulnier; 05-09-2013 at 11:16 AM.

  7. #7
    Registered User
    Join Date
    03-16-2012
    Location
    Moncton, NB, Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Dropdown List value triggers macro.

    That's going to do it. It works fine now and I very much appreciate the assistance MickG. I had to make a small adjustment to the data validation for the dropdown but it works perfectly ... thanks again and have a great day.

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Dropdown List value triggers macro.

    Your welcome
    Regrds Mick

+ 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