Problem with code

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,046
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 ??
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top