Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Applying code being used in a worksheet to the entire workbo

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    I'm trying to apply code currently being used for individual worksheets to the whole workbook. If the worksheet is not active and the below conditions are met, then I get a runtime error. Could you take a look at my code and see what I need to change and where I should put the code:


    Private Sub Worksheet_Calculate()

    Dim lLastRow As Long, Data1, ColE, ColG, ColH, COlI, lRow As Long
    Dim shtThis As Worksheet, bThisRow As Boolean
    Dim ranj As String

    Data1 = Cells(1, 4).Value

    Set shtThis = ActiveSheet

    With Cells(5, 1).CurrentRegion
    lLastRow = .Rows.Count + .Row - 1
    End With


    For lRow = 5 To lLastRow
    ColE = Cells(lRow, 5).Value
    ColF = Cells(lRow, 6).Value
    ColG = Cells(lRow, 7).Value
    ColH = Cells(lRow, .Value
    COlI = Cells(lRow, 9).Value

    bThisRow = False 'pay
    cThisRow = False 'pay nothing


    Select Case ColG
    Case "DDD"
    If ColH = Data1 Or COlI = Data1 Then _
    cThisRow = True
    Case "OO"
    If ColE = Data1 Then _
    bThisRow = True
    Case "RRR"
    Select Case ColF
    Case "X"
    If COlI = Data1 Then _
    cThisRow = True
    Case "O"
    If ColH = Data1 Then _
    cThisRow = True
    End Select
    Case "II"
    Select Case ColF
    Case "X"
    If COlI = Data1 Then _
    bThisRow = True
    Case "O"
    If ColH = Data1 Then _
    bThisRow = True
    End Select
    Case "RKK"
    Select Case ColF
    Case "X"
    If ColH = Data1 Then _
    bThisRow = True
    Case "O"
    If COlI = Data1 Then _
    bThisRow = True
    End Select
    Case "BOX"
    Select Case ColF
    Case "X"
    If ColH = Data1 Then _
    cThisRow = True
    Case "O"
    If COlI = Data1 Then _
    cThisRow = True
    End Select
    End Select

    With shtThis.Rows(lRow)
    If bThisRow Then
    'Cells(lRow, 12).Select
    Range("A" & lRow & ":L" & lRow).Select
    'ranj = "A" & lRow & ":L" & lRow
    ' Object.Range (ranj)
    With Selection.Interior
    .ColorIndex = 3
    .Pattern = xlSolid
    End With

    'Else
    ' With .Interior
    ' .ColorIndex = xlNone
    ' .Pattern = xlNone
    ' End With
    End If

    If cThisRow Then
    Range("A" & lRow & ":L" & lRow).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    End If

    End With
    Next


    End Sub


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi BenNV


    Right click on the Excel icon, top left next to "File", select "View Code" and paste you code in here:

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you. I've done that but it appears some of the properties may need to be changed as this does not apply for workbooks. Can you help?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone help on this, please?

    I am trying to make the conditions apply to the whole workbook for each worksheet, even if the worksheet is inactive.

    I put the code where Dave suggested but think certain areas of the code need changing.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Where and what errors are you recieving?
    Email your Wb if you want. TsTom@hotmail.com
    Tom

    [ This Message was edited by: TsTom on 2002-04-10 01:42 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ben
    I sent this to the wrong e-mail address and am not sure if yours is correct either.
    Anyway,

    Try this and see if it solves your problem.
    Replace the following lines of code in your Workbook_SheetCalculate event

    Replace:
    Dim shtThis As Workbook, bThisRow As Boolean
    with
    Dim shtThis As Worksheet, bThisRow As Boolean

    Replace:
    Set shtThis = ActiveWorkbook
    with
    Set shtThis = sh

    Let me know if this works.
    Thanks,
    Tom

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, thanks. But only seems to work for the active sheet, not any of the other inactive sheets, ie. if a sheet is inactive and the conditions meet then nothing happens.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may be missing your objective here, Ben.
    You want all sheets to do what?
    I do not know exactly how your workbook functions. Do you want the same process which is occuring in the active sheet to duplicate in every sheet?
    If that is the case then that would not be a problem...
    Sorry if I missed the mark here.
    Let me know,
    Tom

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, in all of the sheets in the workbook, I need the same process to occur. So say the conditions are met in an inactive worksheet then I need the cells to highlight. At the moment it only works on the active worksheet. So, should the conditions that I specified be met in an inactive worksheet, nothing happens.

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the former is the case then do this:

    Replace:
    Set shtThis = sh
    With:
    For Each shtThis In Worksheets

    Right above End Sub add this:
    Next

    This will loop through every sheet in the workbook everytime any sheet calculates.

    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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