Pop up Reminder

[shaunkaz]

Hi
Can someone please help me..

When an operater inputs a number in a cell I want a popup (reminder) to appear with a message is this possible.


For example:

operator inputs a number in cell c:5 (popup to appear with message:, then operator inputs a number in cell C:6 (popup to appear with message) and so on

Thanks

Shaun..

 

[Domski]

You could use a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C5:C10")) Is Nothing Then
    MsgBox "Your message here", vbInformation
End If
End Sub

Right click on the sheet tab, select view code and pop the code above on the resulting page.

It will show a message if any cells in C5:C10 are changed.

Dom

 

[shaunkaz]

Thanks.. I have just put that in and it works now as an addition to the popup message for column range C5: C10 is it possible to have another popup reminder with a different message for G5:G10.

** And last of all is it also possible to have another popup message at certain times, 05:45, 13:45 and 21:45.

This spreadsheet is open 24 hours a day

Thanks

Shaun..

 

[Domski]

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim strMessage As String
 
If Target.Count > 1 Then Exit Sub
 
If Not Intersect(Target, Range("C5:C10")) Is Nothing Then
 
Select Case Time
 
    Case Is < TimeValue("05:45"): strMessage = "Message 1"
    Case Is < TimeValue("13:45"): strMessage = "Message 2"
    Case Is < TimeValue("21:45"): strMessage = "Message 3"
    Case Else: strMessage = "Message 1"
 
End Select
 
    MsgBox strMessage, vbInformation
    
End If
 
If Not Intersect(Target, Range("G5:G10")) Is Nothing Then
 
Select Case Time
 
    Case Is < TimeValue("05:45"): strMessage = "Message 4"
    Case Is < TimeValue("13:45"): strMessage = "Message 5"
    Case Is < TimeValue("21:45"): strMessage = "Message 6"
    Case Else: strMessage = "Message 4"
 
End Select
 
    MsgBox strMessage, vbInformation
    
End If
 
End Sub

Dom

 

[shaunkaz]

Thanks.. When inputing into the two different columns they now bring up a message.. Brill

On the time based ones I reset my clock on the PC for eg. 13:42 but when it reached 13:45 no popups appeared..

I take it that this gets the time from my PC

Thanks

Shaun

 

[Domski]

Sorry, misunderstood.

For the first part that re changing the cells use this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Count > 1 Then Exit Sub
 
If Not Intersect(Target, Range("C5:C10")) Is Nothing Then MsgBox "Message 1", vbInformation
 
If Not Intersect(Target, Range("G5:G10")) Is Nothing Then MsgBox "Message 2", vbInformation
 
End Sub

Again right click on sheet tab, view code, and put code on the page that appears.

For the pop up message at certain time try this...

Put this code in a module in the workbook's VBA project:

Public RunWhen As Double
Public Const cRunWhat = "Display_Message"  ' the name of the procedure to run
 
Sub StartTimer()
 
Select Case Time
 
    Case Is < TimeValue("05:45"): RunWhen = TimeValue("05:45")
    Case Is < TimeValue("13:45"): RunWhen = TimeValue("13:45")
    Case Is < TimeValue("21:45"): RunWhen = TimeValue("21:45")
    Case Else: RunWhen = TimeValue("05:45")
 
End Select
 
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
 
End Sub
 
Sub Display_Message()
 
MsgBox "Hello"
 
StartTimer
 
End Sub

and then this needs to go on the ThisWorkbook page of the VBA project:

Private Sub Workbook_Open()
 
StartTimer
 
End Sub

Hope it helps,

Dom

 

[Domski]

I should have mentioned that you will need to close and re-open the workbook to get the timer running.

Dom

 

[shaunkaz]

Thanks.. That works great, hopefull my operators will not forget to complete the next task..

Shaun