Make sheet change stick after Application.Inputbox
 

I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and then
activate that sheet, but I have a feeling that there might be a simpler way
to do this.

RBS

--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

With sticking I mean that it doesn't return to the sheet that was active
when the Application.InputBox was started.

Thanks to both and Range.Parent is the simple answer indeed.

RBS


"Dave Peterson" wrote in message
...
I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and
then
activate that sheet, but I have a feeling that there might be a simpler
way
to do this.

RBS

--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

For completeness might want to do

rng.Parent.Parent.Activate
rng.Parent.Activate

Just in case, there's a bug with Type:=8 on sheets with certain types of CF,
two workarounds here

http://www.jkp-ads.com/Articles/SelectARange.asp

Regards,
Peter T


"RB Smissaert" wrote in message
...
With sticking I mean that it doesn't return to the sheet that was active
when the Application.InputBox was started.

Thanks to both and Range.Parent is the simple answer indeed.

RBS


"Dave Peterson" wrote in message
...
I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while
in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and
then
activate that sheet, but I have a feeling that there might be a simpler
way
to do this.

RBS

--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

Thanks for that, chance is very small my users will come across that bug,
but you never know.
Didn't know you could use Type:=0 argument and still pick the range up.
I will go for the second work-around then.
I used something similar (parsing out the sheet from the range address)
before
I knew about rng.Parent:

Function GetSheetFromRange(rng As Range) As Worksheet

Dim lPos1 As Long
Dim lPos2 As Long
Dim strAddress As String
Dim strSheet As String

strAddress = rng.Address(, , , True)
lPos1 = InStr(1, strAddress, "]")
lPos2 = InStr(1, strAddress, "!")
strSheet = Mid$(strAddress, lPos1 + 1, (lPos2 - lPos1) - 1)

'not sure why the single quote is there sometimes and sometimes not
'------------------------------------------------------------------
If Right$(strSheet, 1) = Chr(39) Then
strSheet = Left$(strSheet, Len(strSheet) - 1)
End If

Set GetSheetFromRange = Sheets(strSheet)

End Function


RBS



"Peter T" <peter_t@discussions wrote in message
...
For completeness might want to do

rng.Parent.Parent.Activate
rng.Parent.Activate

Just in case, there's a bug with Type:=8 on sheets with certain types of
CF, two workarounds here

http://www.jkp-ads.com/Articles/SelectARange.asp

Regards,
Peter T


"RB Smissaert" wrote in message
...
With sticking I mean that it doesn't return to the sheet that was active
when the Application.InputBox was started.

Thanks to both and Range.Parent is the simple answer indeed.

RBS


"Dave Peterson" wrote in message
...
I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while
in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and
then
activate that sheet, but I have a feeling that there might be a simpler
way
to do this.

RBS

--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

That's one of the nice things about using application.goto.

It doesn't need to activate a workbook, then the worksheet.

Peter T wrote:

For completeness might want to do

rng.Parent.Parent.Activate
rng.Parent.Activate

Just in case, there's a bug with Type:=8 on sheets with certain types of CF,
two workarounds here

http://www.jkp-ads.com/Articles/SelectARange.asp

Regards,
Peter T

"RB Smissaert" wrote in message
...
With sticking I mean that it doesn't return to the sheet that was active
when the Application.InputBox was started.

Thanks to both and Range.Parent is the simple answer indeed.

RBS


"Dave Peterson" wrote in message
...
I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while
in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and
then
activate that sheet, but I have a feeling that there might be a simpler
way
to do this.

RBS

--

Dave Peterson


--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

I like that function GetInputRange, nice work, and will use that now.
Maybe Application.Goto could be a little refinement as is this for if you
don't want
the selection to be the default input:

If Len(sDefault) = 0 Or sDefault = "nil" Then
If TypeName(Application.Selection) = "Range" And sDefault < "nil" Then
sDefault = "=" & Application.Selection.Address
'InputBox cannot handle address/formulas over 255
If Len(sDefault) 240 Then
sDefault = "=" & Application.ActiveCell.Address
End If
Else
If TypeName(Application.ActiveSheet) = "Chart" Then
sDefault = " first select a Worksheet"
Else
sDefault = " Select Cell(s) or type address"
End If
End If
End If


RBS


"Peter T" <peter_t@discussions wrote in message
...
For completeness might want to do

rng.Parent.Parent.Activate
rng.Parent.Activate

Just in case, there's a bug with Type:=8 on sheets with certain types of
CF, two workarounds here

http://www.jkp-ads.com/Articles/SelectARange.asp

Regards,
Peter T


"RB Smissaert" wrote in message
...
With sticking I mean that it doesn't return to the sheet that was active
when the Application.InputBox was started.

Thanks to both and Range.Parent is the simple answer indeed.

RBS


"Dave Peterson" wrote in message
...
I'm not sure what you mean by sticking, but maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="pick a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
Application.Goto myRng ', scroll:=true
End If

End Sub

Or maybe...

Application.Goto myRng.Parent.range("A1") ', scroll:=true



RB Smissaert wrote:

Is there a simple way to make a sheet change stick that was done while
in
the Application.InputBox dialog (with Type:=8)?
I can do it by parsing out the sheet name from the resulting range and
then
activate that sheet, but I have a feeling that there might be a simpler
way
to do this.

RBS

--

Dave Peterson

Make sheet change stick after Application.Inputbox
 

"Dave Peterson" wrote in message

That's one of the nice things about using application.goto.

It doesn't need to activate a workbook, then the worksheet.

Yes that's easier.

Regards,
Peter T