+ Reply to Thread
Results 1 to 6 of 6

Dropdown displays different value than what was shown in list?

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Dropdown displays different value than what was shown in list?

    Hello and happy Monday!

    I am trying to create a dropdown menu that will display different information than what is listed. As in, in the dropdown if I select "0-100 Units" I would like the cell to display "199" for the price.

    The problem is that the information is on a different sheet. I have seen in some threads people do a dropdown that displays different information than what is outputted into the cell, but I can't seem to get it to work from a different sheet. I want it to run automatically when the drop down value is selected. Below you can find the code I am using; right now it fails at the line in red. I have also uploaded a test sheet. Any help would be greatly appreciated

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,058

    Re: Dropdown displays different value than what was shown in list?

    You don’t need to select anything here:

    Please Login or Register  to view this content.
    Rory
    I drink, and I know things

  3. #3
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Dropdown displays different value than what was shown in list?

    Hi Rory,

    When I run this macro in the test book, the value "199" appears and quickly disappears from the cell. Do you know why this is happening?

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,058

    Re: Dropdown displays different value than what was shown in list?

    I can’t test currently as I’m on an iPad but will have a look shortly when I’m in the office. We should however be testing the changed cell and disabling events:

    Please Login or Register  to view this content.
    Last edited by rorya; 03-25-2019 at 03:33 AM.

  5. #5
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Dropdown displays different value than what was shown in list?

    the original code is severly overcomplicated and also suffers from the all so common "VBA select disease".. no less then 7 variables have been setup to find 1 simple input value in a fixed range (C4) in another fixed range of 8 cells (A4:B7) on sheet2

    Just cut all that cr*p and limit the code to do what it needs to.. variables are great if you need them several times in the code or to store an intermediate result to test (like foundCell here)

    Please Login or Register  to view this content.
    Explanation of the code.
    Target is the name used in event macro's for the Activecell or the cell that triggers the event and often also the cell that is input or cell to be tested.

    If Target.Address = "$C$4" Then
    This line makes sure the code is only excecuted if the changed cell is C4 on the worksheet with this eventcode

    if that is the case then

    Set foundCell = Sheets("Sheet2").Range("A4:B7").Find(What:=Target.Value)
    This will find the selected value of the dropdown and store it in variable foundCell.
    You need to Set the variable to use it as (sort of a) named range so you can navigate to the wanted cell (here 1 column to the right later on)


    If Not foundCell Is Nothing Then
    This will test if a result was found, if no result is found the code skips next lines end goes to end sub
    The main purpose is to avoid the code ending in error if for some reason the search value is not found or if value in C4 is removed

    Application.EnableEvents = False
    Will disable triggering the event code due to a cell change. This line is required here because we change the value of C4 next and that cell triggers this macro. So it would then excecute again. (this is why in the first code example of rorya the value showed up and disappeared again, the eventmacro was triggered again by the change made by the code)

    Range("C4").Value = foundCell.Offset(0, 1).Value
    the most important line, this actually changes the cell C4 to the wanted result.. by moving (offsetting) 1 column to the right from the foundCell-range

    Application.EnableEvents = True
    this turns event macros back on to make sure next time C4 is changed the eventcode is excecuted properly

    hope that this explains the code and shows why you should not always use variables in simple code that does 1 simple thing.
    Keep it simple
    Last edited by Roel Jongman; 03-25-2019 at 04:23 AM.

  6. #6
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Dropdown displays different value than what was shown in list?

    Dear Roel Jongman,

    Thank you so much for the in-depth explanation. I really appreciate learning the processes of how the macros work; it is much more useful for the future if I understand the process behind it. The code executed perfectly in the test! I will try it with my original workbook and see if it is the same.

    Have a great day!

    UPDATE: it worked perfectly!! Thank you!!
    Last edited by whahmira; 03-25-2019 at 05:33 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 02-05-2016, 11:09 AM
  2. Replies: 11
    Last Post: 07-11-2015, 07:06 AM
  3. Urgent: Dropdown Cell Validation Data is shown next to each other
    By Klaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2014, 05:52 PM
  4. Replies: 9
    Last Post: 11-22-2013, 12:35 PM
  5. Replies: 12
    Last Post: 07-08-2013, 09:33 AM
  6. [SOLVED] Particular items are shown based on a dropdown selection
    By mathieuv in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-28-2013, 11:47 AM
  7. I want to change the series shown in a chart by using a dropdown b
    By Jamie in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-23-2006, 06:25 PM

Tags for this Thread

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