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.
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.