VBA to Call if Sheet is visible

[menor59]

Team,

How can i run a CAll if sheet is Visible, If it isnt carry on??

'--------
'Clear Sheets from Database
'--------
Sub ClearFormulas()
  Dim i As Long
  For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
    With Sheets(i)
      .Unprotect
            .Range("A1, C10, C34:C38, D3, L34:L38, F3, L3:L200, N3:N200, P3:P200, R3:U200").ClearContents
      .Protect
    End With
  Next
'--------
'Clear TOC & Tabs Sheet
'--------

'--------INSERT CALL ClearTableCluster, and Call ClearTableReader Sheets is VISIBLE, If NOT Visible Carry on, If it is VISIBLE, DO the call and return here and Carry on

    Sheets("Site TOC").Select
    Sheets("Site TOC").Unprotect
            Sheets("Site TOC").Range("C7:C31, C34:C38, D7:D31, F3, F4, F7:F31, G7:G31, I7:I31, J7:J31, L7:L31, L34:L38, M7:M31, Z1, AA1, AC1:AC200").ClearContents
    Range("C7:L31, C34:L38").Interior.Color = vbWhite
    ActiveSheet.Tab.ColorIndex = 2
    Range("A1").Activate
    Sheets("Site TOC").Protect
End Sub

 

[rlv01]

Team,

How can i run a CAll if sheet is Visible, If it isnt carry on??

Without getting into the details of your code, the general way is to use the worksheet 'visible' property together with an IF statement.

If Worksheets("Sheet3").Visible = xlSheetVisible Then
        Call <subroutine name goes here>
End If

 

[menor59]

THats not working...

On those sheets i have a button that when i click it the macro clears it..

Just to confirm the sheets are now visible...But the code breaks..

I thought i would do this, but its breaking also..

If Worksheets("! Clusters").Visible = xlSheetVisible Then
    Dim T As ListObject:    Set T = ActiveSheet.ListObjects(1)
    With T.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        On Error Resume Next
        .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End If

If Worksheets("! Controllers").Visible = xlSheetVisible Then
    Dim T As ListObject:    Set T = ActiveSheet.ListObjects(1)
    With T.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        On Error Resume Next
        .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End If

If Worksheets("! Doors").Visible = xlSheetVisible Then
    Dim T As ListObject:    Set T = ActiveSheet.ListObjects(1)
    With T.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        On Error Resume Next
        .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End If

If Worksheets("! Aux Inputs").Visible = xlSheetVisible Then
    Dim T As ListObject:    Set T = ActiveSheet.ListObjects(1)
    With T.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        On Error Resume Next
        .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End If

If Worksheets("! Aux Outputs").Visible = xlSheetVisible Then
    Dim T As ListObject:    Set T = ActiveSheet.ListObjects(1)
    With T.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        On Error Resume Next
        .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End If

These Are basically the sheets i need to clear...

 

[rlv01]

THats not working...

On those sheets i have a button that when i click it the macro clears it..

Just to confirm the sheets are now visible...But the code breaks..

I thought i would do this, but its breaking also..

Code breaks for all kinds of reasons, but the question you asked in this thread was "How can I run a CALL if the sheet is visible". I infer from this post that maybe that's not a problem anymore. If you are now moving on to new problems you should probably start a new thread for those. Solving problems unrelated to the thread title may help you, but it won't help anyone else searching thread titles for help with their own problems.

Keep in mind when you say "that's not working" or "it breaks", there is no where to go for anyone who is not a mind reader. Instead you should describe how it goes wrong in detail. If the problem produces an error message, that information should ALWAYS be included. Reporting errors requires three things: error number, error description, and error location (the specific line of code where it occurs).