Message box error 1

[vba317]

I am trying to use the messagebox function for the first time with no luck. I am hoping I can get some help.

My program imports data everyday with 2 years worth of data. What I am trying to do is check the first cell of the last row and if that is not today's date I want the program to do is not save the file and give me a message saying that the current file is not being saved.

 LR = ActiveSheet.UsedRange.Rows.Count
COde .....
  LR1 = LR - 1
                
                                
If Range("A" & LR1).Value < Date Then MsgBox(Prompt,vbInformation,"This file will not be saved") next lcount


   wbResults.Close SaveChanges:=True
Next lCount

 

[macropod]

Hi vba317,

Presumably something along the lines of:

LR1 = LR - 1
If Range("A" & LR1).Value < Date Then
  MsgBox(Prompt,vbInformation,"This file will not be saved")
Else
  wbResults.Close SaveChanges:=True
End If
Next lCount

Cheers
[MS MVP - Word]

 

[vba317]

I guess I must have a typing error because I get a compile error.

 LR1 = LR - 1
               
                                
       If Range("A" & LR1).Value < Date Then
[Red] MsgBox(Prompt,vbInformation,"This file will not be saved")[/Red]
        Else
              wbResults.Close SaveChanges:=True
        End If
        
        Next lCount

The line in red is causing the error but I can't figure out why. Can someone help?

 

[macropod]

Hi vba317,

I copied your MsgBox code without thinking about its correct construction when used this way. Try it without the brackets, ie:
MsgBox Prompt, vbInformation, "This file will not be saved"
I trust you have a string variable somewhere, named 'Prompt'. If not, you'll need one - or to insert double-quote bounded prompt into the statement.


Cheers
[MS MVP - Word]

 

[GVF]

Your msgbox syntax is wrong. Replace the word Prompt with your message. As written, your messagebox is looking for a variable named Prompt. Use parenthesis when you need a return from the message box (which button did the user click)...

MsgBox "The file will not be saved", vbOkOnly + vbInformation, "Important Message"

'If you need a return from the message box then..

MyReply = MsgBox("Do you want to save the file?", vbYesNo +vbInformation, "Important Message")
If MyReply = vbYes Then 'the return constants like vbYes are in Help
ActiveWorkbook.Save
End If

 

[macropod]

Hi GVF,

The fact the MsgBox is looking for a variable named 'Prompt' doesn't make it wrong - all it means is that the variable needs to be supplied, which I assume vba317 is doing. For all you and I know, the 'Prompt' variable has different strings, depending on whatever occurs somewhere earlier in the code.

Likewise, there is no indication the MsgBox is feeding into a return variable - which is why it should not have parentheses around the expressions. In fact, that's what was causing the error!


Cheers
[MS MVP - Word]

 

[vba317]

I did modify the code as following:

If Range("A" & LR1).Value < Date Then
         MsgBox "This file will not be saved", vbOKCancel
         Else
         wbResults.Close SaveChanges:=True
End If

This weekend I ran into a problem that I hadn't anticipated. During the week I do my updates at night. So the original formula works. Usually I don't do my updates for Friday until Saturday or Sunday Night. So my first question is how do I convert my date function to a day function? So if I am doing an update on Sat or Sun it will be comapring the correct day?

I just wanted to thank everyone for their assistance in advnce.

 

[macropod]

Hi vba317,

I think there's a problem with your approach. Firstly, if you're running the code overnight, two issues arise:
1. What is the validity of the Date test if it is performed after midnight?
2. If the code execution triggers the message box, nothing further will happen until it is dismissed.
I think you'll need to resolve both issues.

As for the weekends issue, what date should the code be testing against - the previous Friday, the day concerned, or the following Monday? Without knowing that, it's impossible to answer your question:

So if I am doing an update on Sat or Sun it will be comapring the correct day?

If you need to test against the previous Friday or the following Monday, modifying the code accordingly is quite simple. For example, to use the previous Friday on the weekend:

Dim DtVal As Date
DtVal = Date
If Format(Date, "ddd") = "Sat" Then DtVal = Date - 1
If Format(Date, "ddd") = "Sun" Then DtVal = Date - 2
If Range("A" & LR1).Value < DtVal Then

Cheers
[MS MVP - Word]