Activate MsgBox on a formula result

[SeliM]

Good morning

I’m endeavouring to develop script that runs a msgbox based on a formula result.

The first part is a macro that counts how many times it is ran on a particular day.

The second part is if the count is >1 then a msgbox is activated to alert the user.

I have used the following for first part: in a Module

Sub Data()

'Macro1 Macro

Sheets("Sheet2").Select

With Sheets("Sheet2")

If .Range("B1") <> Date Then

.Range("B1") = Date

.Range("A1") = 0

End If

.Range("A1") = Range("A1") + 1

End With

Sheets("Sheet1").Select

Range("B2").Select

ActiveCell.FormulaR1C1 = "1"

Range("b3").Select

ActiveCell.FormulaR1C1 = "2"

Range("b4").Select

ActiveCell.FormulaR1C1 = "3"

Range("b5").Select

ActiveCell.FormulaR1C1 = "4"

End Sub

This macro works perfectly (thanks to MrExcel)

I have tried to develop the following for second part from script I found on the web: under sheet 2 (where the data is held)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Response

Dim Isect As Range

Set Isect = Application.Intersect(Target, Range("A1"))

If Not (Isect Is Nothing) And Range("A1") > 1 Then

Response = MsgBox("Alert! The Data macro has been run more than once today", vbOKOnly)

End If

End Sub

Before I tried the second part macro count works fine. Adding second part I get an error “Sheets("Sheet2").Select

Your advice would be much appreciated

Geoff

 

[Michael M]

Where did you put the second part of the code.
It needs to be in the sheet 2 module by itself.
The code runs fine for me in that moduke !!

 

[rlv01]

. Adding second part I get an error “Sheets("Sheet2").Select

Usually that means the macro cannot find Sheet2.

Sub Data()
    Dim WS As Worksheet
   
    On Error Resume Next
    Set WS = Worksheets("Sheet2")
    On Error GoTo 0
   
    If WS Is Nothing Then
        MsgBox "Error - Cannot locate worksheet 'Sheet2' in workbook '" & ActiveWorkbook.Name & "'", vbOKOnly Or vbCritical, ActiveWorkbook.Name
        Exit Sub
    End If
   
    With WS
        If .Range("B1") <> Date Then
            .Range("B1") = Date
            .Range("A1") = 0
        End If
        .Range("A1") = .Range("A1") + 1
    End With
   
    On Error Resume Next
    Set WS = Nothing
    Set WS = Worksheets("Sheet1")
    On Error GoTo 0
   
    If WS Is Nothing Then
        MsgBox "Error - Cannot locate worksheet 'Sheet1' in workbook '" & ActiveWorkbook.Name & "'", vbOKOnly Or vbCritical, ActiveWorkbook.Name
        Exit Sub
    End If
   
    With WS
        .Range("B2").Value = "1"
        .Range("b3").Value = "2"
        .Range("b4").Value = "3"
        .Range("b5").Value = "4"
    End With
End Sub

This code goes in the code module for "Sheet2"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Isect As Range
    Set Isect = Application.Intersect(Target, Range("A1"))
    If Not (Isect Is Nothing) And Range("A1") > 1 Then
        MsgBox "Alert! The Data macro has been run more than once today", vbOKOnly, "Run Count = " & Range("A1").Value
    End If
End Sub

 

[SeliM]

Thanks very much.
I was a long way out

It works a treat.
I'm trying to now have a similar code for a clean macro that deletes the data input. Cross fingers my end
Geoff