Dave,

I just tested the input box idea that you suggested. It will work fine.

I also looked at the compare.xla that you referred me to. The application
that I was considering programming is similar to that, but slightly
different, of course. In my specific situation, I think programming it
myself will fit what the user needs, better, and it won't take me long.

Thanks again,
Mark

"Dave Peterson" wrote:

You can use an application.inputbox to get a range and use the .parent property
to get the worksheet that owns that range:

Dim wks1 as worksheet
dim wks2 as worksheet

set wks1 = nothing
on error resume next
set wks1 = application.inputbox(Prompt:="select a cell on the 1st worksheet", _
type:=8).parent
on error goto 0

if wks1 is nothing then
'user hit cancel
exit sub
end if

set wks2 = nothing
on error resume next
set wks2 = application.inputbox(Prompt:="select a cell on the 2nd worksheet", _
type:=8).parent
on error goto 0

if wks2 is nothing then
'user hit cancel
exit sub
end if

msgbox wks1.name & vblf & wks2.name

(untested, uncompiled--watch for typos.)

=============
As an alternative, you could build a small userform that has a couple of
comboboxes that allows the users to pick from the available worksheets (from the
workbooks that are open).

If you want to see an example, take a look at this compare addin developed by
Myrna Larson and Bill Manville.

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

You may find that it's easier for most users, too.

If you've never created a user form, Debra Dalgleish has a short tutorial:
http://contextures.com/xlUserForm01.html

mark wrote:

Hello.

I would like the user to be able to click on a cell in a sheet to tell the
program where something is.

1) Click a command button to start the program
2) Ask the user where the first sheet to work with is
3) Ask the user where the second sheet to work with is

I'm thinking it would work something like the little ranger finder icon that
you use to define a consolidation range or a Pivot Table's input... or
something like that. But I don't know how to throw up that dialog box to let
the user click on a cell or highlight a group of cells, and then continue on.

Can someone help me with just that piece?

Thanks.
Mark

--

Dave Peterson