UserForm - Search/Update Form

[phendrena]

Hi there,

I've been looking at this thread where the OP has created a UserForm for searching records : http://vbaexpress.com/forum/showthread.php?t=23290

I'm trying (want/would like) to setup and create my own UserForm whereby the end user can search for a record and then edit and update the record. Would anyone be able provide me with some suggestions on how to go about doing this?

My current level of VBA knowledge doesn't go this far as such i'm stuck.
(This ones probably a little over my head tbh ).

I've attached the spreadsheet if it helps (the Search/Edit form would use the data in the YBS sheet).

Thanks,

[phendrena]

What I would be nice is if i could use just the one form.
I could expand the frmYBSCanx using Me.Width to show/hide extra commands to search the records and display the records in a listbox.
The user could then select data and this will then populate the "normal" form with the data, the user could then edit and update the data as needed.

[GTO]

...I've attached the spreadsheet...

Greetings Phendrena,

Didn't look at the other thread as of yet, but thought to courteously advise, ehhh... attachment...? I washed my eyeglasses to no avail, I still no see one...

Hope you chuckle and have a good day,

Mark

[phendrena]

Greetings Phendrena,

Didn't look at the other thread as of yet, but thought to courteously advise, ehhh... attachment...? I washed my eyeglasses to no avail, I still no see one...

Hope you chuckle and have a good day,

Mark

Let's try this one again eh?
Attachment now added.

Now, for a little more detail.
In the listbox for the search results I only need it to show the following :
Surname, Policy Number, Cancellation Date. Of course when the user clicks on an item in the listbox it should still copy all the data into the userform and not just the data shown in the list box.

[mdmackillop]

Have you tried the in-built DataForm?

[phendrena]

Have you tried the in-built DataForm?

I have now! I would still like to know how to code something similar in my own userform though as it won't be me doing to editing. Eventually I don't want the end user to have direct access to the raw data sheet.

I have the following code which populates the listbox based on the searching column A for either Yes or No :-

[vba]Private Sub cmdSearch_Click()
Dim foundArray As Variant
With Me
foundArray = ArrayOfMatchingRows(.txtActionedSearch.Text)
With .lbxResults
.Clear
If 0 < UBound(foundArray, 1) Then
.List = foundArray
End If
End With
End With
End Sub
Function ArrayOfMatchingRows(searchTerm As String) As Variant
Dim oneCell As Range
Dim ColumnCount As Long
Dim DataArray As Variant
Dim countOfFound As Long, i As Long

ColumnCount = 8
ReDim DataArray(1 To ColumnCount, 1 To dataRange.Rows.Count)

countOfFound = 0
For Each oneCell In dataRange().Columns(1).Cells
If LCase(oneCell.Value) = LCase(searchTerm) Then
countOfFound = countOfFound + 1
For i = 1 To ColumnCount
DataArray(i, countOfFound) = oneCell.Cells(1, i).Value
Next i
End If
Next oneCell

If 0 < countOfFound Then
ReDim Preserve DataArray(1 To ColumnCount, 1 To countOfFound)
Else
ReDim DataArray(0 To 0, 0 To 0)
End If

ArrayOfMatchingRows = Application.Transpose(DataArray)
End Function
Function dataRange() As Range
Set wsYBS = Worksheets("YBS")
With wsYBS.Range("A:A")
Set dataRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 8)
End With
End Function[/vba]

• If there is only one matching record it lists the result in one column rather than in a row, how can i avoid this?
• How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row?
• Can the listbox results then be sorted by date?

I've attached an updated workbook.

[GTO]

Greetings phendrena,


"If there is only one matching record it lists the result in one column rather than in a row, how can i avoid this?"

While I didn't muster the most 'artistic' code, this seems to work. Try replacing both ArrayOfMatchingRows() and dataRange() with this.

[vba]Function ArrayOfMatchingRows(searchTerm As String) As Variant

Dim _
wks As Worksheet, _
dataRange As Range, _
oneCell As Range, _
DataArray() As Variant, _
countOfFound As Long

'// As the Function dataRange() was only used herein, I simplified by //
'// including it's basic functionality here. I saw no reason thus far to //
'// resize it to include up to (inclusive) column "H", only to have to //
'// specify Column(1) later. //
Set wks = Worksheets("YBS")

'// Now, we'll set the range to look in (for "Yes" or "No") to just the //
'// first column. As you stated you would like to eventually hide the //
'// sheet alltogether, we'll make sure to be explicit. //
Set dataRange = wks.Range(wks.Cells(2, 1), wks.Cells(65536, 1).End(xlUp))

'// You mentioned a problem (observed) of if there's only one record //
'// returned. I think/believe this to be caused by building the array //
'// with the Column as the first dimension and the Row as the second //
'// dimension - followed by Transpose to correct the built array. //

'// Rather - let's try building the array like: MyArray(Row,Column). //
'// Unless I'm missing something (which is entirely too possible according //
'// to folks who know me well), I didn't see a reason to do it the first //
'// way, only to have to Transpose... //

ReDim DataArray(1 To dataRange.Rows.Count, 1 To 4)

countOfFound = 0

For Each oneCell In dataRange

If LCase(oneCell.Value) = LCase(searchTerm) Then

countOfFound = countOfFound + 1

'// Now, we just just save the values of the four columns in ea row //
'// by offsetting from the cell where we found No/Yes. //
DataArray(countOfFound, 1) = oneCell.Value
DataArray(countOfFound, 2) = oneCell.Offset(, 1).Value
DataArray(countOfFound, 3) = oneCell.Offset(, 2).Value
DataArray(countOfFound, 4) = oneCell.Offset(, 3).Value
End If
Next

ArrayOfMatchingRows = DataArray()

End Function[/vba]


"How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row? "

To the best of my knowledge, ColumnHeads only works correctly if you have RowSource set. That is to say, there will only be values placed in the header area if there's a RowSource to get the vals from.

In case you don't already know this: RowSource uses a string, like "A1:H29" to set the contents of the list box to a range on a sheet. In doing so, it assumes that the first row of the range is being used as a header row, and uses the values from the cells in the first row for the headings in the list box. Hope that made sense, but in short - as you are using an array to fill the list box... no dice as to filling the column headings.

"Can the listbox results then be sorted by date?"

Yes. You can sort the returned ArrayOfMatchingRows(). I would suggest searching the KB for keywords: BubbleSort, Sort an Array

Hope this helps ,

Mark

[phendrena]

Greetings phendrena,

~ snip ~

"How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row? "

To the best of my knowledge, ColumnHeads only works correctly if you have RowSource set. That is to say, there will only be values placed in the header area if there's a RowSource to get the vals from.

In case you don't already know this: RowSource uses a string, like "A1:H29" to set the contents of the list box to a range on a sheet. In doing so, it assumes that the first row of the range is being used as a header row, and uses the values from the cells in the first row for the headings in the list box. Hope that made sense, but in short - as you are using an array to fill the list box... no dice as to filling the column headings.

"Can the listbox results then be sorted by date?"

Yes. You can sort the returned ArrayOfMatchingRows(). I would suggest searching the KB for keywords: BubbleSort, Sort an Array

Hope this helps ,

Mark

Hi,

Many thanks for the reply and the help.

"Can the listbox results then be sorted by date?"

I've had a look on the KB for the bubblesort but i'm a little unclear on how to use this code within my own code. Would you be able to provide any suggestions?

[vba]Sub BubbleSort(MyArray As Variant)

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
End Sub
[/vba]

"How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row? "

I've added the following into Private Sub UserForm_Initialize()
[vba]lbxResults.ColumnCount = 4
lbxResults.RowSource = ("YBS!A11")[/vba]

This does add the column headers, however when i now click search (after replacing both ArrayOfMatchingRows() and dataRange() ) I now get a Runtime Error 'Unspecified Error'. The debug then highlights the following :
[vba]Private Sub cmdSearch_Click()
Dim foundArray As Variant
With Me
foundArray = ArrayOfMatchingRows(.txtActionedSearch.Text)
With .lbxResults
.Clear
If 0 < UBound(foundArray, 1) Then
.List = foundArray
End If
End With
End With
End Sub[/vba]
Do you have any suggestions for getting around this?

BTW... i should mention that i am using Excel '97 for this spreadsheet.

Thanks,

[GTO]

Hi,

Many thanks for the reply and the help.

You are most welcome :-)

I've had a look on the KB for the bubblesort but i'm a little unclear on how to use this code within my own code. Would you be able to provide any suggestions?

You inserted the example. For anyone to answer, we have to see how your code is 'approaching' this. Please attach your workbook, with the revisions made thus far. That way, we (you and I, or you and anyone who latches onto a solution) can be 'on the same page' with you and the development thus far. Make sense?

"How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row? "

I've added the following into Private Sub UserForm_Initialize()

[vba]lbxResults.ColumnCount = 4
lbxResults.RowSource = ("YBS!A11")[/vba]

This does add the column headers, however when i now click search (after replacing both ArrayOfMatchingRows() and dataRange() ) I now get a Runtime Error 'Unspecified Error'. The debug then highlights the following :

[vba]Private Sub cmdSearch_Click()
Dim foundArray As Variant

With Me

foundArray = ArrayOfMatchingRows(.txtActionedSearch.Text)

With .lbxResults
.Clear
If 0 < UBound(foundArray, 1) Then
.List = foundArray
End If
End With
End With
End Sub[/vba]

Do you have any suggestions for getting around this?

My dear brother,

You CANNOT (to the absolute best of my knowledge) load a listbox through both .RowSource and another manner (be it: .List, .AddItem).

What I was trying to say was that the column headers, while awfully spiffy, are, in my opinion, not worth the effort - unless, the nature of the project lends itself to them. That is to say, that if it is a natural selection to use a sheet range for the contents of the listbox, then great! If not, then unless you REALLLLY want the column heads, make do with labels. As I recall (not checking at the moment, but fairly certain based on Transpose elimination), you were using the results of a built array to fill the listbox. If I am correct thus far, this would mean (again, to the best of my knowledge) that you would have to 'dump' the array onto a sheet (immediately below a 'header' row) and then use .Rowsource to load the listbox.

By way of explanation: Let's say you already had a 'table' so-to-speak. Lets say the table is from cell A1 to D4, so it is 4x4 in size. Now if cells A1, B1, C1 and D1, are "Last Name", "First Name", "Height", "Weight", then you could easily use .RowSource to fill the listbox, as well as the column heads. But if you use .RowSource, it is to fill the entire listbox. The Column Heads are secoondary. Does that explain it better?

BTW... i should mention that i am using Excel '97 for this spreadsheet.

While I no longer have access to Excel97, I would need to see the workbook, as currently written, to see if this causes any additional errors.

Hope to help,

Mark

[phendrena]

Hi Mark,
Thanks for the continuing support with my ever ongoing and expanding query and having patience with my restricted level of VBA knowledge (which is expanding)!
I think we?ll file the column headers under the section "would be nice, but not critical" for the moment.
Let?s get everything else sorted out first.
Going back to sorting the results by date,

I've had a look on the KB for the bubblesort but i'm a little unclear on how to use this code within my own code. Would you be able to provide any suggestions?

You inserted the example. For anyone to answer, we have to see how your code is 'approaching' this. Please attach your workbook, with the revisions made thus far. That way, we (you and I, or you and anyone who latches onto a solution) can be 'on the same page' with you and the development thus far. Make sense?

All I have done is added the code as an additional sub into the form code.
I?m stuck with where to actually call the routine from / how best to use it.

I've attached the most current version of the workbook.

Thanks,