I am testing a command button to run three checks on an internal stock requisition sheet ("TestOrder") in excel 2007. Where the basic premise is that from a list of inventory parts(goods), the user can enter the quantity required of item/s and specify the customer code for who the goods are for. If any of the required fields are empty then a message will appear and the procedure stops.
They must enter their initials as the Requisitioner from a drop down list in one dedicated cell (D2), and select initials of the user who will process the request from another drop down list in the dedicated cell (F2). Quantities will be entered into the appropriate Row of column L, and the customer code in the same row of column M. This sheet will then be filtered and emailed for processing.
I have, in the main got all of that working, but due to time constraints I had not been able to work on a way of checking that all the data required has been entered prior to the filtered order being emailed anywhere.
I need to check three things:
- Step 1, Have both sets of initials been entered
- Step 2, If quantities have been entered, then the customer field cannot be blank
- Step 3, If a customer has been entered then the quantity field cannot be blank.
So, all three steps have to have data in their cells.
I have got Step 1 working as the dedicated cells D2 and F2 do not change, if either cell is blank, a message appears to the user and stops the procedure.
Whereas the quantities required in column L and the customer codes in column M are variable and would be subject to a filter to remove blanks and show only what has been ordered.
So, I was working up a test initially on a single column (M-customer codes) to check if the number of non-empty cells in range is less than total number of cells in range and this is where the Run time error 424 occurred. On the line
Set myCellRange = Range("M7:M" & Range("M" & Rows.Count).End(xlUp).Row).Select
where M7 is the top cell of the listed data in that column.
My work in progress code is:
Sub btn_test_checkdata()
' On the click of this button run a check to see if two cells have the required data, in this case they would be Users Initials selected from a drop down list cell'
If [D2].Value = "" Then
MsgBox "There MUST be Initials selected in the Who is ordering Field", vbOKOnly, "Entry Reqd"
[D2].Select
Cancel = True
Exit Sub
End If
If [F2].Value = "" Then
MsgBox "There MUST be Initials selected in the Who is the Req'n being sent to Field", vbOKOnly, "Entry Reqd"
[F2].Select
Cancel = True
Exit Sub
End If
'Sub checkIfAnyCellInRangeIsEmpty()
'declare object variable to hold reference to cell range you work with
Dim myCellRange As Range
'identify cell range you work with
Set myCellRange = Range("M7:M" & Range("M" & Rows.Count).End(xlUp).Row).Select
'check if number of non-empty cells in range is less than total number of cells in range. Depending on result, display message box indicating whether cell range contains any empty cell (True) or not (False)
If WorksheetFunction.CountA(myCellRange) < myCellRange.Count Then
MsgBox myCellRange.Address & " contains at least 1 empty cell"
End If
End Sub
Obviously, I need to extend this check to both columns so any hints on that would be great, as I'm not an expert user my thinking would have me get the code to work for one column then repeat it for the other.
The current error I'm getting now, has it something to do with specifying the worksheet? As I cannot see me doing that anywhere.
Like I say, I have the main operations working in the order sheet, and this is about me building a small procedure to do a final check before anything else happens.
