Select Range of entered data based on last entry in col A then search range and msgbox empty cell for user to complete

[tyrese215]

Hi all,

I am doing up a spreadsheet for my client to fill out.

I need to perform two tasks as follows:

1)
I have a range of data that will start from column A to Column E.

The range should be defined from A1 to the last row entered in column A.

It can be 50 or 70 or any number of records depending on how many lines that need to be entered. I think this is an xlendup thingy.

Ok, next I want the code to search though all the cells and ensure there is a value in every cell. It can be txt or a number. If the cell is empty, I want the code then to highlight this cell and prompt the user to for example.

"Please ensure you enter the data in cell D46. This cell needs to be completed."

This piece of code will be used to ensure that the user has filled all the cell in the worksheet before emailing it too myself.

If all cell have been completed then the code and go onto the following procedure

2)
the second procedure then should run on completion of the above procedure. This procedure needs to ensure cell F1 on sheet 1, equals the sum of A1 and A2 on sheet 2. If it doesnt, I need a message box to appear to say; please check totals in sheet 1 equalls total the sum of the A1 and A2 on sheet 2.

If these totals equal then I want the above procedures 1 and 2 to be attached to a button that will change to a big tick. Please tell me the best way to do this. If one of the above procedures fails then the button will remain a Cross until everything is fixed.


As mentioned above, I would like to have this code attached to a Button that will be clicked and then the above code will run. If there are no empty cells in the range AND if the totals equal as per procedure 2, then the button should turn to a tick and then say: workbook ready for submission.


Then the user will be able to submit it to myself...

Thank you everyone for your time and your excellent and kind expertise. I thank you greatly in advance....

 

[MickG]

Hi, Try this:-
You will have to change the CommandButton name to suit.
NB:- When you first run this code, the command Button Caption will show a "P" or an "O".
In Design Mode Right click the Command Button and in the properties find Font. This should show "Wingding 2" open the Font selection Menu and Click "OK", This seems to stir the code into Showing a "Tick or a "Cross".

Sub Tick()
Dim Rng As Range, Cl As Range, Temp As String
Dim Correct As Boolean
Correct = False
CommandButton1.Font.Name = "Wingdings 2"
CommandButton1.Caption = "O" '"Cross"
CommandButton1.Font.Size = 36
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    Set Rng = Rng.Resize(, 5)
    For Each Cl In Rng
        If Cl = "" Then
            Temp = Temp & Cl.Address & Chr(10)
        End If
    Next Cl

    If Temp > "" Then
        MsgBox "NB:-Data is missing from " & Chr(10) & "the following Cell/Cells " & Chr(10) & Temp
    End If

With Sheets("sheet2")
    If Not Range("F1") = .Range("A1") + .Range("A2") Then
        MsgBox "Please Check Totals of Sheet (2) Range(A1) Plus (A2)"
        Correct = False
    Else
        Correct = True
    End If
End With

If Application.CountBlank(Rng) = 0 Then
    Correct = True
Else
    Correct = False
End If

If Correct = True Then
CommandButton1.Caption = "P" '"Tick"
Msgbox "WorkBook Ready for Submission !!"
End If

End Sub

Regards Mick