none
Running VBA code from a drop down menu RRS feed

  • Question

  • Hi all,

    I've been trying to run a code if a select an item from a drop down menu in Excel and it's been a nightmare. Mind that I'm very new at this so bear with me.

    Basically, what I want to do is something along these lines:

    Suppose that in the drop down menu there Option 1 and Option 2 listed. If I select Option 1, then it will do something like:

    CellA3 = CellA1-CellA2

    If I select Option 2, then it will do:

    CellA3 = CellA1+CellA2

    I know it probably may seem really stupid, but I'd really appreciate if someone could help me.

    Thanks in advance to whoever answers

    Felipe

    Friday, March 14, 2014 1:21 AM

All replies

  • Try the following code. Because you say you are new to VBA, to install this code it goes into the worksheets module.

    Right click worksheet tab name and select View code. (this should open the VBA editor)

    Copy the code below and paste into the editor.

    Save as Macro Enabled workbook.

    Ensure you have enabled macros. See Help for how to do this. Setting to "Disable all macros with notification should be OK"

    Note the comments. (Comments are in green and prefixed with a single quote. These lines are not code but explanation to the user.)

    Do not rename the Sub otherwise it will not work. It is called Event code which runs when a change takes place on the worksheet.

    Private Sub Worksheet_Change(ByVal Target As Range)

        On Error GoTo ReEnableEvents
        Application.EnableEvents = False
       
        Dim strAddress As String
       
        strAddress = "C1"   'Edit "C1" to the cell with the dropdown
       
        'The If statement below only allows the sub to progress if the
        'Target is cell C1 (or whatever cell address is in strAddress variable).
        If Target.Address(0, 0) = strAddress Then
            Select Case Target.Value
                Case "Option 1"
                    Range("A3") = Range("A1") - Range("A2")
               
                Case "Option 2"
                    Range("A3") = Range("A1") + Range("A2")
                   
                'Add additional case options here for say "Option 3", "Option 4" etc
               
                Case Else       'Case Else is optional
                    'Use Case Else if selected value does not match any of the required options.
            End Select
        End If
       
    ReEnableEvents:
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    Friday, March 14, 2014 7:42 AM
  • Hi again OssieMac,

    Sorry to bother you again. When I tried just copy and pasting your code, it worked fine, but when I inserted all the calculations I'd like to do in the worksheet, it gives me an error when I try to run it saying that there was a compilation error 'Sub' or 'Function' not defined.

    This is how I have it:

                

    Private Sub Worksheet_Change(ByVal Target As Range)

        On Error GoTo ReEnableEvents
        Application.EnableEvents = False

        Dim strAddress As String

        strAddress = "H22"

        If Target.Address(0, 0) = strAddress Then
            Select Case Target.Value
                Case "Contracorrente com fluido frio no anel"
                    Range("A3") = Range("A1") - Range("A2")
                    'Cálculo dos grupos adimensionais.
                    Range("C27") = (Range("D6") ^ 2 - Range("D12") ^ 2) / Range("D12") 'Deq.
                    Range("C28") = Range("K8") / Range("I18") 'Fluxo massico anel (fluido frio).
                    Range("D28") = Range("J8") / Range("I19") 'Fluxo massico tubo (fluido quente).
                    Range("C29") = Range("C28") * Range("C27") / Range("Q8") 'Reynolds anel (fluido frio).
                    Range("D29") = Range("D28") * Range("D10") / Range("P8") 'Reynolds tubo (fluido quente).
                    Range("C30") = Range("Q10") * Range("Q8") / Range("Q9") 'Prandtl anel (fluido frio).
                    Range("D30") = Range("P10") * Range("P8") / Range("P9") 'Prandlt tubo (fluido quente).

             End Select
        End If
        
    ReEnableEvents:
        Application.EnableEvents = True
    End Sub

    Could you please help me again? Thanks a lot :)

    Rgds

    Friday, March 14, 2014 6:45 PM
  • I cannot see anything in this particular sub that would return that error. I have tested the code with dummy data and it works fine. The error refers to attempting to call another sub or a function that does not exist.

    When you first run code in a workbook, all code can be compiled and I wonder if you have other code in the workbook that contains an error.

    I suggest that you compile the code from within the VBA editor. To do this, Select menu item Debug -> Compile VBAproject. If Compile VBAproject is disabled. (ie greyed out) then make a dummy change to the code like adding a blank line somewhere and try again. It should halt on the compile error so that you can identify it.

    Some further tips that might help you:

     

    Application.EnableEvents = False is used to stop recursive calls to the event each time the code makes a change on the worksheet which occurs when each of your formulas updates a cell.

    On Error GoTo ReEnableEvents is used so that events do not remain disabled if an error occurs and stops the code before it gets to the line to turn the events on again. Events will remain off until either restart Excel or use code to turn them on again.

    The disadvantage of "On Error GoTo" in the code as I posted it is that you would not know that an error has occurred. Therefore the code should test for errors and post a message accordingly. See my amended code below. With the type of calculations you are making in the code, it is possible to get an error and you would not know. (One such error example is divide by zero).

    I keep a small sub like the following in the workbook and run it from the VBA Editor to Re-enable events because when looking for problems in the code I comment out the line "Application.EnableEvents = False" so that the code will stop on the error. To run the code from within the VBA editor, position the cursor anywhere within the sub and press F5. (alternatively use the Run menu item)

    Sub ReSetEvents()

     Application.EnableEvents = True

    End Sub

    Amended code for your application to identify if an error has occurred. (See change near the end after the label ReEnableEvents)

    Private Sub Worksheet_Change(ByVal Target As Range)

        On Error GoTo ReEnableEvents
        Application.EnableEvents = False

        Dim strAddress As String

        strAddress = "H22"

        If Target.Address(0, 0) = strAddress Then
            Select Case Target.Value
                Case "Contracorrente com fluido frio no anel"
                    Range("A3") = Range("A1") - Range("A2")
                    'Cálculo dos grupos adimensionais.
                    Range("C27") = (Range("D6") ^ 2 - Range("D12") ^ 2) / Range("D12") 'Deq.
                    Range("C28") = Range("K8") / Range("I18") 'Fluxo massico anel (fluido frio).
                    Range("D28") = Range("J8") / Range("I19") 'Fluxo massico tubo (fluido quente).
                    Range("C29") = Range("C28") * Range("C27") / Range("Q8") 'Reynolds anel (fluido frio).
                    Range("D29") = Range("D28") * Range("D10") / Range("P8") 'Reynolds tubo (fluido quente).
                    Range("C30") = Range("Q10") * Range("Q8") / Range("Q9") 'Prandtl anel (fluido frio).
                    Range("D30") = Range("P10") * Range("P8") / Range("P9") 'Prandlt tubo (fluido quente).
             End Select
            
        End If
       
    ReEnableEvents:
        If Err.Number > 0 Then
            MsgBox "Error! " & Err.Description, vbCritical
        End If
        Application.EnableEvents = True
    End Sub


    Regards, OssieMac

    Friday, March 14, 2014 11:22 PM
  • Hi OssieMac,

    Still with problems. I copy and pasted the amended code you gave and when I run it, is gives an error saying there are incompatible types. I really don't know what's happening. Could you please take a look at the Excel files in my Dropbox link below?

    https://www.dropbox.com/sh/t6isu1cu7d5wpgf/9peql401en

    I'm a chemical engineering student and this is basically a worksheet to design a double-pipe heat exchanger. the idea of the dropdown menu is to select what combination of flow and fluid localization I want (e.g. countercurrent with cold fluid in the ring).

    Hope you can help me again. I'm completely lost.

    Thanks again in advance and best wishes,

    f.camposleite

    Sunday, March 16, 2014 4:44 PM
  • During testing you need to comment out the line On Error Goto ReEnableEvents so that the code will stop on the offending line. However, each time the code stops you need to run code to re-enable events otherwise the event will not run next time until after you re-start Excel.

    Code to re-enable events. (You can simply leave the code in the workbook directly below the sub you are testing and to run it when ever required. Just click anywhere in the sub and press F5. There is no need to delete it; just keep it to use during future testing.)

    Sub ReEnableEvents()
        Application.EnableEvents = True
    End Sub

    Alternatively you can use Ctrl / G to open the Immediate window and just type Application.EnableEvents = True and press return.

    If you comment out the On Error line then you will see that the code stops on the following line because Ln is a worksheet function and the code needs to include WorksheetFunction.Ln. Check the brackets in my edited line and ensure they are correct because I thought you had extra brackets that were not required.

    Your line and my edited line below.

    'Cálculo de MLDT.
    'Range("B46") = ((Range("J6") - Range("K7")) - (Range("J7") - Range("K6"))) / (Ln((Range("J6") - Range("K7")) / (Range("J7") - Range("K6")))) 'MLDT contracorrente.

    Range("B46") = (Range("J6") - Range("K7") - Range("J7") - Range("K6")) / (WorksheetFunction.Ln(Range("J6") - Range("K7"))) / (Range("J7") - Range("K6")) 'MLDT contracorrente.

    You also have the following line of code immediately after the Case statement that has been left over from my original example and I don't believe it is now required and it is attempting to performs maths on text cells.

    Range("A3") = Range("A1") - Range("A2")

    After that you have a division by zero error that you will need to fix. Either find out why the zero is there. You can use an if statement to test for the zero before performing the division and skip if dividing by zero. I can't fix because I don't know enough about why the zero exists.

    Continue to test until all errors that you can identify are fixed and then uncomment the On Error GoTo line for the production code just in case another error shows up due to incorrect data at a later date when you will again need to comment out the line to identify the offending line of code.


    Regards, OssieMac

    Monday, March 17, 2014 1:15 AM