Auto open msgbox depending on date and day

[darro]

i have this code

[/CODE]Private Sub Autpen ()
If Day(Now)= 1 Then MsgBox "my text"
End Sub

How can I make this work on first day of month but if that falls on a weekend then open on next following monday? 

Any ideas very welcome

Cheers.

 

[kpark91]

Hi, will you be opening the workbook everyday?
If not and you just want msgbox to pop up when your OS starts, you will need to look into a different solution other than VBA.

Anyways,
in Thisworkbook module

Private Sub ThisWorkbook_Open()
    If Day(Now) = 1 Then
        If Worksheetfunction.Networkdays(Now, Now) = 1 Then
            MsgBox "my text"
        End If
    End If
End Sub

Not too sure about opening on next following monday. I suspect you will need to allocate a cell of the workbook for displaying TRUE/FALSE for the status of the 'following monday'

 

[darro]

Hi thanks for the reply. I will be using the book everyday except the weekend. The code I posted does work for 1st of the month but still dont know how to make it work if the 1st is a Sat or Sun and I open it the following Monday.

Any more ideas?

 

[darro]

Any ideas out there? How cab I make this pop up on the
first of month. But if The first is a sat or sun, it waits and pops up on the following monday? Is it even possible?

Cheers

 

[kpark91]

Hi, darro..
Try this code as I have suggested before (you will need to allocate not used cell for this)

Private Sub ThisWorkbook_Open()
    If Worksheets("Sheet1").Range("A1").Value = "" Then
        Worksheets("Sheet1").Range("A1").Value = Now
    If Month(Worksheets("Sheet1").Range("A1").Value) < Month(Now) Then
        MsgBox "my text"
        Worksheets("Sheet1").Range("A1").Value = Now
    End If
End Sub

This will require cell A1 of Sheet1 and that you open it every single day (excluding the weekends)

 

[darro]

I have the code I posted above that will pop up a msgbox. I've put that in a general module. In Auto_open I am trying to write a code to check a named range called ARCD to see if it contains the date 1st of current month. If not it exits sub. If so it calls the pop up code.

I can get it to call the other code but it ignores whatever is in ARCD.

How I get the code to check the range and exit and NOT run the pop up macro if the date is found?

Thanks for all your help.

 

[kpark91]

Hi, you can check a named range by

If Worksheets("Sheet1").Range("ARCD").Value = 1 Then
    Exit Sub 'exiting the whole procedure
End If

As per to your pop up macro question,
you can simply take out MsgBox line to not run the pop up macro..