Solved VBA to Check if Cell is True before Proceeding

Microsoft Excel 2010 - complete product...
December 31, 2014 at 06:28:32
Specs: Windows 7 Service Pack 1
Hi,

I have a macro that you choose a workbook and it copies the data in to a master worksheet. I have now been asked to check a cell in the workbook and if a cell is "False" I need to add a message to check quantities.

I have found this code, but I am not sure how/where to put it in my main macro.

If Range("E7") = False Then
    MsgBox "Please verify tank quantities match before proceeding"
    Range("B4").Select
    Exit Sub
    End If


Main Code:

Sub NewMerge_Row()
    Dim FileNameXls As Variant
    Dim SummWks As Worksheet
    Dim ColNum As Integer
    Dim myCell As Range, Rng As Range
    Dim RwNum As Long, FNum As Long, FinalSlash As Long
    Dim ShName As String, PathStr As String
    Dim SheetCheck As String, JustFileName As String
    Dim JustFolder As String
    Dim destrange As Range
    Dim nxtRw As Integer

'Add Est Price Summary

 ShName = "Est Price Summary (2)"
    Set Rng = Range("A2:AG2")    '<---- Change

    'Select the files with GetOpenFilename
    FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _
                                              MultiSelect:=True)

    If IsArray(FileNameXls) = False Then
        'do nothing
    Else
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
         

        'Add a new workbook with one sheet for the Summary
        Set SummWks = ThisWorkbook.Sheets("Summary")

        'The links to the first workbook will start in row 1
        
        'RwNum = 3
        nxtRw = SummWks.Range("B" & Rows.Count).End(xlUp).Row + 1
'        nxtCol = Cells(3, Columns.Count).End(xlToLeft).Column + 1
        ColNum = 2
        
        For FNum = LBound(FileNameXls) To UBound(FileNameXls)
            'nxtCol = Cells(3, Columns.Count).End(xlToLeft).Column + 1
            ColNum = 2
            'RwNum = RwNum + 1
            nxtRw = SummWks.Range("B" & Rows.Count).End(xlUp).Row + 1
            FinalSlash = InStrRev(FileNameXls(FNum), "\")
            JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
            JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)
      

            'copy the workbook name in column A
            With Rng
            SummWks.Cells(nxtRw, ColNum). _
                Resize(, .Rows.Count).Value = JustFileName
            End With
    
      
            'build the formula string
            JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''")
            PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!"

    
    On Error Resume Next
            SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1))
            If Err.Number <> 0 Then
            With Rng
            SummWks.Cells(nxtRw, ColNum). _
                Resize(, .Rows.Count).Value = JustFileName
            End With

                MsgBox "Summary Sheet does not exist please contact Sandi S to add"
                Exit Sub
    
            Else

                For Each myCell In Rng.Cells
                    ColNum = ColNum + 1
                    SummWks.Cells(nxtRw, ColNum).Formula = _
                    "=" & PathStr & myCell.Address
              
                Next myCell
                    ColNum = 2
            End If
            On Error GoTo 0
        Next FNum
        ' Use AutoFit to set the column width in the new workbook
        SummWks.UsedRange.Columns.AutoFit
 

        MsgBox "The Summary is ready, save the file if you want to keep it"

        With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    End If
    
End Sub

Thank you,
Sandi

message edited by SandiS


See More: VBA to Check if Cell is True before Proceeding

Report •

#1
December 31, 2014 at 08:09:10
First, there is a comment in your NewMerge_Row macro that doesn't make sense:

        'Add a new workbook with one sheet for the Summary
          Set SummWks = ThisWorkbook.Sheets("Summary")

I don't see any code that adds a new workbook. The instruction that follows the comment assigns a reference to the Summary sheet object in the active workbook, but it doesn't add a new workbook.

As for your current question, where you place new code snippet depends on when you want to check the cell. In fact it's not even clear to me which workbook contains the cell to be checked.

"I have now been asked to check a cell in the workbook"

Are you checking E7 in the workbook that the user chooses via the GetOpenFilename dialog box or are you checking E7 in the workbook with the "Summary" sheet? Going one step deeper, it's not clear to me which sheet in which workbook holds the E7 that you want to check.

Basically, where you place that code will depend on when you want to check the cell, keeping in mind that since you are using "Exit Sub" if E7 is False you need to make sure that you aren't exiting the code and leaving a mess behind.

In other words, let's say you place that code snippet in the middle of a loop, checking E7 each time through the loop. As soon as E7 is False, the code will exit but the loop may have only looped half the number of times it should have. What's going to happen after the user fixes whatever they are supposed to fix and re-runs the code? Will the fact that parts of the original code have already run be an issue? Will there be remnants of data in your workbook from the first "partial run" that need to be cleaned up?

Those are just some of the things you need to keep in mind when you bolt something onto an existing piece of code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 31, 2014 at 08:51:43
Oh sorry I forgot to take that comment out I changed the code to use the sheet the macro button is stored in.

Yes I am using the Workbook the user chooses to check if E7 is False. I thought I could some how include this like the part of the code that checks to see if there is a sheet named Estimate Summary (2), but I can't figure out how to make it work?

    On Error Resume Next
            SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1))
            If Err.Number <> 0 Then
            With Rng
            SummWks.Cells(nxtRw, ColNum). _
                Resize(, .Rows.Count).Value = JustFileName
            End With

                MsgBox "Summary Sheet does not exist please contact Sandi S to add"
                Exit Sub
    
            Else


Report •

#3
December 31, 2014 at 11:12:56
✔ Best Answer
Once again I'm seeing some sloppiness on your side and it is causing confusion on my end, making me work harder than I want to. Keep in mind that I don't have your files to work with, so I usually have to create my own test environment before I can offer solutions. I don't want to just throw out some code without testing it, so I need to replicate your environment first.

You said:

"...like the part of the code that checks to see if there is a sheet named Estimate Summary (2)"

So I created a workbook with a sheet named Estimate Summary (2) and your code kept telling me that the sheet didn't exist. It wasn't until I dug deeper and found this line in your code that I figured out why:

ShName = "Est Price Summary (2)

Est Price Summary (2) <> Estimate Summary (2) which explains why I couldn't get your code to work properly. Please try to be as accurate as possible so I don't waste time tracking down errors that shouldn't even exist.

That said...

I modified what I found at this site...let me know if either of my suggestions below lead you in the right direction.

http://stackoverflow.com/questions/...

Based on the code snippet that you posted in #2, my first thought was that any instructions after the Else will only run if the Est Price Summary (2) sheet exists. Since there is no need to check E7 of that sheet until we know it exists, and assuming that E7 is the next thing that you want to check, the code should check E7 right after the Else.

As a test, I used a MsgBox to display the contents of E7...you would obviously want to modify that to suit your needs:

On Error Resume Next
            SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1))
            If Err.Number <> 0 Then
            With Rng
            SummWks.Cells(nxtRw, ColNum). _
                Resize(, .Rows.Count).Value = JustFileName
            End With

                MsgBox "Summary Sheet does not exist please contact Sandi S to add"
                Exit Sub
    
            Else
            'Build path to Est Price Summary (2)!E7 and display contents
                strInfoCell = PathStr & Range("E7").Address(, , xlR1C1)
                   MsgBox ExecuteExcel4Macro(strInfoCell)


After I tested that I had another thought: Why not go straight for the value in Est Price Summary (2)!E7?

'Build path to Est Price Summary (2)!E7 and get contents
     strInfoCell = PathStr & Range("E7").Address(, , xlR1C1)
        strE7_Val = ExecuteExcel4Macro(strInfoCell)

If you try to check that value directly - instead of checking for the sheet first and then checking E7 - it should error out if the sheet doesn't exist. If it errors out, display your "No Summary Sheet" msg. If it doesn't error out, you can check to see if strE7_Val = False and proceed from there.

This eliminates the need for the bolt-on to the code, making your macro a bit more efficient.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 31, 2014 at 11:55:37
Perfect ....thank you for your help!!

Sandi


Report •

Ask Question