Calling macro from change in Data Validation List

**DavidBW** · 01-11-2013, 06:48 AM

Hello

could someone please advise why the following macro does not work. It is supposed to run macro FindLocation_New when I change the selection in cell D4.

PHP Code:


Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim FindLocation_New As String 
     
    Application.EnableEvents = True 
      
    If Target.Address = "$d$4" Then 
    Application.Run FindLocation_New 
    End If 
      
End Sub

Thank you

**OllieB** · 01-11-2013, 07:23 AM

David,

a couple of possibilities:
- you are updating a range of cells whereby D4 is not the first cell (upper left) in the range, hence the address of Target would not equal $d$4
- you are using selection_change are you sure you do not mean to trigger this routine when the value changes?

and last

the address returned by Target.Address is in uppercase so you should compare against $D$4

**DavidBW** · 01-11-2013, 07:38 AM

Thanks Ollie

I changed the target to D4 which is a positive step as the rountine now goes further but now I get the message - Run-time error 1004, the macro " cannot be found.
I think it's to do with your second point whereby I just want to trigger the rountine i.e. run the same macro irrespective of the selection in D4. The FindLocation_New macro simply uses the value in D4 to generate different results.
Please advise how I can make the necessary change.

**OllieB** · 01-11-2013, 07:46 AM

David,
In order for the macro to be found it needs to:
- exist, and
- be declared as public

So "the first line of your macro needs to read 'Public Sub FindLocation_New' (obviously without the quotes)

I am not entirely clear on when you want to execute the macro:
- when the users makes a change to the value in D4?
- when the user selected cell D4?
- other?

**DavidBW** · 01-11-2013, 08:07 AM

Ollie
- The macro exists
- I have now made it public but it is still not being found (I tried Worksheet_SelectionChange but still no luck)
- the macro needs to execute when the user changes the value in D4
thanks

**DavidBW** · 01-11-2013, 08:36 AM

Ok after a bit of trial & error the following very simple code seems to work

HTML Code:

Thanks for your help!

**OllieB** · 01-11-2013, 10:07 AM

You are welcome

**jep5156** · 02-25-2014, 11:43 AM

I am doing something very similar, however the code provided crashes my excel. Here is what I am doing:

1: Designed a Vlookup macro to replace cell values with the lookup value.
2: The cell value is selected via validation list dropdown.
3: When I run my vlookup by itself it works flawlessly.
4: When I run the vlookup with the code provided and print the results to a message box, it works flawlessly.
5: When I run the vlookup with the code provided and print the results to the cell it crashes.
6: When the code is finished it will update any cell that has had a drop down item selected. The dropdown is only in column K but there will be an undefined number of rows.

My other code

Please Login or Register  to view this content.

**jep5156** · 02-25-2014, 11:51 AM

Additionally:

The error that I get is "Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed

**arlu1201** · 02-28-2014, 01:43 AM

jep5156,

Welcome to the forum. 2 pointers.

1. I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

2. Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.