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

tyrese215

New Member
Joined
Feb 25, 2009
Messages
33
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....
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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".
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top