Multicolumn Listbox

[Jaye7]

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.

 

[xenou]

Are you using controls from the forms menu or the activeX menu/toolbar?

 

[Jaye7]

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.

 

[xenou]

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.

 

[Jaye7]

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.

 

[xenou]

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...

 

[Jaye7]

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.

 

[xenou]

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).

 

[Jaye7]

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.

 

[xenou]

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:

    If LCase(CStr(OneCell.Value)) Like "*" & LCase(SearchTerm) & "*" Then

that finds Cat and cat in the example given...

ξ

 

[Jaye7]

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.