Free Microsoft Excel 2013 Quick Reference

Show search results in the list box of the user form

Hello everybody

I have posted this on one of the excel forums but did not get any luck so far.

I am fighting the search results to appear in the listbox in the user form (search criteria are also part of the user form)
File content is the following:
Sheet 1 contains Button 1 which initiates a simple user form.
Sheet 2 contains data range with columns: Account Number, Account Name, Account Code ets.

What I cant figure out is a makro which would do the following: every time I input Account number or Account Name or Account Code (any of those or if account number and account code are inputted - use both as a search criteria) and hit the SEARCH button the list box within the userform is being populated with search results (entire row). Initial database for search is in the Sheet name "Detail"
This might be a hard one but I am quite sure some of you out there can handle it.

I would appreciate any help or suggestion.

Thank you

p.s. Using Windows XP with office 2007


Post your answer or comment

comments powered by Disqus
Hi,

Attached is my draft of my project. I've an error of after clicked search and find all with the sales id more than quantity of 6. VBA shows "run-time error 9 , subscript out of range". I wish to extend the numbers of row in the list box, probably i need at least 20 rows. Thx

Afternoon all,

I have been searching for some help on here but cant quite find what i need.

I was hoping someone may be able to help me. I would like to be able to insert a date into a cell in run a macro through a button that will match the date and display the information in column C,D,E and F in a list box.

Any body have any idea's i'm very close to gauging my eyes out with a spoon.

Thanks for taking the time to read this.

Mart.


Dear Members

In my user form " I AM USING COMBO BOX, FOR FILLING TIME"

Error is :

a. Time is showing in "DECIMAL FORMAT ( Example: 9:00 showing as "0.375") in the text box of user form

b. I am using "ROW SOURCE" only from another worksheet, as the source for combo box

Regards
jwala

BACKGROUND
============
I've been working on an Invoice Creator through a system of userforms so that we can create invoices, store them into a database and then view customer accounts to see what invoices have been settled.

So when you click on View an Account, you select a customer from the dropdown list.
This puts the relevant CustomerID into the cell, "Search_CustID" (E12) on the setup page.

Then when you click the "View account" button it brings up the ShowAccount userform.

I've got a multipage element in there and it first loads up on the Customer details tab and has all their details. There's also a page "Total Invoices (Num)" where Num shows the total number of invoices. This works by searching the InvoiceDB sheet for all invoices from the CustomerID and pasting them into the temporary spreadsheet "InvoiceSearch" and then doing a number of rows count to get the results. The same is then repeated for the tab "Outstanding Invoices" which are basically, invoices that have not been settled yet. So it uses "InvoiceSearch" and searches for any that are listed under PAID as "NO" and copies them to this sheet and displays the number of results.

If you click onto "Total Invoices (Num)" there is a list box which links back to the InvoiceSearch sheet. It puts all the reference numbers in the list box and when you click on the reference number, it populates the other fields so you can get the details on the invoice.

If you click onto the "Outstanding Invoices (Num)", there is a similar set up, but it shows the total amount of money outstanding over all the invoices. If there are none outstanding, there will be none to choose.

THE PROBLEM
============
This works perfectly... when there is a mixture of outstanding and paid invoices (try clicking on the account for Stifford Clays and you will see it working fine).
If all invoices are paid, for some reason, in the total invoices multipage, there are no results to choose from, from the listbox, even though when you click on the InvoiceSearch spreadsheet, there are results. You can see this if you try the acocunt for Ramsden Hall or Millhouse School.

It has to be something to do with my coding but I'm pulling my hair out over this... so I'd appreciate if someone could help me, thank you sooooo much.

Bonjour! My code, as posted below, cutterntly open up a userform, I enter in a name or number, and it returns associated name or number and provider care level and puts that into a listbox. When clicking on the item in the listbox, it enters the found data into the active cell. I can't figure out 2 things:
1 - A For-Next that will find if there is more than 1 Smith
2 - List all the results in the list box with: name, number, and PCL.

I've got the basics running, it's the advanced stuff I'm still learning!

Thanks in advance,

Eric


	VB:
	
 
Private Sub Search_Ok_Click() 
    Dim FindResult As Range 
    Dim PCL As String 
     
    Search_Results.Clear 
     
    Set FindResult = Sheet1.Cells.Find(What:=Input_Search, _ 
    After:=ActiveCell, _ 
    LookIn:=xlFormulas, _ 
    LookAt:=xlPart, _ 
    MatchCase:=False) 
     
     
    If Application.WorksheetFunction.IsText(FindResult) Then 
        If FindResult.Offset(, -1).Text = "P" Then 
            PCL = "PCP" 
        ElseIf FindResult.Offset(, -1).Text = "A" Then 
            PCL = "ACP" 
        End If 
        Search_Results.AddItem FindResult.Offset(, -2).Text 
         'msg = FindResult.Text & vbNewLine
         'msg = msg & PCL & vbNewLine
         'msg = msg & FindResult.Offset(, -2)
         'Unload Search_Database_Userform
         'MsgBox msg
        Exit Sub 
    ElseIf Application.WorksheetFunction.IsNumber(FindResult) Then 
        If FindResult.Offset(, 1).Text = "A" Then 
            PCL = "ACP" 
        ElseIf FindResult.Offset(, 1).Text = "P" Then 
            PCL = "PCP" 
        End If 
        Search_Results.AddItem FindResult.Offset(, 2).Text 
         'msg = FindResult.Text & vbNewLine
         'msg = msg & PCL & vbNewLine
         'msg = msg & FindResult.Offset(, 2)
         'Unload Search_Database_Userform
         'MsgBox msg
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi there

I am very new in VBA coding in excel and this forum has helped me a lot with the creation of userform.
I have created a userform that can allow users to input, search and display data in the same userform.

Everything seems to work well except that I am unable to display the search results in the listbox. I was made to understand that the listbox only allows up to 10 columns only whereas i have 26 columns. i managed to use the coding that was posted in this forum, however, it shows error (run time error:9 subscript out of range) in the below code:

ReDim vntData(1 To lngRowCount, 1 To MaxCol)

The coding that in my userform are as below:


	VB:
	
 FindAll() 
    Dim rng As Range 
    Dim strFind As String 'what to find
    Dim rFilter As Range 'range to search
    Set rFilter = Worksheets("Database").Range("a7", Range("aa65536").End(xlUp)) 
    Set rng = Worksheets("Database").Range("a6", Range("a65536").End(xlUp)) 
    strFind = Me.cboPSC.Value 
    With Worksheets("Database") 
        If Not .AutoFilterMode Then .Range("a7").AutoFilter 
        rFilter.AutoFilter Field:=1, Criteria1:=strFind 
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible) 
         
        Set rng = .Range("A7").CurrentRegion.SpecialCells(xlCellTypeVisible) 
        vntData = GetRangeData(rng, 27) 
        ListBox1.Clear 
        ListBox1.ColumnCount = 27 
        ListBox1.List = vntData 
    End With 
End Sub 
Private Function GetRangeData(Data As Range, MaxCol As Long) As Variant 
     
     
    Dim lngRowCount As Long 
    Dim rngArea As Range 
    Dim lngRow As Long 
    Dim lngCol As Long 
    Dim lngDataRow As Long 
    Dim lngDataCol As Long 
    Dim vntData As Variant 
     
    lngRowCount = (Data.Columns.Count / Data.Columns.Count) - 1 
    Redim vntData(1 To lngRowCount, 1 To MaxCol) 
     
    lngDataRow = 1 
    For Each rngArea In Data.Areas 
        For lngRow = 1 To rngArea.Rows.Count 
            If rngArea.Cells(lngRow, 1).Row = 1 Then 
                 ' skip header
            Else 
                lngDataCol = 1 
                For lngCol = 1 To MaxCol 
                    vntData(lngDataRow, lngDataCol) = rngArea.Cells(lngRow, lngCol).Text 
                    lngDataCol = lngDataCol + 1 
                Next 
                lngDataRow = lngDataRow + 1 
            End If 
        Next 
    Next 
     
    GetRangeData = vntData 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Appreciate if someone can help me with the coding.

Thank you in advance

I need help with the following VB. On the user form, the "Find' button will search worksheet and show duplicate results in a list box. User form has 10 checkboxes that were previously used to add data to a worksheet. When I click on one of the multiple search returns in the listbox I need the checkboxes to return true or false according to how it was originally entered in the workbook.

Can someone please help??? Please?
I am also attaching the whole workbook in case the problem is in the code somewhere else.

Private Sub ListBox1_Click()

If Me.ListBox1.ListIndex = -1 Then 'not selected
MsgBox " No selection made"
ElseIf Me.ListBox1.ListIndex >= 1 Then 'User has selected
r = Me.ListBox1.ListIndex

With Me
.TextBox1.Value = ListBox1.List(r, 0)
.TextBox2.Value = ListBox1.List(r, 1)
.TextBox3.Value = ListBox1.List(r, 2)
.TextBox4.Value = ListBox1.List(r, 3)
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want duplicate
If ListBox1.List(r, 4) = "1.ABYLT" Then Me.CheckBox1.Value = True
If ListBox1.List(r, 4) = "" Then Me.CheckBox1.Value = False

End With
End If
End Sub

In cell A1 i have created a "Drop Down list " using validation which calls
data from B1:B4.
and in A2 i have created a "drop Down List" using validation which Calls
data from D14.
And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively.
C1 = A1 cell data
then in D1 : D4 cell i have used vlookup function to create a dynamic list
i.e. if in C1 data is = 1 the D14 shows 1.1, 1.2, 1.4, 1.4 respectively and
if in C1 data is = 2 the D14 shows 2.1, 2.2, 2.4, 2.4 respectively
Which then shows up in the list box of A2.
The problem i face is say first i selected 1 in cell A1 and the list Box of
A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1
cell value to 2 but but subsequently D14 value chaged to 2.1, 2.2, 2.4, 2.4
respectively but as i entered data in A2 didn't chaged.

what i want is if i chaged data in A1 then the the cell A2 value to be made
"" (Blank) and a alert message to provide to select data in A2.

Hi guys,

I have a form that has a list box and two buttons.

I want to be able to input a directory location on my pc into the listbox and then when i click on the open button it opens up the directory specified in the list box.

and if there is no directory specified (or it can't find it) i want it to generate a error message.

this is the code i tried but sadly keep getting an error.


	VB:
	
 
Dim sPath As String 
 
retVal = Shell(ListBox1.Value & sPath, vbNormalFocus) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any help would be greatly appreciated!

Good afternoon,

I have created form that contains a list box and a single button.

The list box pulls data from a range of cells in a hidden worksheet using the following code:

Code:
 
WSForm.RgList.RowSource = "Regions!A1:A10"
    WSForm.Show
All this does is pop the nice form up on the screen.

What I want to achieve is to store a variable, based on the selection the user chooses in the list box, that can be used later on in the macro. Specifically, I am going to filter the spreadsheet based on the user's selection and then dump the data into a new tab that is named based off the users selection.

I want the user to select a value (or possibly multiple values?) from the list, and then press the button. I want the pressing of the button to store the variable for future use, cause the form to disappear, and cause the macro to continue.

I'd like to set up code that will also cause the macro to stop, the form to disappear, and the user to be left on the Excel sheet if they press the X button on the form window.

I have the following code so far:

Code:
SelectedRegion = Me.WSForm.RgList.Value
NewTabName = Me.WSForm.RgList.Vaue & " Weekly Sales"
I've been told the first line will pull the value the user selects and name it as "SelectedRegion" so I can use this variable later

The second line is supposed to give me the name of the new tab to be used later in the macro...

I know this is a large post, but will someone please help? I simply can't figure this out

I am using the "name box" as an index for people's payment records. So all I
have to do is click on the name box and it brings up a list of all the
payment records I have on the worksheet in alphabetical order. The list uses
their addresses. The thing is, I can't change an address's name once I have
put it in the name box. Does anyone know how to make changes to material you
have entered in the "name box" of an excel worksheet? Having all the
addresses in the name box helps me to move around the worksheet much faster
than before when I didn't know how to use the "name box". Please help if you
can. Thanks.

Hi I have never used a list box, with VBA before..

How do i do it when the list box can have several marked choices...

i want my macro to add text is certain cells, depending on what is picked of the list in the list box.

Thansk

Hi all,

Can anyone tell me how to, in vba, find all the values in a named range on say sheet1 that are greater than or equal to 17 and display the results one under the other and the offset (0,-1) next to each result in a message box? (i dont need to include the words offset and result in the message box)..............something like:

Offset Result
J Bloggs 18
D Bloggs 17
F Bloggs 19
etc.

Thanks for your help in advance!

Simon

Hi Guys,

I have a form with a list box. The multi column list box in the form gets populated with data from a text file.

Now I want to filter data in the list box by controls on the form. For instance i would like to see all records in the list box where date cell value of listbox record is today. Or for example display all records in listbox where customer cell value of listbox record = John Smith

The way I am filtering now is setting date in code itself but thats a pain in the neck because if i want to see records for different dates i have to go back and modify code and set the date there. Id rather user a control on the form itself to filter data in list box. much easier that way. The question is can that be done. if yes what syntax should i be using

any help/suggestions would be appreciated.

Regards,

Well it's been awhile. This stuff can just take over your life that when i
lay off for a bit it ends up being a long spell. Well this should be easy.
I have a userform with a list box containing 8 columns when i click on a row
in the list box it fills the labels. I would like the option of deleting the
entire row they see fit. I attached my update button just to give you an idea
of what is going on. Thank you. Jennifer

Private Sub btnUpdate_Click()
Dim pointer As String
pointer = lstData.ListIndex
If pointer = -1 Then Exit Sub

For index = 2 To source.Rows.Count
If source.Cells(index, 1) = txtDataID.Text Then

With source
.Cells(index, eBoxes.Vendor) = txtVendor.Text
.Cells(index, eBoxes.MiscCosts) = txtMiscCosts.Text
.Cells(index, eBoxes.ProduceID) = Trim(txtProduceID.Text)

.Cells(index, eBoxes.PalletNum) = txtPallet.Text
.Cells(index, eBoxes.QtyPurchased) = txtQty.Text
.Cells(index, eBoxes.QtySold) = txtSold.Text
.Cells(index, eBoxes.Price) = txtPrice.Text
.Cells(index, eBoxes.Frt) = txtFrt.Text
End With

Exit For

End If
Next

LoadData
lstData.ListIndex = pointer

End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer

I am using the "name box" as an index for people's payment records. So all I
have to do is click on the name box and it brings up a list of all the
payment records I have on the worksheet in alphabetical order. The list uses
their addresses. The thing is, I can't change an address's name once I have
put it in the name box. Does anyone know how to make changes to material you
have entered in the "name box" of an excel worksheet? Having all the
addresses in the name box helps me to move around the worksheet much faster
than before when I didn't know how to use the "name box". Please help if you
can. Thanks.

I'm using a list box so the user can select the player name when entering
team members on a score sheet (sheet 1). The list box range is connected to
the Player Details (sheet 2) and displays all players irrespective of the
team they play for. Using the team name (sheet 1) I want to limit the
players shown in the list box to only those of that team. Sheet 2 contains a
list of the players with a unique player number, their name and the team they
are on.

I'd be grateful to receive suggestions as to whether this is possible and if
it is, how to achieve it.

Thanks,

Dave

Hi All!

I have a listbox with three columns, I then have 2 text box and 1 combo box underneath.
and then three commands, Edit, Add and Delete.
I've worked out the code to make the textbox and combobox fill when i click on the name in the list box but I cannot get them to edit.

I also want to type an entry into the text boxes to add to the list

and delete a name in the list....

this is my code so far!!

Private Sub ListBox1_Click()
    With Me
        .txtUSERID.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
        .txtNAME.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
        .cboPERMISSION.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
        End With
        
End Sub


Private Sub comEDIT_click()
Dim rCell As Range
Dim rCell1 As Range
Dim rCell2 As Range

With ListBox1
Set rCell = Range(.RowSource).Resize(1).Offset(.ListIndex, 0)
rCell.Value = txtUSERID.Value

Set rCell1 = Range(.RowSource).Resize(1).Offset(.ListIndex, 1)
rCell1.Value = txtNAME.Value

Set rCell2 = Range(.RowSource).Resize(1).Offset(.ListIndex, 2)
rCell2.Value = cboPERMISSION.Value
End With

End Sub

thanks!!!!!!

Hi,

I have a form with a list box, which I have populated from a sheet using two columns..
Column 1 states the definition, (example, "harmful to skin")
Column 2 states the definition code, (example, relating to above, "R1")

I have a command button which loads this form, where the list box is present, multi-select is turned on so the user can select as many options as needed, and there is another command button on the form which i need to execute the following:

Take the selected options and use the definition code in the second column, and place these into a cell. No matter how many selections are made in one go, they are all submitted into the same cell (just the codes). (Cell is formatted to auto size etc so is okay).

After doing this, if the process is run again, the selection goes into the cell below!

Anybody reckon they could help me please, I'm very new to this! Any help would be much appreciated!

Thanks in advance!

I have set up a search box which searches for business names in all sheets of the attached worksheet. The search part is working...

Ideally, when the options show up in the list box, when I double click one of them, I would like the form to fill out with the data of the particular business chosen. I would then add an 'Amend' button when someone changes the information on the form. Also, when the search results show up in the listbox, is there any way of showing up the 'Status' information alongside the business name?

Any help is much appreciated!

Cheers,

And thanks to Ger Plant for solving my previous issue!

I have set up a userform on the sample worksheet I have attached.

I have an error coming up when I hit 'Add' saying ''Compile error: method or data member not found''

I'm not sure where I have made an error in the code as it was working until I added some new code for one of the combo boxes.

I have also set up a search box which searches for business names in the worksheets. The search is working... ideally, when the options show up in the list box, when I double click one of them, I would like the form to fill out with that particular business. I would then add an 'Amend' button when someone changes the information. Also, when the search results show up in the listbox, is there any way of showing up the 'Status' files alongside the business name?

Any help is much appreciated!

Cheers,

John

I'm sorry in advance, my knowledge of list boxes etc is very rudimentary. Got a project from the boss, and no idea how to do this, all help most appreciated...

1. Have a workbook with multiple sheets, say A, B, C

2. Have a list box of project names on sheet A, referencing table of project data on sheet C.

3. When user makes a selection in list box, want to show the row of data that the selection matches to, in sheets B and C.

So for example, if the user selects 'Project Name 1', I would like to show Project ID, Project Name 1, Start Date, End Date, that is populated in the table on Sheet C.

Extremely grateful for any help on how to do this!

[SIZE=12]
Please refer to this URL which contains an earlier thread kindly answered by jindon.
HTML Code:
http://www.ozgrid.com/forum/showthread.php?t=37464

The file attached below contains a section of code provided by jindon. If you run the macro 'Module2.show' from the 'products' worksheet, it provides a search box. Enter any of the 9 digit code numbers and the results are shown. I like the fact that the search pulls column headings into the listbox. Looking through jindon's code, i think that it is this code that triggers this result.

	VB:
	
 .Range("f1") 
    .CurrentRegion.Clear 
    .Resize(, UBound(title) + 1).Value = title 
    .Offset(1).Resize(UBound(a, 2), UBound(a, 1)).Value = Application.Transpose(a) 
    Set rng = .CurrentRegion 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

However, if the module2.show is run from 'sheet1' no results happen.

Jindon tackles this further down the above post by modifying the code but the result is that although the module2.show correctly produces search results when run from 'sheet1', the column headers are absent.

What i would like to achieve is :-
· retain the current 'sheet1' and 'products' worksheets
· Retain all data in the 'products' worksheet (but maybe hide it from the user)
· run the search form from sheet 1 and to produce results into the list box but to include a header at the top of each column.

I have attached a file for illustration purposes. Can i please add that this is not my work, rather an example that was used by another submitter.

I hope this explains the query and many thanks for taking the time to read this. I have advised jindon of this thread for his input.

Regards
bobz

PS. FAO Moderators. I apologise but i originally inadvertantly posted this thread on the incorrect forum (it went under technical issues.) Please remove the other post as you see fit.

many thanks

In a MS Excel worksheet, after making an Auto filter in a list, why sometimes
does not show the result " x of y records found' (x being the count result
and y the total number of records in the list) on the status bar. Is there
any preference setting to make it shown automatically


No luck finding an answer? You could always try Google.