I have a macro that currently pops up a windows explorer box to allow the
user to find an excel spreadsheet to run the macro on. I would like to
change the macro to allow the user to select a currently open active excel
spread sheet on their desktop instead. Any Ideas?

Maybe you could just use something like:

dim myCell as range
set mycell = nothing
on error resume next
set mycell = application.inputbox(Prompt:="pick a cell on any worksheet", _
type:=8)
on error goto 0
if mycell is nothing then
'user hit cancel
exit sub
end if

msgbox mycell.address _
& vblf & mycell.parent.name _
& vblf & mycell.parent.parent.name

====
Tell them to use Window to get to the other workbooks.

=========
Alternatively, you could create a userform that has a combobox that displays all
the open workbooks and let them choose from one of them.

Look at Chip Pearson's site:
http://www.cpearson.com/excel/download.htm
and look for the Compare utility that Bill Manville and Myrna Larson wrote.

You may be able to borrow lots of code from that.

zulfer7 wrote:

I have a macro that currently pops up a windows explorer box to allow the
user to find an excel spreadsheet to run the macro on. I would like to
change the macro to allow the user to select a currently open active excel
spread sheet on their desktop instead. Any Ideas?

--

Dave Peterson

Thanks Dave for responding to my inquiry. The macro you gave me again only
looks at the currently active sheet and the link you provided to the compare
macro does the same, it only lists open sheets within the same workbook. I
kind of found a work around in that the user can manually paste in a column
of data which is kindof the next best thing to being able to go to another
spreadsheet and pick a range.

Thanks!

"Dave Peterson" wrote:

Maybe you could just use something like:

dim myCell as range
set mycell = nothing
on error resume next
set mycell = application.inputbox(Prompt:="pick a cell on any worksheet", _
type:=8)
on error goto 0
if mycell is nothing then
'user hit cancel
exit sub
end if

msgbox mycell.address _
& vblf & mycell.parent.name _
& vblf & mycell.parent.parent.name

====
Tell them to use Window to get to the other workbooks.

=========
Alternatively, you could create a userform that has a combobox that displays all
the open workbooks and let them choose from one of them.

Look at Chip Pearson's site:
http://www.cpearson.com/excel/download.htm
and look for the Compare utility that Bill Manville and Myrna Larson wrote.

You may be able to borrow lots of code from that.

zulfer7 wrote:

I have a macro that currently pops up a windows explorer box to allow the
user to find an excel spreadsheet to run the macro on. I would like to
change the macro to allow the user to select a currently open active excel
spread sheet on their desktop instead. Any Ideas?

--

Dave Peterson

You can choose another open workbook via the Window option on the menubar.

Then you can select any sheet you want in that workbook. And choose a cell
there.

And the Compare written by Myrna Larson and Bill Manville shows all the
worksheets in all the open workbooks.



zulfer7 wrote:

Thanks Dave for responding to my inquiry. The macro you gave me again only
looks at the currently active sheet and the link you provided to the compare
macro does the same, it only lists open sheets within the same workbook. I
kind of found a work around in that the user can manually paste in a column
of data which is kindof the next best thing to being able to go to another
spreadsheet and pick a range.

Thanks!

"Dave Peterson" wrote:

Maybe you could just use something like:

dim myCell as range
set mycell = nothing
on error resume next
set mycell = application.inputbox(Prompt:="pick a cell on any worksheet", _
type:=8)
on error goto 0
if mycell is nothing then
'user hit cancel
exit sub
end if

msgbox mycell.address _
& vblf & mycell.parent.name _
& vblf & mycell.parent.parent.name

====
Tell them to use Window to get to the other workbooks.

=========
Alternatively, you could create a userform that has a combobox that displays all
the open workbooks and let them choose from one of them.

Look at Chip Pearson's site:
http://www.cpearson.com/excel/download.htm
and look for the Compare utility that Bill Manville and Myrna Larson wrote.

You may be able to borrow lots of code from that.

zulfer7 wrote:

I have a macro that currently pops up a windows explorer box to allow the
user to find an excel spreadsheet to run the macro on. I would like to
change the macro to allow the user to select a currently open active excel
spread sheet on their desktop instead. Any Ideas?

--

Dave Peterson


--

Dave Peterson