Vba userform custom search return a value

korhan

Board Regular
Joined
Nov 6, 2009
Messages
215
I am trying to make a userform which will return a certain value if it finds the value in the worksheet.
I just need the textbox value for me to wait till i hit ok to search the value. Also when I do erase I don't want it to do anything and if the value is not found I want a msgbox to pop up and say No value is found. And after I hit ok on msgbox I want search box to reset itself.
This seems really hard. I am not familiar with userforms so please try to explain as much as possible or take it easy on me.

Private Sub cmdcancel_Click() Unload Me
End Sub


Private Sub cmdok_Click()
If Me.txtlogoname.Value = "" Then
MsgBox "Please enter a logo name"
Me.txtlogoname.SetFocus
End If
End Sub


Private Sub cmdreset_Click()
Unload UserForm1
UserForm1.Show
End Sub


Private Sub txtlogoname_Change()
FindValue = Me.txtlogoname.Value
Cells.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
txtboxno.Text = ActiveCell.Offset(0, 4).Value
If FindValue.Value Then


On Error Resume Next
MsgBox "Logo does not exist"
UserForm1.Show
'Unload UserForm1
'UserForm1.Show
Me.txtlogoname.SetFocus
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdok_Click()

    [COLOR=darkblue]Dim[/COLOR] Found [COLOR=darkblue]As[/COLOR] Range  [COLOR=green]'Define a range variable[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Me.txtlogoname.Value = "" [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'Missing Logo entry[/COLOR]
        MsgBox "Please enter a logo name. ", , "No Logo Entry"
        Me.txtlogoname.SetFocus
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=green]'Search[/COLOR]
        [COLOR=green]'Set a range variable as the search result[/COLOR]
        [COLOR=darkblue]Set[/COLOR] Found = Cells.Find(What:=Me.txtlogoname.Value, _
                               LookIn:=xlFormulas, _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
                            
        [COLOR=green]'Test if the search was successful[/COLOR]
        [COLOR=darkblue]If[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=green]'No Match[/COLOR]
            MsgBox Me.txtlogoname.Value & vbLf & vbLf & _
                   "Logo does not exist. ", , "No Match Found"
            Me.txtlogoname.Value = ""
            Me.txtlogoname.SetFocus
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'Match found[/COLOR]
            txtboxno.Text = Found.Offset(0, 4).Value
            [COLOR=green]'Found.select[/COLOR]
            [COLOR=green]'Beep[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdreset_Click()
    Me.txtlogoname.Value = ""
    Me.txtboxno.Value = ""
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdcancel_Click()
    Unload Me
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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