MsgBox Popup in Excel

[Danny Legault]

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

 

[Bernie Deitrick]

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

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.")