Make sheet change stick after Application.Inputbox

R

RB Smissaert

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
 
B

Bernie Deitrick

RBS,

There is an easier way:

Dim myR As Range
Set myR = Application.InputBox("Select a range", Type:=8)
myR.Parent.Activate

And it would be improved by some error checking...

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

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
 
R

RB Smissaert

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
 
P

Peter T

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 said:
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
Click to expand...
 
R

RB Smissaert

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 said:
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
Click to expand...
 
R

RB Smissaert

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 said:
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
Click to expand...
 
P

Peter T

Dave Peterson said:
That's one of the nice things about using application.goto.

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

Yes that's easier.

Regards,
Peter T
 
Top