VBA on cell value change not working as required

[DWNewmac]

Hello,

I think this is probably a very simple solution for all those with a little VBA knowledge (which obviously rules me out). Any help would be much appreciated....

I'm setting up an orderbook, much of which requires data entry (until a more appropriate solution is in place).

In the meantime, what I'd like to happen is that when a name is selected (from a dropdown) in cell Q4 that a lookup formula automatically runs in cells U4 and W4 to return data from to columns of a table. I've put the following code in place but it doesn't allow me to go back into Q4 as the macro runs each time I select the cell.

Effectively, I want the user to be able to go back into the cell as required and for the macro only to run if the value entered in the cell changes (be this from blank to an entry or from one entry to another).

I suspect this code could be shortened down somewhat, but here is what I have so far.....

Thanks in advance

David

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$4" Then
Call getdata


End If
End Sub

Sub getdata()

Range("U4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,2,FALSE),"""")"

Range("W4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,3,FALSE),"""")"

Range("U4").Select
Selection.Copy
Range("U4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("W4").Select
Selection.Copy
Range("W4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("S4").Select

End Sub

 

[DWNewmac]

Apologies, Peter....

Worksheet_Change code is in Sheet1 (Input)
'getdata' acts on a change to cells Q4 and Y4 which are also both in Sheet1 (Input).

Sheet2 (Validation) simply holds various tables / lists that are used for validation lists etc. in sheet1

Hope this clarifies but let me know if not.

Thanks

David

 

[Peter_SSs]

Hope this clarifies

It does, thanks. However, I have not been able to reproduce the sorts of issues that you are describing.
Are you able to upload (eg DropBox, OneDrive etc) a copy of the workbook (with any sensitive data removed or disguised) & provide a link here?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DWNewmac]

Thanks Peter.

You should be able to access this link to the file: Client Order Book (Copy).xlsm

 

[Peter_SSs]

Thanks Peter.

You should be able to access this link to the file: Client Order Book (Copy).xlsm

The code in that workbook is working for me - but are you sure you want the Worksheet_Change code to be looking at Q4 and Y4 on 'Input' and not perhaps Q5 and W5? (or even Q3 and W3)
Row 4 is tiny and doesn't appear to have anything in it and certainly not a drop-down that you hinted at in post #7

 

[DWNewmac]

Argh!! You've spotted the now glaringly obvious.

I'd inserted a row and not updated the code!!!

Apologies, Peter and thanks so much for your help.

David

 

[Peter_SSs]

You're welcome. Glad we got it sorted.