Sheets Missing From Reference

**chromics** · 03-18-2009, 01:42 PM

I would like to use an input box to ask the user to select a range that may be another sheet. If it is on another sheet, the returned range references only the cells without the sheet. How can I return the source sheet too?

Set myrange = Application.InputBox(Prompt:="Choose a range", Title:="Choose", Type:=8)

'User selects a range on another sheet....
mystring = myrange.Address

Any guidance would be appreciated. Thanks

**Richard Buttrey** · 03-18-2009, 01:52 PM

Hi,

You could use the .Parent property of the range to obtain the sheet name.

e.g.

stSheetName = Range(mystring).Parent.Name

HTH

**chromics** · 03-18-2009, 01:58 PM

Richard,

Thanks, but this command returns the name of the sheet that was active when the input box was called, not where the range was selected.

**Richard Buttrey** · 03-18-2009, 02:18 PM

Hi,

Your original post had implied that the user had already activated a cell on the sheet in question.

How do you expect the system to know which sheet the user has in mind when he calls the input box? You will either need to get the user to navigate to a sheet so that you can use the name, or prompt the user for a sheet name as well as a range name/address.

HTH

**chromics** · 03-18-2009, 02:37 PM

Richard, Thanks again, and sorry if I confused you. When the input box opens, the user can select another sheet. The box stays up and when the user highlights a range, the box displays the sheet with the range address. I found it strange that the sheet name was "volatile". After selecting OK, only the cell address is returned.