Make sheet change stick after Application.Inputbox

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
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
 
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
 
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
 
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
 
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...
 
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...
 
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...
 
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
 
Back
Top