Linking Userforms and Worksheets: Part VI
Just when you think you’ve had enough from
Part I: Setting Up the Form
Part II: Helper Procedures
Part III: Determining If the Form Needs to be Saved
Part IV: The Initialize Event
Part V: The ScrollBar
Carl asks how incorporate a search function into the userform. There are a lot of ways to do it, and most will make me change my current form too much (although they may be more intuitive methods). I present here a method that’s not so intrusive and I hope that the techniques are transferable.
I added the Frame (frmFind), TextBox (tbxFind), ComboBox (cbxFind), and CommandButton (cmdFind) to the above form. I want to give the user the ability to search on any field, so I add the field names to cbxFind. The user selects a field on which to search, then types all or part of the search data in the TextBox. When the CommandButton is clicked, the form goes to that record.
To fill the ComboBox, I added this line to the Initialize event:
Me.cbxFind.List = Application.Transpose(wksContacts.Range(”ColHeads”).Value)
I named a range on the worksheet that contains the column heads. Because the range is a row, I have to transpose the resulting array into a column before I put it in the ComboBox.
Next, I want to make sure that cmdFind is disabled until there is something to find. To do that, I add two event procedures and one special procedure to enable/disable the control. In design mode, I set the Tag property of cmdFind to “tgFind”.
Private Sub cbxFind_Change()
‘If the user has made a selection from the combobox and
‘typed something into the textbox, then enable the button
If Me.cbxFind.ListIndex > -1 And Len(Me.tbxFind.Text) > 0 Then
EnableControls “tgFind”
Else
EnableControls “tgFind”, True
End If
End Sub
Private Sub tbxFind_Change()
If Me.cbxFind.ListIndex > -1 And Len(Me.tbxFind.Text) > 0 Then
EnableControls “tgFind”
Else
EnableControls “tgFind”, True
End If
End Sub
Private Sub EnableControls(sTag As String, _
Optional bDisable As Boolean = False)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = sTag Then
ctl.Enabled = Not bDisable
End If
Next ctl
End Sub
The code behind the button looks like this:
Private Sub cmdFind_Click()
Dim lCol As Long
Dim rFound As Range
‘Which column to search - add one because ListIndex
’starts at zero
lCol = Me.cbxFind.ListIndex + 1
‘Find the first cell that contains the textbox text. I use xlPart
‘to search for partial data, but this could cause problems.
Set rFound = wksContacts.Columns(lCol).Find(what:=Me.tbxFind.Text, _
LookIn:=xlValues, _
lookat:=xlPart)
‘If a match was found, change the scrollbar to that record
‘otherwise show a new record
If Not rFound Is Nothing Then
Me.scbContact.Value = rFound.Row - 1
Else
Me.scbContact.Value = Me.scbContact.Max
End If
End Sub
franck:
Thx for your job, but could you please attached the workbook because I’m still in trouble. thx again
16 October 2004, 9:28 amalex:
i have folled the series and find your in put and help a great asset to all budding programers.
i would ask your indulgence on two points
1)To fill the ComboBox, I added this line to the Initialize event:
Me.cbxFind.List = Application.Transpose(wksContacts.Range(”ColHeads”).Value)
i could not get this to work and eventually placed the headers in colum A:A i now works.
2)can the header colum be moved to a different colum or another worksheet.
sorry
3)as i said the use of find is great but as per normal the question arrises where the found colum has the same name repeated at various places in the actual data. one thing i have not been able to do is get the programme to work with finding more than one occurance.
any help you can give on “FIND NEXT” would be appreciated.
alex
25 January 2005, 1:13 pmDeen:
very useful example. A newbie would certainly find this resourceful. Is there a way to delete the record from your example. Also, let’s say i wanted to add a new record to the list using a New button without scrolling all the way to the end..how do i go about doing dat?
any help from the above would be much appreciated.
Seasons greetings!!
19 December 2005, 2:06 pmDeen
ahmed:
could you please attached the workbook because I’m still in trouble. thx again
23 April 2008, 11:47 pmDick Kusleika:
There’s a download link here
http://www.dicks-blog.com/archives/2004/09/09/linking-userforms-and-worksheets-part-v/
24 April 2008, 9:54 amAngela Moss:
This example has been extremely useful. I am trying to use the code in part VI but get an error when I either complete the find text box or select an option from the find combo box it says tgFind has not been defined.
Any help would be great or can you let me have a copy of your spreadsheet with the find option included.
Many thanks
26 November 2008, 6:23 amAngela
Dick Kusleika:
Where did I get “tgFind”? I think that should be “cmdFind” or whatever the name of that commandbutton to the right of the find textbox is.
26 November 2008, 7:31 am