Help on making sure all the data is filled in

[Zimmerman]

Can someone help me with this. I have a spreadsheet that our sales department fills out. They enter a lot of different data in cells all around the sheet which is important information that our production staff and our shipping staff needs, and sometime they fail to enter all of it which leaves questions. I would like to have the screen flash what cells need to be filled in before they save the sheet. Is it possible?

Thanks in advance
Jamie

 

[VoG]

Try this. Right click the Excel logo

just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Sheets("Sheet2").Range("A1, B2, C3")) < 3 Then
    Cancel = True
    MsgBox "You must complete A1, B2 and C3" & vbCrLf & "Save cancelled!", vbExclamation
End If
End Sub

Adjust the sheet name and ranges to suit and change the 3 to the number of cells that have to be filled.

 

[Zimmerman]

That worked perfect!! Thank you very much!!

Jamie

 

[Zimmerman]

One more question on this. Can I add if statements in a macro?

 

[VoG]

Yes you can. Care to elaborate?

 

[Zimmerman]

Sure, I have certain cells that if the sales answers yes to it then refers them to another cell. Meaning if they answer yes in cell (A1) thats a drop down box to the question "do we need to put a tag on each item?" it refers them to another cell (O12) asking them "what kind of tag is needed?". So to answer your question if they answer yes to question 1 then they need to answer question 2 before they close the sheet. But if they answer no to question 1 they shouldn't have to answer question 2 to close the sheet.

Right now I have formulas protected every where in this sheet and would love to try and get them into macro's so I can keep the sheet unlocked so I could allow the users to add hyperlinks to the customer specification sheets.

Does that make any sense to you?
Thanks again
Jamie

 

[VoG]

I think I understand - try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyRng As String
With Sheets("Sheet2")
    If .Range("A1").Value = "Yes" Then
        MyRng = "A1, B2, C3, O12"
    Else
        MyRng = "A1, B2, C3"
    End If
    If WorksheetFunction.CountA(.Range(MyRng)) < .Range(MyRng).Count Then
        Cancel = True
        MsgBox "You must complete " & MyRng & vbCrLf & "Save cancelled!", vbExclamation
    End If
End With
End Sub

 

[Zimmerman]

I received an error message that I pasted below in red. I've included what each cell represents.

AA18 - the question with a yes or no answer
c2 - a customer needs to be filled in here
j2 - a customer code needs to be filled in here
m2 - a product code needs to be filled in here
d51 - comes frm aa18 if the answer is yes they need to fill in a number here
k51 - comes frm aa18 if the answer is yes anothe number needs to be filled in here.

If aa18 is no then d51 and k51 are never seen and don't need any numbers in them.

Hopefully this helps you, because I need all the help I can get. But I have to admit, I'm new to this macro programming and so far I love it.




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyRng As String
With Sheets("Master")
If .Range("AA18").Value = "Yes" Then
MyRng = "AA18, ,C2, J2, M2, D51, K51"
Else
MyRng = "AA18, C2, J3"
End If
If WorksheetFunction.CountA(.Range(MyRng)) < .Range(MyRng).Count Then
Cancel = True
MsgBox "You must complete " & MyRng & vbCrLf & "Save cancelled!", vbExclamation
End If
End With
End Sub

 

[VoG]

You had an extraneous comma in the list defining the longer range. Try this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyRng As String
With Sheets("Master")
    If .Range("AA18").Value = "Yes" Then
        MyRng = "AA18, C2, J2, M2, D51, K51"
    Else
        MyRng = "AA18, C2, J3"
    End If
        If WorksheetFunction.CountA(.Range(MyRng)) < .Range(MyRng).Count Then
            Cancel = True
            MsgBox "You must complete " & MyRng & vbCrLf & "Save cancelled!", vbExclamation
        End If
End With
End Sub

 

[Zimmerman]

I do have to apologize for taking up so much of yoor time, but thank you for everything.

That worked out perfect except for one thing.
I filled in information in cell J2 and I answered yes in cell aa18 and tried to save it. It wouldn't let me just like it was supposed to because I didn't have all the info in all the cells, but it also told me that I needed to still fill in cells j2 and aa18 after I already did.

Do you have a solution for this?