Message Box

thedeadzeds · May 14, 2013

Guys,

I have this code and it works fine. Is there a wasy to change it to show

If d6 = "Assistance" then message box to show "J6 must be completed"
If d8 = "Assistance" then message box to show "J8 must be completed"
If d10 = "Assistance" then message box to show "J10 must be completed"
If d12 = "Assistance" then message box to show "J12 must be completed"
If d14 = "Assistance" then message box to show "J14 must be completed"

and so on and so on

Thanks
Craig

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("sheet1").Range("D6").Value = "OK" ThenExit SubElseMsgBox ("You need to complete the task box")End IfEnd Sub

Simi · May 14, 2013

You can do this with an if / elseif block. How many cells do you have data in that you want to check? You said so on and so on.
if you have 20+ lines of data I would use a loop and a variable to check one row at a time. A sample workbook with dummy data in it would be helpful.

in the meantime see if this is along the lines you were looking for.

Code:

   With Worksheets("Sheet1")
      If Trim(UCase(.Range("D6"))) = "OK" And Trim(UCase(.Range("D8"))) = "OK" And Trim(UCase(.Range("D10"))) = "OK" And Trim(UCase(.Range("D12"))) = "OK" And Trim(UCase(.Range("D14"))) = "OK" Then
         Exit Sub
      ElseIf Trim(UCase(.Range("D6"))) = "ASSISTANCE" Then
         MsgBox ("J6 must be completed")
      ElseIf Trim(UCase(.Range("D8"))) = "ASSISTANCE" Then
         MsgBox ("J8 must be completed")
      ElseIf Trim(UCase(.Range("D10"))) = "ASSISTANCE" Then
         MsgBox ("J10 must be completed")
      ElseIf Trim(UCase(.Range("D12"))) = "ASSISTANCE" Then
         MsgBox ("J12 must be completed")
      ElseIf Trim(UCase(.Range("D14"))) = "ASSISTANCE" Then
         MsgBox ("J14 must be completed")
      Else
         MsgBox ("Not all data is ""OK"", Workbook not saved.")
         Cancel = True
      End If
   End With

Message Box

thedeadzeds

New member

Simi

New member