Checking for required values in Excel

[tskelton]

Using Excel, let's say I have a spreadsheet with 6 columns of data, 4 of which are required. In database terms, these 4 columns make up my composite key.

[b]Branch  Month  Year    Contract[/b]  Salesperson  Amount
101     9      2005    1234      Judy         100.00

(The column names above in bold are required.)

When the user saves the workbook, I would like to call VBA to check all rows that contain data to ensure that all 4 required columns are populated. If a row doesn't contain any data, the code doesn't have to run the check.

If the data checks correctly, the code can then save the workbook. If not, an error messagebox should appear.

I think I should be using the Workbook_BeforeSave event, but I'm not sure. I have a little bit of experience with VBA, but not much, so I welcome any and all suggestions.

Thank you.

 

[Crystalyzer]

OK probably not the most elegant of solutions but here goes!

First you must create dynamic range names for your columns headers (see FAQ's how to change range names dynamically)

Second copy this code into the ThisWorkbook section in the VBV editor. Make sure the array c below contains the range names exactly as you have created them.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Declare Variables
Dim i As Integer
Dim m As Integer
'Set range/column names in order into array c
c = Array("Branch", "Month", "Year", "Contract", "Salesperson", "Amount")
'Set number of rows for each range into array r
r = Array(Range(c(0)).Rows.Count, Range(c(1)).Rows.Count, _
    Range(c(2)).Rows.Count, Range(c(3)).Rows.Count, _
    Range(c(4)).Rows.Count, Range(c(5)).Rows.Count)
'Set m equal to the first value
m = r(0)
'cycle through remaining values replace m with larger values
'so that m will ultimately be the maximum value in r
For i = 1 To 5
    If m < r(i) Then
        m = r(i)
    End If
Next
'cycle through the 1st four columns (requiring information)
'and check that the number of rows in that range is not less
'than the maximum found above.
For i = 0 To 3
    If r(i) < m Then
        MsgBox ("Data is missing from " & c(i))
        Cancel = True
    End If
Next
End Sub

Anytime the user selects the save or save as menu items this code will run to check that the columns that must have data have the same number of rows as the largest range otherwise the save will not work and the message box appears telling them what range is missing data.

Hope this helps


Thanks and best regards,
-Lloyd

 

[Crystalyzer]

Also, for simplicity in the code I gave you you could replace the loop that sets m to the maximum value with this:

m = Application.WorksheetFunction.Max(r)

Much cleaner



Thanks and best regards,
-Lloyd

 

[Crystalyzer]

New code would be as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Declare Variables
Dim i As Integer
Dim m As Integer
Dim mx As Integer

'Set range/column names in order into array c
c = Array("Branch", "Month", "Year", "Contract", "Salesperson", "Amount")

'Set number of rows for each range into array r
r = Array(Range(c(0)).Rows.Count, Range(c(1)).Rows.Count, _
    Range(c(2)).Rows.Count, Range(c(3)).Rows.Count, _
    Range(c(4)).Rows.Count, Range(c(5)).Rows.Count)

'Set m equal to the maximum value in r
m = Application.WorksheetFunction.Max(r)

'cycle through the 1st four columns (requiring information)
'and check that the number of rows in that range is not less
'than the maximum found above.
For i = 0 To 3
    If r(i) < m Then
        MsgBox ("Data is missing from " & c(i))
        Cancel = True
    End If
Next
End Sub

Thanks and best regards,
-Lloyd

 

[tskelton]

Just a note to let you know that I'm still working on this project.

I'm having a few issues, but soon I'll post the solution I came to with a lot of assistance from your code.

Thanks a bunch!