Missing Sheet Reference

[Chromics]

I would like to use an input box to prompt the user to select a range on another sheet. The box opens on the main sheet, the user selects another sheet, then selects the range. The box displays the sheet and range address correctly, but when it is accepted, mystring does not return the sheet source, only the cell range. I would like to return both the sheet and range address.

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 Schollar]

Hi

mystring = "'" & myrange.Parent.Name & "'!" & myrange.Address

 

[Chromics]

Richard,

Thank you, works well. By the look of your icon, you have your hands full.

 

[Richard Schollar]

You're welcome

I think you could more accurately say my wife has her hands full