UserForm to Search Workbook

[BrendanDixon]

Hi All,

I have a userform called "Search" I have a textbox called "TextBox1"
and a button called "CommandButton1" I would like to have some programming that what ever I type in the text box and then press the button it will go to the cell with with the same text. while keeping the userform on top. if I click on the button again it will go to the next value of the text in the text box. My workbook has multiple sheets and I want the search to ignore Case differences.

Does anyone know how to do this and could you please help me. I do not know where to start with this.

 

[BrendanDixon]

bump

 

[al_b_cnu]

Hi,

Try the following:

1) to call the userform:

Search.Show vbModeless

Userform code:

Option Explicit
Dim mrCurrentCell As Range
Private Sub CommandButton1_Click()

With Cells
    If mrCurrentCell Is Nothing Then
        Set mrCurrentCell = .Find(TextBox1.Value, LookIn:=xlValues)
    Else
        Set mrCurrentCell = .FindNext(mrCurrentCell)
    End If
    If Not mrCurrentCell Is Nothing Then mrCurrentCell.Select
        
End With
    
End Sub

Private Sub TextBox1_Change()
Set mrCurrentCell = Nothing
CommandButton1.Enabled = TextBox1.Value <> ""
End Sub

Private Sub UserForm_Initialize()
CommandButton1.Enabled = False
End Sub

 

[BrendanDixon]

Hi al_b_cnu,

Your code works, but it only searches the active worksheet where I would like to search the whole workbook.

 

[BrendanDixon]

 

[al_b_cnu]

Hi Try this Userform code:

Option Explicit
Dim mrCurrentCell As Range
Dim msaWorksheets() As String, msFirstAddress As String
Private Sub CommandButton1_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:=TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    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:="Cannot find '" & TextBox1.Value & "'", _
                                        Buttons:=vbOKOnly + vbInformation, _
                                        Title:="Text not found"
End Sub

Private Sub TextBox1_Change()
Set mrCurrentCell = Nothing
CommandButton1.Enabled = TextBox1.Value <> ""
End Sub

Private 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
CommandButton1.Enabled = False
End Sub

 

[BrendanDixon]

Brilliant, That works almost 100%

Only thing is that it finds cells with the value and I would like it to maybe find cells that contain the string that is typed into the textbox

ie if I type "eldor" it will find "eldor" in all the cells including those which have something like "eldor engineering"

I thought this would be easy to change but looking at you code I do not understand it. Did you copy this from someone else or write this yourself?

Thanks

 

[al_b_cnu]

Hi,

Try changing this line:

Set mrCurrentCell = WS.Cells.Find(what:=TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)

to This:

Set mrCurrentCell = WS.Cells.Find(what:=TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)

All own work BTW ;¬)

 

[BrendanDixon]

it works!!!!


Thank you so much for all your effort. It is really appreciated.

Maybe one day I would be able to do the same ....I hope. I have been learning every day with this VBA.

But Thanks again!

 

[jonathann3891]

This works great but there is one small bug I hope you can help me iron out.

If you accidently click search again without changing the number, you will get an error.

1004

Select method of worksheet class failed

Is there a way to eliminate that error with an on error portion?