Message Box

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 
Top