VBA : Userform Questions

  • Hi:

    I am in the process of developing a custom userform. I want it to appear very much like the standard "find" userform provided in Excel, however, than are a few exceptions. First, I want this userform to be capable of both next and previous searchdirections. Then, I want it to report the result of the find on the userform. Thus, far I my search starts fine but it will not report the value to the list box. Should I being using a listbox or a textbox?

    Anyway, here is a copy of the code:

    Sub listbox1_Click ()
    Dim foundvalue as Variant
    listbox1.value = foundvalue
    End Sub

    Is this code sufficient? HELP!!!


  • I just finished something similar to your request...(thanks mostly to help from this forum)

    I used a textbox, commandbutton, and combobox.
    The user entered the search word or number in the textbox, clicked the command button and then a list returned in the combobox that met the search criteria.

    Is that similar to your request?

  • HI Tudor30

    Try this - first set up your range - no doubt the values you are after are within a range i.e

    Private Sub UserForm_Initialize()
    Dim myrange As Range
    Dim counter As Integer

    Set myrange = Range("A1:A200") ' or named range etc

    ' now load up the listbox
    For Each c In myrange
    If c.Value = "" Then
    ListBox1.AddItem c.Value, counter
    counter = counter + 1
    End If

    End Sub

    this skips black cells and loads the data up into the listbox or combobox. I've found the = sign works all the time where <> sometime fails - I don't know why but this should load up your listbox.

    Hope this helps - Phil

  • Sorry - didn't read the question - thought you wanted to populate the list box.

    OK to search for a match you need to set a range and then decide on how to fire the code - I note you are using the click event which is OK - so

    your current code simply sets up a varant to be equal to the listbox value - That's fine but you need to display the found value. Normally the listbox value is not what you are after but say data within row's next to it - I'm assuming that's the case so this should work - similar to loading code - you run a loop looking for a match then load up the found value into another textbox

    dim myrange as range

    set myrange = range("A1:A200")

    for each c in myrange

    if c.value = listbox1.value then
    textbox1.value = listbox1.value & c.value.offset(1,0).value
    end if

    This should load up a textbox with both the listbox selected value & the next cell to the one that contained the data if was looking for -

    Hope this is what you wanted - Phil

  • This code will search all cells in a sheet and find the value in Textbox1. Click again and it will select the next occuremce

    Cells.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    CommandButton1.Caption = "Next &gt;&gt;"

    Is this anything like what you are looking for? I have a userform with Textbox1 and a button to click. It then works it's way throught the sheet at each click.

  • EDIT: This is better

    Commandbutton 1 titled Find

    Cells.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    CommandButton1.Caption = "Next &gt;&gt;"
    CommandButton2.Caption = "<< Previous"

    This code goes into Commandbutton2

    Cells.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
    , SearchFormat:=False).Activate
    CommandButton1.Caption = "Next&gt;&gt;"

    Have a textbox to type into (TextBox1)

    Type your Text in hit Find. Now you can search next or previous

  • Thanks Guys:

    Thanks you so much for your assistance, I really appreciate your help. Things are finally beginning to look up.

    I have implemented some of the code that you suggested and I am now able to initiate a search, retrieve both 2 descriptions and 2 codes to the userform simultaneously, and assign the found code values to their intended destination cells on the spreadsheet. But I have yet to get the application to perform a "next" or "previous" search.

    In my application the userform queries the end-user for input, performs a search which is designed to retrieved 2 corresponding codes along with their descriptions. This allows the user determine if he has obtained the appropriate code.

    Here is a copy of the code:
    Private Sub FindIt_Click()
    Dim foundCell As Range
    Dim foundValue As Range

    Set foundCell = foundValue
    'Find SIC Description and code along with NAICS description and code from input in "Find It" box
    200 Set foundValue = Sheets("Codes").Range("b773:b2638").Find(What:=TextBox5.Value, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    FindIt.Caption = "Next&gt;&gt;"

    'test to see if any matching text was found using an IS test
    If foundValue Is Nothing Then
    MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
    Exit Sub
    Else 'If text found, go to cell where text found

    TextBox8.Value = foundValue 'loads SIC Description

    TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code

    TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description

    TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code

    End If

    End Sub
    How do I format the "After" argument being that my values are being sent to the userform?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!