Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel POPUP | Excel Worksheet Functions | |||
How to make excel popup windows | Excel Discussion (Misc queries) | |||
Popup MsgBox | Excel Discussion (Misc queries) | |||
Popup in excel | Excel Discussion (Misc queries) | |||
Popup form from Excel not VBA | Excel Programming |