VBA Enter key problem

[hatstand]

I have a cell with a validation drop down. I also have the code below that applies formatting to a range depending on the value chosen in the validation cell.

The problem is that validation enters the value. But doesn’t “Press Enter” afterwards. So the vba won’t pick up the value and run.

I’ve tried using a separate combo box, SendKeys ~, SendKeys ENTER etc but still can’t think of a way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" And Target = "Dogs" Then
Range("Row18").Select
Selection.NumberFormat = "0.00%"
End If
If Target.Address = "$B$2" And Target = "Cats" Then
Range("Row18").Select
Selection.NumberFormat = "0"
End If
End Sub

 

[Ruddles]

Changing a drop-down should trigger the event handler. Are you sure it isn't?

Insert MsgBox Target.Address in as the first executable statement and try again - make sure it reports $B$2 back at you.

Change another cell - for example A1. Does it get triggered for that?

Is the event handler in the code module for that sheet?

 

[AlphaFrog]

Changing the value in a Data Validation drop-down list does trigger the Worksheet_Change event. So your code should work. It did for me. You don't have to "Enter" in the change. Just select a value from the drop-down list.

I updated your code below. It's essentially the same as your original.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" Then
        Select Case Target.Value
            Case "Dogs":  Range("Row18").NumberFormat = "0.00%"
            Case "Cats":  Range("Row18").NumberFormat = "0"
        End Select
    End If
    
End Sub

This assumes "Row18" is a named range and not necessarily the actual row 18.

Make sure you haven't left the Event macros "Turn off". As a test, run the following macro to ensure the events are "On". Then try changing the the drop-down list.

Sub Events_On()
    Application.EnableEvents = True
End Sub

 

[hatstand]

Hi,

Thanks for the replies, much appreciated.

I tried inserting MsgBox Target.Address and it returns the msgbox, for both values. So my error must be the format side. I'll have to use something else.

 

[Ruddles]

What's "Row18"? A named range? I mean, have you actually defined a range with that name?

 

[hatstand]

Row18 isn't the best range name I've made, I have to say. The range is actually F18:HF18.

Can't understand why it won't appply the simply formatting bit of code.

 

[Ruddles]

The formatting looks okay to me. I assume the cats/dogs thing isn't your actual code though. Is it possible that Target.Value isn't ever "Cats" or "Dogs" (or whatever the real data is)?

String comparisons in VBA are case sensitive, so try:-

        Select Case UCase(Target.Value)
            Case UCase("Dogs"):  Range("Row18").NumberFormat = "0.00%"
            Case UCase("Cats"):  Range("Row18").NumberFormat = "0"
        End Select

Alternatively insert Options Compare Text at the top of your code module. This makes string comparisons non-case-sensitive throughout the module.

Do you know how to use breakpoints? Add one at the start of your procedure and step through the code using the F8 key, watching which statements are being executed and what the values in your variables are. Make sure when you get "Dogs", the statement Range("Row18").NumberFormat = "0.00%" is being highlighted: if it isn't, then there's a discrepancy between the data you have and the data you think you have, for example "Dogs" and "Dogs " (trailing space).Mousing over Target.Value will reveal that.

To disregard leading and trailing spaces, change your Select line to:-

        Select Case UCase(Trim(Target.Value))

If you're expecting your data to always trigger one of the Case statements, you could add a Case Else immediately before the End Select which will highlight the fact that some data didn't trigger any of the Case statements:-

            Case Else: MsgBox "Oops! """ & Target.Value & """ fell through all my case statements!"

Persevere - this won't be insoluble!

 

[hatstand]

Well this got me very confused. If I use your code:

Select Case UCase(Target.Value)
Case UCase("Dogs"): Range("Row18").NumberFormat = "0.00%"
Case UCase("Cats"): Range("Row18").NumberFormat = "0"
End Select

In a new workbook it works perfectly. But not in the current workbook. But if the current workbook is open the code won't work in the new one. Eh!

The code sheet in the current workbook has other codes but they all relate to buttons on the worksheet.

 

[Ruddles]

If you have mutliple workbooks open, you need to make sure that any references to rows/columns/cells/ranges are fully qualified, i.e. that they point to the exact place you expect them to be pointing and that they're not pointing to whatever worksheet happens to be active at the time the code runs. In fact it's good practice to do that all of the time.

So for example, rather than Range("Row18") you should always code ThisWorkbook.Sheets("Sheet1").Range("Row18"). (Insert the correct sheet name.)

 

[hatstand]

Hi,

Thanks once again for the reply.

I have changed the references to be exact. As the code works perfectly on any workbook apart from the one. I think it's time to transfer everything into a new workbook.

I really appreciate your help and hope you have a great weekend.