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

 

[Peter_SSs]

When posting code, please use code tags to preserve the formatting. See my signature block below for more info.

Instead of Worksheet_SelectionChange you should be using Worksheet_Change.

There are a few other things that could be improved too, but for a start see how this goes. remove that SelectionChange code and replace it with this & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$Q$4" Then
    Application.EnableEvents = False
    Call getdata
    Application.EnableEvents = True
  End If
End Sub

 

[DWNewmac]

Thanks very much for this, Peter. It works perfectly.

Noted regarding the posting of code.

Cheers

David

 

[Peter_SSs]

Thanks very much for this, Peter. It works perfectly.

Great.

Your 'getdata' code looks as though it was from a recorded macro. Whilst that is a great place to start, the macro recorder tends to generate very inefficient code. Actually 'selecting' cells to work with them is a slow process for vba and is almost always not necessary.

Try this for your 'getdata' instead of what you currently have.
Wasn't sure if you really needed to select S4 at the end but I left that 'select' in just in case.

Sub getdata()
    With Range("U4")
      .FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,2,FALSE),"""")"
      .Value = .Value
    End With
    With Range("W4")
      .FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,3,FALSE),"""")"
      .Value = .Value
    End With
    Range("S4").Select
End Sub

 

[DWNewmac]

That's exactly what I did (novice's approach)

Thanks for the revised code - I've added this and it works perfectly again. This will be a good starting point for understanding the differences between recorded and efficient code.

David

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[DWNewmac]

Hi Peter

Apologies for a further query...

I've tried to add to the code by including an extra step, which upon changing a value (from a list of 1-10) in cell Y4, will display just the rows where the formula in column A (A6:A28) returns 'show'.

This hasn't worked but, and I'm not sure what I've done wrong here, when I remove the additional code, the previous vba relating to Q4 and 'getdata' doesn't work?

Any further advice would be appreciated.

Thanks

David

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$Q$4" Then
    Application.EnableEvents = False
    Call getdata
    Application.EnableEvents = True
  
  ElseIf Target.Address = "$Y$4" Then
    Application.EnableEvents = False
    Call HideUnwanted
    Application.EnableEvents = True
  
  End If
End Sub

Sub getdata()
    With Range("U4")
      .FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,2,FALSE),"""")"
      .Value = .Value
    End With
    With Range("W4")
      .FormulaR1C1 = "=IFERROR(VLOOKUP(R4C17,SHOPLOOKUP,3,FALSE),"""")"
      .Value = .Value
    End With
    Range("S4").Select
End Sub

Sub HideUnwanted()

' Only show rows where the cell value in column A = Show (number to determine if show in cell Y4
    
    Worksheets("Input").Range("A6").AutoFilter _
        Field:=1, _
        Criteria1:="Show", _
        VisibleDropDown:=False

End Sub

 

[Peter_SSs]

From a quick look, the code seems okay to me. Perhaps your 'events' have become disabled. Try closing right out of Excel and reopening again.

What is the name of the worksheet that the Worksheet_Change code is in?

What is the name of the worksheet that 'getdata' is supposed to act on?

 

[DWNewmac]

Hi Peter,

I've closed and re-opened Excel but it's continuing not to work, unfortunately.

The sheets are: Sheet 1 = Input and Sheet 2 = Validation.

The 'HideUnwanted' sub works when I play that code but not via the worksheet change event but the 'getdata' sub doesn't work when I play the code or via the worksheet change event. These were previously working when you kindly provided the code so I suspect it's something that I must have changed somewhere!!

 

[Peter_SSs]

Can you please address the questions directly?

What is the name of the worksheet that the Worksheet_Change code is in?

What is the name of the worksheet that 'getdata' is supposed to act on?