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]

The first two lines are incorrect.

[COLOR=#333333]Sub MG01Apr57()[/COLOR]
[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]

Each one is the declaration of a new Procedure. You would never have two lines like that in a row.
You cannot nest Excel VBA procedures. You must end one ("End Sub") before you start another.

 

[EddyStone]

Must admit I am no code expert so many thanks. It has been running fine under Windows for about the last 5Yrs. Guess there are much better ways of do it, maybe someone can help with new code.
This is what I want to do.
I have a Master Invoice sheet I need to
Open up the sheet automatically get a message box up asking if I want to increment the number in cell i6 by 1 Yes or No.
If Yes increment number by 1 and save sheet.
If No leave the number in Cell i6 as is.

I could at a push leave out the message box and just automatically increment the cell number in cell i6 when the sheet is opened and the automatically save the sheet.

Thanks you. Ed

 

[EddyStone]

Should have said that the reason for the change is that I am now moving over to MAC O/S

 

[Joe4]

What is the name of the module is this VBA code found in?
Do you have any code in the "ThisWorkbook" module?
If so, what does that look like?

 

[EddyStone]

Hi Joe The code in ThisWorkbook module is the code below and as shown in my original mail

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

 

[Joe4]

When is this code supposed to run?

When the workbook first opens?
When manually called?

If only when the workbook opens, try deleting this line:

[COLOR=#333333]Sub MG01Apr57()[/COLOR]

If only when manually called (i.e. from Macro Menu, via button, via shortcut), try deleting this line:

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]

Does that solve the issue?

 

[EddyStone]

Hi Joe. I want the macro to run when the workbook opens.

Tried deleting Sub MG01Apr57() . Still get the same error when I re-open the workbook (checked that line of code was still in fact deleted).
The line If Ans = vbYes Then is highlighted in yellow when I run the Debugger, get the same error code

 

[MARK858]

Did you reset the macro under the "Run" tab?

 

[EddyStone]

Yes I did Mark