CODEWITHSUNDEEP
excelvbauserform
Bar-Tzur
Bar-Tzur

Reputation: 85

Conditionally unhide worksheets in Excel/vba

I am trying to unhide a group of worksheets if they meet a certain condition. This uses a user form, triggered by a command button, with selection options and another command button. The expected behavior is that once the selection has been made and the button has been pressed, all worksheets meeting the criteria will be unhidden. The Target word is present at different locations along the first row and all cells before it are empty on that row. Ideally, the process will scan each cell in the first row of each worksheet in the workbook until it comes across the Target, unhide the worksheet, then move on to the next worksheet to start the process over again until all worksheets with the workbook have been checked.

Upon activation of the command button on the user form I have the following:

Private Sub ContinueCommand_Click()
Dim Valid As Boolean, wks As Worksheet, c As Integer, actCol As Long    
    actCol = ActiveSheet.Range("A1").End(xlToRight).Column
    For Each wks In ActiveWorkbook.Worksheets
        For c = 1 To actCol
            If ActiveCell.Value = "Target" Then
                wks.Visible = xlSheetVisible
                Exit For
            End If
        Next c
    Next wks
    Valid = True
If Valid = True Then
Unload Me
End If
End Sub

I've borrowed from several sources, including here for using ActiveCell, determining if a value exists, unhidding worksheets, Finding values within a range, and searching for a string. Any help would be greatly appreciated.

Upvotes: 0

Views: 638

Answers (2)

Bar-Tzur
Bar-Tzur

Reputation: 85

I want to thank BruceWayne, Scott Craner, Stavros Jon, Darell H whom all helped me get closer to this answer. The final result looked like this: 

Private Sub ContinueCommand_Click()
Dim wks As Worksheet    
    For Each wks In ActiveWorkbook.Worksheets
       If Not IsError(Application.Match("Target", wks.Range("A1").End(xlToRight), 0)) Then
                wks.Visible = xlSheetVisible
            End If
    Next wks
Unload Me
End Sub

If anyone in the future has issues getting this to work, let me know and I will post a more complete version.

Upvotes: 0

Stavros Jon
Stavros Jon

Reputation: 1697

As I said in my comments there are some issues with the way you've chosen to implement this.

Your For c = 1 To actCol loop is not needed. This can be easily seen because c is not really used anywhere in the loop.

Let's assume your Target value is in wks.Range("A100") (the 1st row and 100th column).

Your code would then perform the exact same operation 100 times and would come up with the exact same result. That's what leads you to use Exit For, which is a bad practice.

If I understood your initial post correctly, if Target exists in a particular worksheet, then all cells before Target are empty.

If that's the case, the Target will either be in wks.Range("A1") or in wks.Range("A1").End(xlToRight). If it's not in either of these two cells then it doesn't exist at all in this particular worksheet, which would mean that the 1st row is completely empty. You don't need to check any more cells apart from these two.

Your code does not check whether Target is in wks.Range("A1").

Also your use of Application.Match, makes me believe that you have probably been misled by the common misconception that wks.Range("A1").End(xlToRight) is a range of cells starting from A1 and extending all the way to the last non-empty cell in the 1st row.

The truth is that wks.Range("A1").End(xlToRight) is a single cell rather than a range of cells. Selecting A1 and then pressing CTRL+right arrow, will show you exactly which cell it is.

I might be missing something, but according to your description in the initial post, I would do something like the following:

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    If sht.Range("A1").Value = "Target" Or sht.Range("A1").End(xlToRight).Value = "Target" Then
        sht.Visible = xlSheetVisible
    Else
        MsgBox "target was not found in " & sht.Name
    End If
Next sht

Upvotes: 1

Related Questions