[SOLVED] Macro to search table and show row info

Is there a reason you're not using a UserForm to perform these actions? It's far easier to manipulate controls that way.

BSB

yeah i guess i could use a userform....my thinking was that on this main sheet which has a pivot table showing the status of these items, if i wanted to quickly and easily change the status of a particular job...it would be easier to just type in the job number right on the worksheet and update it ....rather than open a userform and do it that way (which i already have)...for me its the same either way, but i know the person actually using this sheet might get lost with the userforms...so something simple right on the worksheet might be more user friendly?

however, after writing this out and thinking about it...you're right, i probably should just go with the userform which has only these items on it...especially since most of the code I already have written.

Thanks for the bringing me back to reality here...

Thats a good idea. how would you go about doing that?

with my current userform...i have to close it in order to select a new worksheet....so you're saying that you can have a userform that is open and still move around the workbook? or at least select a new worksheet which would close the userform?

Yes the line below would allow you to open a UserForm and still navigate around and manipulate the workbook.

Please Login or Register  to view this content.

BSB

And this in the Dashboard sheet code module would close the UserForm when you select another sheet if need be.

Please Login or Register  to view this content.

Worth noting that if you unload the form rather than just hide it then you'll lose any data you've recorded on it.

BSB

So I'm trying to figure out how to use this code

Please Login or Register  to view this content.

where exactly do i place it? would i be able to have the userform pop up with you open the work sheet? or do i need a button to open it?

Place this code in the "worksheet" module. Easiest way to access that is right click the sheet tab at the bottom of Excel and select View Code.

Please Login or Register  to view this content.

Then this code in the "workbook" module. In the VB Editor double click on the icon in the "Project window" top right (usually) where is says "ThisWorkbook".

Please Login or Register  to view this content.

This will effectively select Sheet1 if it's not already selected when the workbook opens and then open the form, but will also open the form if Sheet1 IS already selected.

It will also mean once the workbook is open you can select other sheets, but will reopen the form when you go back to Sheet1 if you do decide to have it automatically unload when you select another sheet.

I hope that makes sense. Shout for help if need be.

BSB

sorry i forgot to respond to this. This worked perfectly! Thank you.