Multicolumn Listbox

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,066
I would like a script that would find all values in column A based on the value typed in textbox1 and then would put the cell value in column 1 of listbox1 and the cell address in column 2 of listbox1.

Can someone please help.
 
Are you using controls from the forms menu or the activeX menu/toolbar?
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Mike,

The code does work for the value, I do want to be able to return to the cell that is why I wanted the cell address in column 2, but I don't have the vba skills to write the code, can you please help as that's teh main bit that I want, the ability to go to a cell.
 
Upvote 0
You may be happy to discover Excel has a find feature which both searches for values, lists cell addresses, and even takes you to the cell ... all in a flash and with no need for code or extra controls on the worksheet. :cool:
 
Upvote 0
Thanks Xenou, but I need it in a userform as I have scripts in the userform to do various tasks based on what is in the userform, the problem is that sometimes I may want to run a script on one of the values only and then run other scripts from the form.

I find that I have much more versitility with userforms to do processes quickly, especially when some processes can take up to 5 minutes to complete using scripts or 20-30 minutes manually.
 
Upvote 0
I'd be happy to try out the ideas so far but what type of controls are you using (post 11)? By the way, you can use find in code too but that's probably too easy...
 
Upvote 0
Xenou.

I am using controls on the userform, at the moment I am using a Test userform and then once I can get it working right I will add it to another userform which has my other codes (obvously I will have to modify the scripts).

The controls are textbox1 which I enter the search/ find value in (partial match)

I have listbox1 which is what I want to have the two columns, the value in column 1 and the address in column 2, so that I can return to a specific cell if I need to before running other script.

I have commandbutton1 which I use to search for the value in textbox1 and load into listbox1.

I will also have commandbutton2 so that if I click on a specific value in listbox1 I can then go to that cell address to run my codes.

Later I will use other controls so that I can put other information into listboxes using offset functions etc... which I have little enough ability to manipulate.
 
Upvote 0
I'm not finding any difficulties with Mike's code <a href="http://northernocean.net/etc/mrexcel/20101025_findcat.zip">Sample workbook</a> Try again, maybe? I've attached a very simple test (pardon - I didn't even bother to get controls lined up or anything).
 
Last edited:
Upvote 0
Thanks Mike and Xenou it works great,

Sorry Mike I have never actually used multi column so I wasn't aware that you could set the listbox to columns, I have seen other scripts written and they were included in the script, I just could never get them to do exactly what I wanted them to.

Brilliant, now I can manipulate the scripts to make my life much easier.:):):)
 
Upvote 0
Great. Almost like tag team wrestling (well, ... almost).

Looking at my sample I notice one cell isn't found. For a case insensitive search you could amend to:

Code:
    If LCase(CStr(OneCell.Value)) Like "*" & LCase(SearchTerm) & "*" Then
that finds Cat and cat in the example given...

ξ
 
Upvote 0
Thanks Xenou,

I just opened up my internet to ask about the exact thing you just answered, I too noticed it on the spreadsheet that you provided.

Thanks for the adjustment, it now works perfectly.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top