User Profile
Rhianna
Copper Contributor
Joined 5 months ago
User Widgets
Recent Discussions
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:
