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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,543
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,543
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,123,454
Messages
5,601,764
Members
414,471
Latest member
cchunyadi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top