MsgBox Popup in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
I hope somebody can help

I need to have a message box pop up if somebody enters information
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
already entered in another week! Please check your entry and
correct.")
 
Back
Top