Search for Values

[smandula]

Hello, has anyone developed a VBA routine to find several cell
values,
using a Message Box, for a specific range. Please no conditional
formulae.

With thanks

 

[Dave Peterson]

You may want to try Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

 

[Chip Pearson]

I have a procedure at http://www.cpearson.com/excel/findall.aspx named
FindAll that returns a Range object containing all the found cells
from a search of some range. Using that function, you can loop through
the results and display the found cells' addresses and/or values.

I also have an add-in (which at the core just runs the aforementioned
code) which allows you to search any number of worksheets and display
the addresses and values of the found cells in a list box. See
http://www.cpearson.com/excel/FindAllXLA.aspx for information about
and a download of the FindAll XLA add-in.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mike Clemens]

I have a userform named FunctionButtons with showmodal set to true that is
displayed automatically by the workbook_open event procedure with the following
code:

VBA.UserForms.Add(FunctionButtons.Name).Show

The form is displayed on the first and only worksheet in the workbook when the
workbook is opened.

One of the macros (accessed by clicking one of the button on the FunctionButtons
form) adds a new worksheet and activates the new worksheet.

I don't want the FunctionButtons form displayed on the second worksheet so I
added the following code to the first worksheet.

Private Sub Worksheet_Activate()
FunctionButtons.Show
End Sub

Private Sub Worksheet_Deactivate()
FunctionButtons.Hide
End Sub

I know these routines are executed at the right times from placing breakpoints
in them.

Sometimes with the second worksheet is added and displayed the FunctionButtons
form is still displayed. When this happens going back to the first worksheet
causes a second copy of the form to be displayed. Getting the second copy of
the form only happens once regardless of the number of times you switch back and
forth between the first and second worksheets. If it happens it is always the
first time switching back to the first worksheet.

Other times everything appears to work correctly - no form on the second
worksheet and no second copy of the form on the first worksheet.

Any ideas as to what is going on? I've spent most of the day trying to figure
this out without an success.

Thanks for your help.