Danny,
Once your workbook is all set up, try using this event, copied into
the codemodule of the ThisWorkbook object:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim mystr As String
Dim myRange As Range
mystr = "'" & Worksheets(1).Name & _
":" & Worksheets(Worksheets.Count).Name & "'!" & Target.Address
If Evaluate("Count(" & mystr & ")") 1 Then
MsgBox "Don't do that!"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub
--
HTH,
Bernie
MS Excel MVP
"Danny Legault" wrote in
message ...
I hope somebody can help
I need to have a message box pop up if somebody enters information
in a cell such as C9 in more than one sheet of an Excel file.
Scenario:
a) 5 sheets in file - each the same layout - weeks 1 to 5 for weeks
of month
b) each row can only have one occurance in any month - thus only one
sheet can be populated
c) columns C,D,F and G are used for data entry of date of month for
each week - 1 to 31
d) columns T,U,X, and Y have formulas to test for duplication of
data entry
X Column sample
=IF(ISBLANK(G9)=TRUE,"OK",IF(OR(ISBLANK('Cleaning Week
1'!G9)=FALSE,ISBLANK('Cleaning Week 3'!G9)=FALSE,ISBLANK('Cleaning
Week 4'!G9)=FALSE,ISBLANK('Cleaning Week 5'!G9)=FALSE),"DUPLICATE
ENTRY","OK"))
e) Y Column tests if any duplicate entries exist - if so - result =
1
=IF(OR(T9="DUPLICATE ENTRY",U9="DUPLICATE ENTRY",W9="DUPLICATE
ENTRY",X9="DUPLICATE ENTRY"),"1",)
f) Z column tests if column Y = 1
=IF(Y9="1",1,)
g) VB Change Event
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Cells(Target.Column, 26) < 0 Then Run ("Date_Warning")
End Sub
h) Date Warning Msg Box
Sub Date_Warning()
Dim spudd As Variant
spudd = MsgBox("You have entered a date for this unit that is
already entered in another week! Please check your entry and
correct.")
End Sub
Thanks
Danny