Help on Search button (Minor Problem)

xc405

Board Regular
Joined
Jul 2, 2011
Messages
54
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top