Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro that Reads Table logic (Excel 2000)

    Hello Woody's

    I am trying to make a macro that checks errors before saving the workbook.

    My Explanation;

    Each row is a different case

    Limits,
    Col. D to G (may be expanded)

    I thought that I could write if statements for errors along the case row. If the logic finds an error give the value
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Welcome to Woody's Lounge!

    When should the code fire? If any of the values in columns D through G is TRUE, or if all (non-blank) values are TRUE?

  3. #3
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Hello Hans,
    I have recorded a save macro,

    My plan is to add the code into the macro or call it up,
    The trigger would for now be the button I put on the sheet

    Click it and the code fires

    Actuallly reading some forms on how to do that right now.

    Allen

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    OK, but when should the code issue a warning? If any of the cells in columns D:G in a row contains TRUE or if all non-blank cells in columns D:G in a row contain TRUE?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Assuming that you want to display a message if any value is TRUE, assign this macro to the command button:

    Sub Check()
    ' First row to look at is 3 - adjust if needed.
    Const MinRow = 3
    ' Columns to look at are D to G - adjust if needed.
    Const MinCol = 4
    Const MaxCol = 7

    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long

    lngMaxRow = Range("A63336").End(xlUp).Row
    For lngRow = MinRow To lngMaxRow
    For lngCol = MinCol To MaxCol
    If Cells(lngRow, lngCol) = True Then
    If Cells(lngRow, 1) = True Then
    MsgBox Cells(lngRow, 3), vbCritical, _
    Cells(lngRow, 2)
    Exit Sub
    ElseIf MsgBox(Cells(lngRow, 3) & vbNewLine & _
    "Continue anyway?", vbQuestion + vbOKCancel, _
    Cells(lngRow, 2)) = vbCancel Then
    Exit Sub
    End If
    End If
    Next lngCol
    Next lngRow
    End Sub

  6. #6
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Hans,

    Eash case is a Row, in range D:G, if there is a True any where in the range, there is an error

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    OK, see my other reply - I posted a macro that should do what you want.

  8. #8
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Thank you!!

    I ran it through and it works. I then added it to the beginning of my save code. as long as the ERROR CHECKER sheet is selected.
    the Macro works

    can the code know that it will look at the error checker sheet when ran.

    I looked up a way to fix this worksheet.select right at the start.
    is this the most efficient fix

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    This version acts on the ERROR CHECKER worksheet even if it is not the active sheet (and without selecting it):

    Sub Check()
    ' First row to look at is 3 - adjust if needed.
    Const MinRow = 3
    ' Columns to look at are D to G - adjust if needed.
    Const MinCol = 4
    Const MaxCol = 7

    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long

    Set wsh = Worksheets("ERROR CHECKER")
    lngMaxRow = wsh.Range("A63336").End(xlUp).Row
    For lngRow = MinRow To lngMaxRow
    For lngCol = MinCol To MaxCol
    If wsh.Cells(lngRow, lngCol) = True Then
    If wsh.Cells(lngRow, 1) = True Then
    MsgBox wsh.Cells(lngRow, 3), vbCritical, _
    wsh.Cells(lngRow, 2)
    Exit Sub
    ElseIf MsgBox(wsh.Cells(lngRow, 3) & vbNewLine & _
    "Continue anyway?", vbQuestion + vbOKCancel, _
    wsh.Cells(lngRow, 2)) = vbCancel Then
    Exit Sub
    End If
    End If
    Next lngCol
    Next lngRow
    End Sub

  10. #10
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Hans,

    Thank youfor your time,

    One issue with adding this into a peice of code it that when the code finds an error it Exits the Sub,

    Is there a way to end the code?

    Exit sub does not work because I have it in a seperate module. I can place the code with in the main save code, instead of using Call Macro name
    but is there a way to end the VB code right in the current module?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    You could change it to a function:

    Function Check() As Boolean
    ' First row to look at is 3 - adjust if needed.
    Const MinRow = 3
    ' Columns to look at are D to G - adjust if needed.
    Const MinCol = 4
    Const MaxCol = 7

    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long

    Set wsh = Worksheets("ERROR CHECKER")
    lngMaxRow = wsh.Range("A63336").End(xlUp).Row
    For lngRow = MinRow To lngMaxRow
    For lngCol = MinCol To MaxCol
    If wsh.Cells(lngRow, lngCol) = True Then
    If wsh.Cells(lngRow, 1) = True Then
    MsgBox wsh.Cells(lngRow, 3), vbCritical, _
    wsh.Cells(lngRow, 2)
    Exit Function
    ElseIf MsgBox(wsh.Cells(lngRow, 3) & vbNewLine & _
    "Continue anyway?", vbQuestion + vbOKCancel, _
    wsh.Cells(lngRow, 2)) = vbCancel Then
    Exit Function
    End If
    End If
    Next lngCol
    Next lngRow
    Check = True
    End Function

    This function will return True if it ran to the end, i.e. if no errors were found or if the user selected to continue, and False if an Exit Function was executed. You can test the return value in your main code:

    If Check = True Then
    ' Finished normally
    ...
    Else
    ' Check found an error
    ...
    End If

  12. #12
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that Reads Table logic (Excel 2000)

    Thanks alot for the help,

    Al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •