Pop Up Message Keeps popping up

[spat82]

Hi, I want a pop up message to appear when a user selects recharge client from a drop down field.

However, the message is currently appearing every time they change anything else on the sheet when that criteria is met.

I can get this to work on one cell, but cannot get it to work on a range of cells.

This is my current code

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D9")) Is Nothing Then Exit Sub
If Range("E9").Value = 1 Then MsgBox "Please fill out Recharge Form", vbExclamation
End Sub[/VBA]

I currently use the following if statement (in cell E9)to get this to work.

=if(D9="Recharge Client",1,0)

This works on when D9 is changed, but I would like it to work if anything in Column D is changed? So something like this (although I know this is very wrong)

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D")) Is Nothing Then Exit Sub
If Range("E:E").Value = 1 Then MsgBox "Please fill out Recharge Form", vbExclamation
End Sub[/VBA]

First post and beginner on VBA, any help much appreciated! Apologies for my somewhat crude code also

[p45cal]

At its most basic (if more than one cell is changed in column D at the same time it will probably balk), try this:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D2100")) Is Nothing Then Exit Sub
If Target.Offset(, 1).Value = 1 Then MsgBox "Please fill out Recharge Form", vbExclamation
End Sub
[/VBA]

[spat82]

That works, brilliant...

Thank you!

Currently the "recharge client" is chosen from a drop down list. the only time more than one cell will be changed at the same time is someone copies and pastes one of the drop down options onto numerous other cells.

Is there a way of restricting it so that the option must be selected from the drop down, rather than typed in? Both are possible as long as they type exactly the same as one of the drop down options.

This isn't very important though, so no worries if there isn't this option.

Thanks again

[p45cal]

Presumably the dropdown is a Data Validation drop down - this defaults to only allowing the correct input, dropdown used or typed in, it doesn't matter. If it is allowing incorrect input then Data Validation needs to be configured to alert the user.

[spat82]

yes, its data validation, and only allows correct input.

It just means someone can copy and paste over numerous cells (crashing the vba) rather than going into each cell and picking from the drop down.

Hopefully not a large issue for us though.

Thanks again

[GTO]

Hi All :-)

Hopefully you mean if the user PasteSpecial|Values, as a stright Paste wipes out the DV.

Anyways, not well tested, but I was thinking maybe?:
[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("D220")) Is Nothing _
And Not Target.Count > 1 Then

If Not Target.Value = vbNullString _
And Target.Offset(, 1).Value = 1 Then
MsgBox "Please...", vbInformation, vbNullString
End If

ElseIf Not Application.Intersect(Target, Me.Range("D220")) Is Nothing _
And Target.Count > 1 Then

Application.Undo
MsgBox "Only one cell may be updated at a time...", vbExclamation, vbNullString

End If
Application.EnableEvents = True
End Sub[/vba]

Hope that helps,

Mark