+ Reply to Thread
Results 1 to 3 of 3

vba excel - macro executes only when sheet is active

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    vba excel - macro executes only when sheet is active

    A function creates banded rows on another worksheet. The routine only seems to work when the sheet is active, so I believe the statement nomenclature is incorrect. I've tried setting the worksheet reference but the function does not work regardless if I set it within the function, within the sub, or both. Can someone point me in the right direction on how to deal with calling a function? I want the sub to work regardless what sheet or workbook the user has active.
    Public Sub Band()
        Set wb = ThisWorkbook
        Set ws = wb.Sheets(1)
        With ws
             fRow = .Cells(Rows.Count, 1).End(xlUp).Row
             eRow = fRow
                   BandRows_Fm 54, eRow
        End With
    End Sub
    
    Private Sub BandRows(StartRow As Integer, EndRow As Integer)
        
        Range("B" & StartRow & ":J" & EndRow).Interior.ColorIndex = xlNone
            
        For i = StartRow To EndRow
            If Not Rows(i).Hidden Then
                nohide = nohide + 1
                    If Not nohide Then
                        Range("B" & i & ":J" & i).Interior.Color = RGB(225, 225, 225)
                    End If
             End If
        Next i
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: vba excel - macro executes only when sheet is active

    This,
    Range("B" & StartRow & ":J" & EndRow).Interior.ColorIndex = xlNone
    will refer to the active sheet, as will Rows here.
            If Not Rows(i).Hidden Then
    Try passing the worksheet ws as well as StartRow and EndRow.
    Public Sub Band()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim eRow As Integer
    
        Set wb = ThisWorkbook
        Set ws = wb.Sheets(1)
        With ws
            fRow = .Cells(Rows.Count, 1).End(xlUp).Row
            eRow = fRow
            BandRows ws, 54, eRow
        End With
    End Sub
    
    Private Sub BandRows(ws As Worksheet, StartRow As Integer, EndRow As Integer)
    
        With ws
            .Range("B" & StartRow & ":J" & EndRow).Interior.ColorIndex = xlNone
    
            For i = StartRow To EndRow
                If Not .Rows(i).Hidden Then
                    nohide = nohide + 1
                    If Not nohide Then
                        .Range("B" & i & ":J" & i).Interior.Color = RGB(225, 225, 225)
                    End If
                End If
            Next i
        End With
    
    End Sub
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - macro executes only when sheet is active

    Thank you Norie. Exactly what I was after.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel VBA - Creating a macro that inserts the active sheet name inside another sheet cell'
    By brandonstockwell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2014, 12:29 AM
  2. Macro to go back to the last active sheet and hide the active sheet
    By yatahaze in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2014, 08:18 AM
  3. macro to send an email with an active excel sheet attached
    By megtoma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2014, 11:37 PM
  4. Macro with reference to active chart on active sheet
    By bhavikpatel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2014, 10:43 AM
  5. Macro to Print active sheet as PDF to Active workbook and customize name
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 08:52 PM
  6. Excel 2007 Macro that Executes Action Periodically Until Condition is Met
    By jmr.noun in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-25-2012, 03:41 AM
  7. Vba Code Executes After Copying Sheet
    By RichG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2007, 09:09 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1