Pop Up Box

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

This code compares a cell value to other cells in the same
row and gives pop up if it is a dup. Can this be modified
to compare a cell value to existing work sheet names?

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Rows(2)) Is Nothing Then
If Application.WorksheetFunction.CountIf( _
Rows(2), "=" & Target.Value) > 1 Then
MsgBox "Please enter a unique value."
Target.ClearContents
Target.Select
End If
End If
End Sub
 
Steve,

Do you mean

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Rows(2)) Is Nothing Then
For Each sh in Activeworkbook.Worksheets
If Target.Value = sh.Name Then
MsgBox "Sheet exists."
Target.ClearContents
Target.Select
End If
Next sh
End If
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top