Problem with code

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
In cell B4, when someone has entered text in this cell and they press either return or enter, I want a message box to appear with a yes or no option. If they click NO I want B8 to become deselected, if they click YES i want cell B6 to be deselected. This is the code I have which is set up in a module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Response = MsgBox("Is this a new listing?", vbYesNo)
Select Case Response
Case Is = vbNo
Exit Sub
Case Is = vbYes
Range("B6").Select
Exit Sub
End Select
End Sub

Can anyone see the obvious mistake, why nothing at all happens ??
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Erm, what do you mean by "deselected"?

When you say that nothing at all happens, do you mean that nothing happens after the Yes/No prompt, or that the prompt doesn't appear?
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
Apologies, I meant selected not deselcted and the message box doesn't appear,
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
I assume that the code is stored in the worksheet code area for the sheet?

You may have switched off event handling accidentally. Try putting application.enableevents in a module on its own and running it, and then trying again.

By the way, you have code for selecting B6 when Yes is pressed, but no code for doing anything when No is pressed. That isn't what you said you are aiming for.
 

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
You've used some words there that I'm not familiar with but this should work.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$B$4" Then Exit Sub
    If MsgBox("Is this a new listing?", vbYesNo) = vbYes Then
        Range("B6").Select
        Else: Range("B8").Select
    End If
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,732
Messages
5,573,903
Members
412,555
Latest member
mark84
Top