VBA activate workbook, msg box if cell contains certain text, close workbook

[willow1985]

I need help with a code. It has been a very long day and I know my code is a mess.

All I want the below code to do is open the workbook specified, which it does fine - we will call this Workbook A
Activate Workbook A
If Range O15 in Workbook A is "FALSE" Then
Activate Workbook B
On current row position: Range(Cells(Selection.Row, 22), Cells(Selection.Row, 22)).ClearContents
give a Msg box
If Range O15 is "TRUE" Then close workbook A
and Activate Workbook B

Currently it only opens the file and activates Workbook B.
Workbook A is showing as FALSE in O15

Any help would be greatly appreciated. Thank you!

Sub Verify_Form()
'
' Verify_Form Macro
'
Dim FORM As String
FORM = ActiveCell.Offset(0, 4).Range("A1").Value 'WORKBOOK A
Workbooks.Open FORM

'Activate WORKBOOK A

If Worksheets("CC").Range("O15").Value = "FALSE" Then

Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B
ActiveCell.ClearContents

MsgBox "CC Report incomplete. Please complete the report before closing out"

Else

'Close WORKBOOK A

Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B
    
End If
'
End Sub

 

[Micron]

Not sure I follow that but perhaps

Dim FORM As String
FORM = ActiveCell.Offset(0, 4).Range("A1").Value 'WORKBOOK A
Workbooks.Open FORM 'Activate WORKBOOK A
If Worksheets("CC").Range("O15").Value = "FALSE" Then
     Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B
     ActiveCell.ClearContents
     MsgBox "CC Report incomplete. Please complete the report before closing out"
Else
     Workbooks.Close FORM SaveChanges:= False
End If

Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B

You didn't say what to do after "give a Msg box" if value is false so I guessed. HTH

 

[willow1985]

Not sure I follow that but perhaps

Dim FORM As String
FORM = ActiveCell.Offset(0, 4).Range("A1").Value 'WORKBOOK A
Workbooks.Open FORM 'Activate WORKBOOK A
If Worksheets("CC").Range("O15").Value = "FALSE" Then
     Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B
     ActiveCell.ClearContents
     MsgBox "CC Report incomplete. Please complete the report before closing out"
Else
     Workbooks.Close FORM SaveChanges:= False
End If

Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B

You didn't say what to do after "give a Msg box" if value is false so I guessed. HTH

I receive an error on this line: Workbooks.Close FORM SaveChanges:= False

 

[willow1985]

I figured it out. FORM was referring to the entire directory of the file but in order to close I just needed to refer to the file name which I needed to define, so I defined that as FILE.

Thank you Micron for the help!

Application.ScreenUpdating = False
Dim FORM As String
Dim FILE As String
FORM = ActiveCell.Offset(0, 2).Range("A1").Value 'WORKBOOK A
FILE = ActiveCell.Offset(0, -21).Range("A1").Value & ".xls"
Workbooks.Open FORM 'Activate WORKBOOK A
If Worksheets("CC").Range("O15").Value = False Then
     Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate 'WORKBOOK B
     ActiveCell.ClearContents
     MsgBox "CC Report incomplete. Please complete the report before closing out"
Else

  Workbooks("Customer Concern - Warranty Request Log.xlsm").Activate
Workbooks(FILE).Close SaveChanges:=False

End If

Application.ScreenUpdating = True

 

[Micron]

Nice to see you solved it. I was deterred by your comment that your code is a mess and was going to suggest changes but thought different of it. Usually I'd create objects like workbooks if I was going to refer to them several times (such as getting at their sheets, opening, closing, etc.) rather than trying to define a wb with a string variable like FORM. Perhaps I should have said so and that might have saved you a bit of grief. I see that you stuck with the string variable approach, but hey, it works for you.

 

[willow1985]

Nice to see you solved it. I was deterred by your comment that your code is a mess and was going to suggest changes but thought different of it. Usually I'd create objects like workbooks if I was going to refer to them several times (such as getting at their sheets, opening, closing, etc.) rather than trying to define a wb with a string variable like FORM. Perhaps I should have said so and that might have saved you a bit of grief. I see that you stuck with the string variable approach, but hey, it works for you.

To be honest I am self taught with all of this and don't know of any other approach. I have a lot to learn. Thank you I will have to look into other approaches.