2

I am new to VBA. I have a problem which I believe is very simple, but I am failing foolishly. I have to generate a MsgBox warning the user to enter data in blank cells.

I also have to mention the destination of the blank cell by indicating the parameters in the preceding column. Actually my excel list is very big, however, the image I uploaded demonstrates my needs.

In column A I have entered the name of the companies - Ferrari, Audi and Lamborghini, while in Column B I have mentioned their respective cars. In column C,D,E I have mentioned their sales figure for the months. If the user fails to enter the sales figure for one entry, I need to warn him when he checks the data validity.

The message for this image should be : Please Enter the number of units of Lamborghini Aventador and Lamborghini Veneno sold.

In my image, the yellow highlight indicates the data not entered. In my file I have over only one column ( only one month) for sales, but thousands of rows (for cars).

Snapshot Image

Community
  • 1
  • 1
Shalom Vineeth
  • 35
  • 7
  • You can use conditional formating. Like if data in column A and B are entered then cells in column C,D,E would become in red color for respective row if they are blank. If you opt for msgbox it would become irritative as you have thousand rows for verification. – Santosh Apr 18 '13 at 13:36
  • Thanks for the answer santosh. I am aware of the conditional formatting. In fact I had implemented conditional formatting by highlighting the blank cells by red color, however, since there are lots of rows, the user tends to ignore them. As you mentioned, if I generate the msgbox, there will be a lot of msgs, yes, I know that, but I want to generate the list of missing datas in a single message. So the user will not get irritated with tens of msgbox. – Shalom Vineeth Apr 18 '13 at 13:51
  • ok will post the code – Santosh Apr 18 '13 at 14:10
  • `I need to warn him when he checks the data validity.` What do you mean by this? How will the user check the data validity? Have you provided a button which the user can select? – Siddharth Rout Apr 18 '13 at 14:24
  • Yes Sidharth.. I will be creating a button for the users to check if they have missed entering any data.. – Shalom Vineeth Apr 18 '13 at 14:26
  • Can anybody vote up the question please?? I have 8 points and I need two more points to post an image. I want to post an image of the snapshot, which will give a clear idea to everybody if at all I was not clear. – Shalom Vineeth Apr 18 '13 at 14:29

1 Answers1

3

Let's say your data looks like this

enter image description here

Try this

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim lrow As Long, i As Long

    Set ws = ThisWorkbook.Sheets("Sheet3")

    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 4 To lrow
            If Application.WorksheetFunction.CountA(.Range("C" & i & ":E" & i)) <> 3 Then
                MsgBox "Please Enter the number of units of " & .Range("B" & i).Value & " Sold"
                Exit Sub
            End If
        Next i
    End With

    MsgBox "Data Validated"
End Sub

This is what you get when you run the code

enter image description here

and this when everything is filled

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks for the answer Siddharth. Actually, this is not what I have been looking for, perhaps I am unclear in my question. I have tried to upload an image of the snapshot. I believe, the snapshot will be a great tool for better explaining my difficulty, however, I am not able to see it. Can any of you see a image in the question??? – Shalom Vineeth Apr 18 '13 at 14:54
  • Yes I can see the image. And my sample data is almost the same as yours and it does alert the user as you wanted... – Siddharth Rout Apr 18 '13 at 14:56
  • Ah Ok.. I couldn' see the image.. Thanks a lot for the answer Sidharth.. I will try to play around and customize as per my needs. Thanks a ton. You are a rockstar!!! Thanks for the instant replies too..:) – Shalom Vineeth Apr 18 '13 at 15:01