Search for Values
 

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

Search for Values
 

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

smandula wrote:

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

Search for Values
 

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)

On Sat, 27 Dec 2008 19:18:43 -0800 (PST), smandula
wrote:

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

Problem with hide/show for userform where showmodal is set to true
 

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.

Problem with hide/show for userform where showmodal is set totrue
 

One other thing I found - when the hide/show stuff works the following line of
code does not work.

AppActivate ActiveWorkbook.Windows(1).Caption

When the hide/show stuff does not work the same lime of code works fine.

The error generated when it doesn't work is "Invalid procedure call or argument".

Mike Clemens wrote:
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.