Forum Discussion
VBA Code: ensuring users fill out specific cells
Hi all, I'm looking for some help with the following:
My team has a excel spreadsheet with a table in it. This spreadsheet gets passed around different users as they each need to fill in a row with their data. The problem is, not all users are filling in the "mandatory" columns. So, when I open up the document after everyone has supposedly filled it in, I see blanks where I should see data.
I have used CoPilot to help write a VBA code that basically stops the document from being closed or saved until a row in the table has been filled in:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not IsTableComplete() Then
MsgBox "You must complete at least one row in the table before saving.", vbCritical, "Incomplete Data"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not IsTableComplete() Then
MsgBox "You must complete at least one row in the table before closing.", vbCritical, "Incomplete Data"
Cancel = True
End If
End Sub
Function IsTableComplete() As Boolean
Dim ws As Worksheet
Dim tbl As ListObject
Dim row As ListRow
Dim isComplete As Boolean
' Set the worksheet and table name (update "Sheet1" and "Table1" to match your spreadsheet)
Set ws = ThisWorkbook.Worksheets("Sheet2")
Set tbl = ws.ListObjects("Table14")
isComplete = False ' Default to incomplete
' Check if any row is fully filled out
For Each row In tbl.ListRows
Dim allFilled As Boolean
allFilled = True ' Assume the row is complete
Dim cell As Range
For Each cell In row.Range
If isEmpty(cell.Value) Then
allFilled = False
Exit For
End If
Next cell
If allFilled Then
isComplete = True
Exit For
End If
Next row
IsTableComplete = isComplete
End Function
However, when I fill in the first row, save and close the document, upon re-opening it, the warning messages disappear and I can save and close the document.
I need the code to reset itself each time a user opens the document. So that it checks when its been newly opened for the next blank row, if that has not been filled in then the user should not be able to save or close the document until it is.
Any help on this would be much appreciated :)
The 'practice' table looks like this:
A workaround I used recently with a client was to utilize conditional formatting to indicate when a record was incomplete.
This is exactly what I was going to suggest. Just create a cell off to the right (or left (or both)) that is red until each cell has content. The VBA solution, even if it can be made to work, is overkill.
Of course, unless you're using Data Validation as well, it's possible that one or more of the cells may have invalid data. But I'm also assuming that the omissions you're experiencing are inadvertent and that, in fact, all your colleagues need is the kind of reminder that the Red conditionally formatted cell would provide.
The issue with a vba solution is that it's very easy to not enable content:
- if the workbook is opened from the web, it will disable all vba macros
- if the workbook contains 'the mark of the web', Windows will block it and then Excel will view it as "untrusted"
- Someone can simply choose to not enable content on open
A workaround I used recently with a client was to utilize conditional formatting to indicate when a record was incomplete. When the information is entered then the fill goes away. It's not fool-proof, but it was working in a situation where 7-8 people were in the workbook at the same time.
A workaround I used recently with a client was to utilize conditional formatting to indicate when a record was incomplete.
This is exactly what I was going to suggest. Just create a cell off to the right (or left (or both)) that is red until each cell has content. The VBA solution, even if it can be made to work, is overkill.
Of course, unless you're using Data Validation as well, it's possible that one or more of the cells may have invalid data. But I'm also assuming that the omissions you're experiencing are inadvertent and that, in fact, all your colleagues need is the kind of reminder that the Red conditionally formatted cell would provide.
Does this do what you want?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not IsTableComplete() Then MsgBox "You must complete the entire row in the table before saving.", vbCritical, "Incomplete Data" Cancel = True End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not IsTableComplete() Then MsgBox "You must complete the entire row in the table before closing.", vbCritical, "Incomplete Data" Cancel = True End If End Sub Function IsTableComplete() As Boolean Dim ws As Worksheet Dim tbl As ListObject Dim row As ListRow ' Set the worksheet and table name (update "Sheet1" and "Table1" to match your spreadsheet) Set ws = ThisWorkbook.Worksheets("Sheet2") Set tbl = ws.ListObjects("Table14") IsTableComplete = True ' Default to complete ' Check if any row is fully filled out For Each row In tbl.ListRows Dim cell As Range For Each cell In row.Range If IsEmpty(cell.Value) Then IsTableComplete = False Exit Function End If Next cell Next row End FunctionHi, so this us as far as I got when I first started to create this document. So, in a way it does work, but the table has to be only as long as the last piece of data,
i.e. i can't have a pre-made table of 14 rows, because the code will then force the document to not save or close unless the last row of the table (row 14) is filled out.
I wanted to have a document set up ready for the many workers to open it, start filling in their row and each column, and then if they went to shut down the document and they haven't filled in all the cells in the row they started to fill out, a warning message would pop up and stop them from closing it....I don't see how I could make that work.
Brilliant thanks everyone. I will let my colleague know and tell them about this possible solution instead. Thank you
