Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Form (2003 SP3)

    Good afternoon

    I am trying to add a search facility to a WS to search for employee's names in Column A only, similar to the Excel search button I would like a next feature as well, having Googled and searched here I cannot find how to code these 2 buttons.

    As an aside, and if you do look at my example attached, what have I stupidly done to make my Combo not find my 'Staff' range?

    TIA etc.

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Form (2003 SP3)

    Are you not 're-inventing' the wheel by designing this dialog. What is the reason for doing all of this if there already is a search feature that can simply be manipulated by code to do what you need?

    PS: About the combo. If you paste your code into the UserForm_Initialize event, it will populate the combo when the form is activated. The Search and find next buttons can run code that is recorded by useing the Find/Replace dialog.
    Regards,
    Rudi

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003 SP3)

    Hi Rudi

    If I use the inbuilt search feature and type in somebody's name and I want to search in column a I have to go into other options and change it to column etc. I am putting this on our company server in a WB and I know that if I do not have something really simple it will cause an avalanche of phone callls from all over the building(s) and from our other offices.

    As I am the transport manager and not even in IT I just want something so simple that anybody could use it, hence the Combo to stop them having to even type anything in!!

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search Form (2003 SP3)

    The problem is not with the combobox but with your code:

    <pre>Private Sub CboEmployee_Change()
    Dim oCell As Range
    CboEmployee.Clear
    For Each oCell In ThisWorkbook.Names("Staff").RefersToRange
    CboEmployee.AddItem CStr(oCell.Value)
    Next

    End Sub</pre>


    Whenever you change (ie select from the cbo) you are telling VBA to clear it and then refilling it with the names. So none is ever selected.

    You could use something like this for the form:
    <pre>Option Explicit
    Dim sFirstAddress As String
    Dim rFind As Range
    Private Sub UserForm_Initialize()
    Dim oCell As Range
    CboEmployee.Clear
    For Each oCell In ThisWorkbook.Names("Staff").RefersToRange
    CboEmployee.AddItem CStr(oCell.Value)
    Next
    Me.CmbSearch.Enabled = False
    Me.CmbFindNext.Enabled = False
    End Sub
    Private Sub CboEmployee_Change()
    Me.CmbSearch.Enabled = True
    Me.CmbFindNext.Enabled = False
    End Sub
    Private Sub CmbSearch_Click()
    Dim sFind As String
    sFind = Me.CboEmployee.Value
    Set rFind = ActiveSheet.Range("A:A").Find(sFind, LookIn:=xlValues)
    If rFind Is Nothing Then
    MsgBox "'" & sFind & "' is not found"
    Else
    rFind.Select
    Me.CmbFindNext.Enabled = True
    sFirstAddress = rFind.Address
    End If
    End Sub

    Private Sub CmbFindNext_Click()
    Set rFind = ActiveSheet.Range("A:A").FindNext(rFind)
    If rFind.Address = sFirstAddress Then
    MsgBox "there are no more"
    Me.CmbFindNext.Enabled = False
    ElseIf Not rFind Is Nothing Then
    rFind.Select
    End If
    End Sub
    Private Sub cmdClose_Click()
    Unload Me
    End Sub</pre>


    It fills the cbo when the form is loaded and disables the search and findnext bottons. After an item is selected the search is enabled. The find next is enabled after an item is found.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Form (2003 SP3)

    Hi,

    OK...that does say a lot.

    See if this modified version of your sample file is doing what it should.
    Attached Files Attached Files
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003 SP3)

    Thanks Steve

    Thats great, it works perfect, I will now disect it so I know how to do it in future

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003 SP3)

    Cheers Rudi

    Thats great and much more condensed, I am have to do a lot of studying to see the difference between yours and Steve's version to see how they both work

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search Form (2003 SP3)

    When you are dissecting it and trying to understand, I suggest you try and add a "Find Previous" button that works similarly to the "Find Next". the coding is almost identical and would help you to get comfortable with modifying and adapting existing code (making sure it is enabled /disabled when appropriate)

    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Form (2003 SP3)

    Don't study up mine to hard as there are flaws in it that I noticed after posting the attachment. Mine will only work if the selected cell is in the A column. This of course cannot always be guarenteed when the search is performed. Since Steve has declared range based variables and then used those to drive the dialog, his is much more solid and reliable. In addition, Steve has added some nice user friendly touches by disabling the buttons when necessary to prevent code from firing when not intended.
    Happy studying...

    Cheers
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •