Code now will not run

[EddyStone]

Using Excel on MAC (Latest)
My program worked fine a few days ago. I haven't done anything but now the start up macro fails.
The error is:-
Run Time Error '6'
Overflow

This is the code

Sub MG01Apr57()
Private Sub Workbook_Open()
Dim Ans As String
Ans = MsgBox("Update the Invoice Number", vbYesNo + vbInformation)
If Ans = vbYes Then
With Sheets("Invoice").Range("i6")
.Value = .Value + 1
End With
End If
ActiveWorkbook.Save
End Sub

I get the same error if I select either Y or N to update the Invoice Number.

All help welcome.

 

[Joe4]

What exactly is currently in cell I6 on the Invoice tab?
Can you post the current value?

 

[MARK858]

Then no idea as the code runs fine for me as long as your code looks like the below now.

Private Sub Workbook_Open()
    Dim Ans As String
    Ans = MsgBox("Update the Invoice Number", vbYesNo + vbInformation)
    If Ans = vbYes Then
        With Sheets("Invoice").Range("i6")
            .Value = .Value + 1
        End With
    End If
    ActiveWorkbook.Save
End Sub

Edit: should have refreshed first, see Joe4's question

 

[EddyStone]

In call i6 at the moment is the number 278.
As I mentioned earlier the code works fine in Windows 10 (Office 16), but not on a MAC (Catalina) MS Excel.
The really odd thing is it did work on the MAC a week ago. I only got the error a few days ago and I didn’t change anything, to my knowledge. I think this is more a MAC problem rather than a code problem.
Would be great if anyone who is running Catalina and the latest MAC version of Office Excel could try the code and see what happens.
Many thanks for any help, suggestions.

 

[MARK858]

As I mentioned earlier the code works fine in Windows 10 (Office 16), but not on a MAC (Catalina) MS Excel....

Would be great if anyone who is running Catalina and the latest MAC version of Office Excel could try the code and see what happens.

Just to be clear for anyone who tests by "latest MAC version of Office Excel" do you mean you are running Office 2019 or Office 365... please specify.

 

[EddyStone]

Sorry should have said Mark, using Office 2019.

Just cant work this one out why should the code work with Windows 10 and not MAC
All I really want to do is automatically increment the value currently in cell I6 by 1 and the save the workbook. So just need macro for ThisWorkbook.

 

[Joe4]

Sounds like it might be a Mac-specific issue, and unfortunately, I do not have a Mac to test/confirm.
I thought it might be something else with the code, but we made those changes and it did not seem to solve it for you, so I am afraid there isn't anything else I can really offer on this one.

 

[EddyStone]

Thanks to all who have tried to help. I am now certain this is an Excel Office 19 for MAC problem. I now have a solution that works in MAC and automatically increments the value in Cell I6 by 1 on start up and then saves the sheet. Just continuing this thread to maybe help others. The problem seems to be in the message box area.

This code works fine in Windows but not in Office for MAC (Get the error listed in the thread above)

Sub MG01Apr57()
Private Sub Workbook_Open()
Dim Ans As String
Ans = MsgBox("Update the Invoice Number", vbYesNo + vbInformation)
If Ans = vbYes Then
With Sheets("Invoice").Range("i6")
.Value = .Value + 1
End With
End If
ActiveWorkbook.Save
End Sub

This code works perfectly in Excel for MAC (Office 2019). Just done away with the message Y/N box.

Private Sub Workbook_Open()
With Sheets("Invoice").Range("I6")
.Value = .Value + 1
End With
ActiveWorkbook.Save
End Sub

Cheers Ed

 

[Joe4]

Thanks for posting your solution.

So, it appears that the issue is with the MsgBox. I am curious, if you make a few tweaks to it, like this:

Private Sub Workbook_Open()
    Dim Ans
    Ans = MsgBox("Update the Invoice Number", vbYesNo)
    If Ans = vbYes Then
        With Sheets("Invoice").Range("i6")
            .Value = .Value + 1
        End With
    End If
    ActiveWorkbook.Save
End Sub

does that work?

 

[EddyStone]

Hi Joe,
BINGO!

Yes indeed that works a treat. Grateful thanks, much appreciated Joe.
Now why does removing + vbInformation solve the problem for a MAC

Thanks Ed

 

[Joe4]

I actually made two changes. That, and I changed

Dim Ans as String

to

Dim Ans

I did that because I have found sometimes Excel gets picky about how you declare variables used to return Message Box values. The change just makes it a variant, which allows anything.

With the other one, I thought maybe we should simplify that argument, that maybe it does not like the "+" in it. I typically just use "vbYesNo" most of the time, with nothing else after it.

If you are bored and have the time to mess around with it, you can test it out and see which of these two changes did the trick (by adding one back in at a time, and see when it blows up).