user interaction to select a sheet
 

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

user interaction to select a sheet
 

Have a look in vba HELP index for INPUTBOX or MSGBOX

--
Don Guillett
SalesAid Software

"mark" wrote in message
...
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

user interaction to select a sheet
 

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

user interaction to select a sheet
 

Dave,

Thanks for the ideas.

I had thought of the userform idea to display available workbooks and
sheets. I've developed lots of userforms and could do that easily enough.

I may also look at the compare.xls file that you mentioned. It was a
comparison of two things that I did manually which someone liked a lot that
had me considerating automating for them.

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

user interaction to select a sheet
 

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

user interaction to select a sheet
 

Sometimes, training users to use Window on the worksheet menubar can be a pain.
Using the combobox can make it easier to select different worksheets.

mark wrote:

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


--

Dave Peterson