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

excelvbafirsttime

New Member
Joined
Jan 22, 2013
Messages
1
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
Code:
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.
 
Last edited:

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top