I want to first say that this code works if anybody is looking for a userform code that will search there workbork when activated. It has one label, one textbox, one search button, and one done button. I know no one wants to read through this but, I forgot that i was going to be hiding some sheets and now i cant figure out how to fix this code to where it still searches my workbook and doesn't return an error when it gets to a hidden sheet. I know i need to change to mrCurrentCell to something else, i just dont know what that else should be.
Code:
Option Explicit
Dim mrCurrentCell As Range
Dim msaWorksheets() As String, msFirstAddress As String
Public Sub cmdDone_Click()
Unload SearchData
End Sub
Public Sub cmdSearch_Click()
Dim iPointer As Integer, iStartPointer As Integer
Dim sCurName As String
Dim ws As Worksheet
iStartPointer = 1
If Not (mrCurrentCell Is Nothing) Then
Set ws = ThisWorkbook.Sheets(mrCurrentCell.Parent.Name)
sCurName = mrCurrentCell.Parent.Name
Set mrCurrentCell = ws.Cells.FindNext(mrCurrentCell)
If Not (mrCurrentCell Is Nothing) Then
If mrCurrentCell.Address = msFirstAddress Then
Set mrCurrentCell = Nothing
Else
mrCurrentCell.Select
Exit Sub
End If
End If
For iStartPointer = 1 To UBound(msaWorksheets)
If msaWorksheets(iStartPointer) = sCurName Then
iStartPointer = iStartPointer + 1
Exit For
End If
Next iStartPointer
End If
For iPointer = iStartPointer To UBound(msaWorksheets)
Set ws = Sheets(msaWorksheets(iPointer))
Set mrCurrentCell = ws.Cells.Find(what:=txtboxInput.Value, LookIn:=xlValues, lookat:=xlPart)
If Not (mrCurrentCell Is Nothing) Then
msFirstAddress = mrCurrentCell.Address
Sheets(mrCurrentCell.Parent.Name).Select
mrCurrentCell.Select
Exit Sub
End If
Next iPointer
If mrCurrentCell Is Nothing Then MsgBox prompt:="Finish Search '" & txtboxInput.Value & "'", _
Buttons:=vbOKOnly + vbInformation, _
Title:="Search"
End Sub
Public Sub txtboxInput_Change()
Set mrCurrentCell = Nothing
cmdSearch.Enabled = txtboxInput.Value <> ""
End Sub
Public Sub UserForm_Initialize()
Dim iPtr As Integer
ReDim msaWorksheets(1 To ThisWorkbook.Sheets.Count)
For iPtr = 1 To UBound(msaWorksheets)
msaWorksheets(iPtr) = ThisWorkbook.Sheets(iPtr).Name
Next iPtr
Set mrCurrentCell = Nothing
cmdSearch.Enabled = False
End Sub