CODEWITHSUNDEEP
excelvba
Andrew
Andrew

Reputation: 29

Code won't stop file from saving when certain cells have no contents

I'm working on some code which is supposed to check to make sure that the cells at the bottom of the page in the D column have contents in them before it will let the user save the file, but for some reason it does not do this, and I am allowed to save the file even when those cells are empty, and I am unsure as to why. I put this code into the 'ThisWorkbook" object as a BeforeSave action. The primary way this code will be saved is by using a button which I made to automatically save the file to a specific location, but the same thing occurs when I save it manually.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim names As Variant 
Dim name As Variant
names = Array("sheet1", "sheet2", "sheet3") 'set up array of sheet names

For Each name In names 'check to see if cell is filled out or not
    If Worksheets(name).Cells(Rows.Count, 4).End(xlUp).value = 0 
        MsgBox "Save cancelled. Sheet " & name & " is missing signature."
    Cancel = True 'stop save from occurring
    End If

Next name

End Sub

So essentially, I want it to go through specific sheets called out by name - I've changed the sheet names and the number of sheets for this post but I don't think that should impact how the code works - check them to see if they have contents in the last row of column D (which will be empty unless the user puts information in) and then if any of these cells are empty, to not let the user continue saving the file until the signatures have at least some contents. It does none of this, allows the file to save, and gives me the error message, "Runtime error '9' Subscript out of range." I would appreciate any help with this issue!

Upvotes: 1

Views: 30

Answers (1)

Cyril
Cyril

Reputation: 6829

POsting comment as answer:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim names As Variant 
Dim name As Variant
Dim lr as Long 'ADDED
names = Array("sheet1", "sheet2", "sheet3") 'set up array of sheet names

For Each name In names 'check to see if cell is filled out or not
    With Sheets(name)
        lr = .Cells(.Rows.Count, 4).End(xlUp).Row 'ADDED
        If .Cells(lr, 4).value = "" Then 'CHANGED, but does not make sense as written unless NOTHING exists in column D (4)
            MsgBox "Save cancelled. Sheet " & name & " is missing signature."
            Cancel = True 'stop save from occurring
            Exit For
        End If
    End With
Next name

End Sub

Edit1:

In regard to your comment about the structure being incorrect, you will most likely want to check the last row based on ANOTHER column.

As we cannot see your sheet, I will assume that there is a Header for signature in the adjacent cell, which might work to your advantage. Might look like:

Cells(lr,3)    |    Cells(lr,4)
Signature:     |    ""

If the above is a true scenario, you can modify the above code to:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim names As Variant 
Dim name As Variant
Dim lr as Long 'ADDED
names = Array("sheet1", "sheet2", "sheet3") 'set up array of sheet names

For Each name In names 'check to see if cell is filled out or not
    With Sheets(name)
        lr = .Cells(.Rows.Count, 3).End(xlUp).Row 'ADDED
        If .Cells(lr, 4).value = "" Then 'CHANGED, but does not make sense as written unless NOTHING exists in column D (4)
            MsgBox "Save cancelled. Sheet " & name & " is missing signature."
            Cancel = True 'stop save from occurring
            Exit For
        End If
    End With
Next name

End Sub

The only change is in the line defining lr where I swapped the column from 4 to 3.

Upvotes: 1

Related Questions