msgbox in sheet

[Peter]

Hi there.

In a sheet, I've got a validation list that contains the values to select
(ex: 1,2 3, 4, 5) and I select the value "1" on the first cell, then, in the
same column, second cell, if I choose the value "1" again, I want that apears
a msgbox saying that value is allready there and when I click in the button
on the msgbox, that second cell keeps blank, is it possible?

tku,

 

[Jacob Skaria]

Since you already have a data validation in the cell..you will have to use
the worksheet event..Select the sheet tab which you want to work with. Right
click the sheet tab and click on 'View Code'. This will launch VBE. Paste the
below code to the right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTemp As Range
Set rngTemp = Range("A1:A5")
If Not Application.Intersect(Target, rngTemp) Is Nothing Then
If WorksheetFunction.CountIf(rngTemp, Target) > 1 Then
Application.EnableEvents = False
Target = "": Target.Activate: MsgBox "Value already exist"
Application.EnableEvents = True
End If
End If
End Sub

If this post helps click Yes