MsgBox Popup in Excel
 

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

MsgBox Popup in Excel
 

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