User must fill in certain cells from a range before being able to save

[excelvbafirsttime]

Hi,

This is a follow up to a thread I've read here about values being entered into cells before being able to save the excel file.
http://www.mrexcel.com/forum/excel-questions/433912-must-fill-cell-before-being-able-save.html

The solution here is fine for one cell, but what I am trying to achieve is the following:
In the workbook we use, everytime a user starts a new line and enters a reference in column A (e.g. cell A3), I want to make it mandatory for them to then complete the rest of the row before saving (e.g. if there is a value in A3, they must then fill in "B3:D3", "F3").
So I want the rest of the workbook to have this rule (e.g. range A1:A1000, if there is a value in an A cell - B,C,D,F on the same row must be complete).

We tried conditional formatting to highlight the cells we want to be mandatory but users are still not filling in all the information so this is our next step.

Heres what I've entered after copying the above threads solution but keep encountering Runtime Error 13 type mismatch. I think I'm missing a parameter, does anyone have any advice? I'm using Excel 2007

Here is the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim msg As String

If Sheets("CT2013").Range("A1:A100").Value <> "" And _
Sheets("CT2013").Range("B1:D100", "F1:F100").Value = "" Then
msg = "Before saving, please ensure all green cells on your new line are completed."
Cancel = True
End If
If Cancel Then
MsgBox msg
End If
End Sub

 

[sunnyland]

what I notice in your code is :
If Sheets("CT2013").Range("A1:A100").Value <> "" And _Sheets("CT2013").Range("B1:D100", "F1:F100").Value = ""
I don't think this is going to evaluate non blank cells as your range refers to an array not a unique cell.
I have reworked your code and came up with this

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim msg As String
Dim nonBlank1 As Long
Dim nonBlank2 As Long
Dim lastrow As Long
lastrow = Sheets("CT2013").UsedRange.Rows.Count
msg = "Before saving, please ensure all green cells on your new line are completed."
'count non empty cell in column A used range
nonBlank1 = WorksheetFunction.CountA(Sheets("CT2013").Range("A1:A" & lastrow))
'count non empty cell in column B,C,D,F or 4 values per row
nonBlank2 = WorksheetFunction.CountA(Sheets("CT2013").Range("B1:D" & lastrow, "F1:F" & lastrow))
'compare nonblank1 and nonblank2
'logically if we have 3 row of data in A, we should have a result of 3rows * 4 values or 12 nonempty in relevant cells.
'if not equal it means that some cells are blank
If nonBlank2 <> (nonBlank1 * 4) Then
MsgBox msg
Cancel = True
End If
End Sub

I am using excel function in vba to count first how many non blank cell in column A from A1 to the last used row: nonblank1.
Then I do the same to count how many non blank cell in column B,C,D and F [4 columns]:nonblank2
If nonblank2 is different of nonblank1 * 4, that means there are empty fields.

I am using usedrange.rows.count to get the number of row in the used range as it start from row1

I think this may work, but as always, test it with different case scenario.