Mandatory Cells Before Saving; IF Statement: Message box problem

[Maik]

Hi there, I'm new in the forum. Hope some kind soul helps

I wrote this code without many idea of VBA thanks to the search tool:

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim msg As String
With Sheets("Form")
    If WorksheetFunction.CountIf(.Range("C5"), "Creation") <> 0 Then
        If .Range("C6").Value = "" Then msg = msg & "Select Customer Group" & vbNewLine
        If .Range("C7").Value = "" Then msg = msg & "Select Code" & vbNewLine
        If .Range("C11").Value = "" Then msg = msg & "Enter Company or Family Name" & vbNewLine
        If .Range("B12").Value = "First Name" And .Range("C12").Value = "" Then msg = msg & "Enter First Name" & vbNewLine
        If .Range("C13").Value = "" Then msg = msg & "Enter Address" & vbNewLine
        If .Range("C14").Value = "" Then msg = msg & "Enter Postal Code" & vbNewLine
        If .Range("E14").Value = "" Then msg = msg & "Enter City" & vbNewLine
        If .Range("C15").Value = "" Then msg = msg & "Select Country" & vbNewLine
        If .Range("D46").Value = "" Then msg = msg & "Enter contact information" & vbNewLine
        MsgBox msg, vbExclamation
        Cancel = True
    End If

    If WorksheetFunction.CountIf(.Range("C5"), "Modification") <> 0 Then
        If .Range("E5").Value = "" Then msg = msg & "blablablal...

..........same structure for other cases.......................................
...........................................................................................
...........................................................................................


    If Cancel Then
        MsgBox msg
    Else
           
    
End If
End With
End Sub

The thing is I continue receiving the MsgBox alert empty even if all the cells are filled in all the "cases" (Creation, modification, etc..) and it doesnt let me save the file.

I tried with an "ElseIf structure" without success, I suppose is something stupid

btw: i am using excel 2010

Regards,

m.

 

[pjmorris]

Hi Maik,

Welcome to the forum - I hope you get as much help and assistance as you need.

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim msg As String
With Sheets("Form")
    msg=""
    If WorksheetFunction.CountIf(.Range("C5"), "Creation") <> 0 Then
        If .Range("C6").Value = "" Then msg = msg & "Select Customer Group" & vbNewLine
        If .Range("C7").Value = "" Then msg = msg & "Select Code" & vbNewLine
        If .Range("C11").Value = "" Then msg = msg & "Enter Company or Family Name" & vbNewLine
        If .Range("B12").Value = "First Name" And .Range("C12").Value = "" Then msg = msg & "Enter First Name" & vbNewLine
        If .Range("C13").Value = "" Then msg = msg & "Enter Address" & vbNewLine
        If .Range("C14").Value = "" Then msg = msg & "Enter Postal Code" & vbNewLine
        If .Range("E14").Value = "" Then msg = msg & "Enter City" & vbNewLine
        If .Range("C15").Value = "" Then msg = msg & "Select Country" & vbNewLine
        If .Range("D46").Value = "" Then msg = msg & "Enter contact information" & vbNewLine
        MsgBox msg, vbExclamation
        if msg<>"" then
              Cancel = True
        endif
    End If

    If WorksheetFunction.CountIf(.Range("C5"), "Modification") <> 0 Then
        If .Range("E5").Value = "" Then msg = msg & "blablablal...

..........same structure for other cases.......................................
...........................................................................................
...........................................................................................


    If Cancel Then
        MsgBox msg
    Else
           
    
End If
End With
End Sub

The changes are to ensure msg is blank at the start of the testing msg="" and then to only set Cancel = to true if there was a message if msg<>"" then Cancel = true endif

At present whenever C5 contains any of your key words Cancel is set to True and prevents you saving!

Hope this helps.

Regards

 

[Snakehips]

Maik,

Does this help?

[FONT=monospace]Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
[/FONT]
msg = ""Dim msg As String
With Sheets("Form")    If WorksheetFunction.CountIf(.Range("C5"), "Creation") <> 0 Then        If .Range("C6").Value = "" Then msg = msg & "Select Customer Group" & vbNewLine        If .Range("C7").Value = "" Then msg = msg & "Select Code" & vbNewLine        If .Range("C11").Value = "" Then msg = msg & "Enter Company or Family Name" & vbNewLine        If .Range("B12").Value = "First Name" And .Range("C12").Value = "" Then msg = msg & "Enter First Name" & vbNewLine        If .Range("C13").Value = "" Then msg = msg & "Enter Address" & vbNewLine        If .Range("C14").Value = "" Then msg = msg & "Enter Postal Code" & vbNewLine        If .Range("E14").Value = "" Then msg = msg & "Enter City" & vbNewLine        If .Range("C15").Value = "" Then msg = msg & "Select Country" & vbNewLine        If .Range("D46").Value = "" Then msg = msg & "Enter contact information" & vbNewLine     
   If Not msg = "" Then MsgBox msg, vbExclamation        Cancel = True    End If    If WorksheetFunction.CountIf(.Range("C5"), "Modification") <> 0 Then        If .Range("E5").Value = "" Then msg = msg & "blablablal.............same structure for other cases.............................................................................................................................................................................................................................    If Cancel Then        If msg ="" Then MsgBox msg    Else               End IfEnd With [FONT=monospace]End Sub[/FONT]

Code is not easy to read!!??

Basically, at the start set msg = ""

Then for your two instances of display message use an if statement that checks if msg is still = ""
If it is it ignores Msg otherwise it displays.

 

[pjmorris]

Hi Maik,

I think Tony and I have got similar approaches to solutions to the problem, though Tony's msg="" needs to come after the declaration of msg.

Tony, I have no idea why you're code is elongated either - I've had problems when the > and < symbols are interpreted as something to do with HTML, but cant see how that applies here!

Regards

 

[Snakehips]

Peter,

Absolutely correct, case of fingers not connected to brain or brain operating at less than 100% or more likely, BOTH!!

Re the display of code. It was just a copy paste and small, albeit slightly flawed, edit of the original code. No < > symbols which as you say can confuse HTML if not separated by spaces.

 

[Maik]

It worked perfectly, thanks a millon both!!

 

[pjmorris]

Our pleasure - thanks for the feedback