AMBIGUOUS NAME DETECTED: Workbook_BeforeSave

[The_T]

Hi guys,

I'm new to the forum so apologies if I've already broken a rule/some rules by posting this.

I am trying to create an excel for work that doesn't allow the user to save if a cell is blank.

Having copied some VBA coding from the internet, I currently have this in the workbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("E4:E6,F4:F6,G4:G6,H4:H6,I4:I6,J4:J6,K4:K6,L4:L6,M4:M6,N4:N6")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "A cell for apartment N1 has been left blank. Please fill in all cells."
Cancel = True
End If
End Sub

It works perfectly well. However, I then want to repeat the same process for another selection of cells with a different message, I get an error stating 'COMPILE ERROR: AMBIGUOUS NAME DETECTED: Workbook_BeforeSave'.

The second batch of coding I am trying to use is:

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("E8:E10,F8:F10,G8:G10,H8:H10,I8:I10,J8:J10,K8:K10,L8:L10,M8:M10,N8:N10")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "A cell for apartment N2 has been left blank. Please fill in all cells."
Cancel = True
End If
End Sub

I understand the error is resulting from the fact that I have two private subs with the same name. However, I don't know how to combine these so that I could apply the above coding to multiple cell selections.

The excel document in question can be found here (it has another macro running which forces the user to enable macros in order to see the rest of the spreadsheet):
https://we.tl/t-ArL9QhU1yY

Any help would be much appreciated!

Cheers guys.

 

[Fluff]

Hi & welcome to MrExcel.
Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Dim myrange As Range
   Set myrange = Worksheets("Sheet1").Range("E4:E6,F4:F6,G4:G6,H4:H6,I4:I6,J4:J6,K4:K6,L4:L6,M4:M6,N4:N6")
   If Application.WorksheetFunction.CountA(myrange) < myrange.Cells.Count Then
      MsgBox "A cell for apartment N1 has been left blank. Please fill in all cells."
      Cancel = True
   End If
   Set myrange = Worksheets("Sheet1").Range("E8:E10,F8:F10,G8:G10,H8:H10,I8:I10,J8:J10,K8:K10,L8:L10,M8:M10,N8:N10")
   If Application.WorksheetFunction.CountA(myrange) < myrange.Cells.Count Then
      MsgBox "A cell for apartment N2 has been left blank. Please fill in all cells."
      Cancel = True
   End If
End Sub

 

[The_T]

You are a genius Fluff! Thank you ever so much.

 

[Fluff]

Glad to help & thanks for the feedback