How to specify the default range for Application.InputBox

[SammyB]

Hans has posted that this is the way

Code: Select all

 Set rngSelRange = Application.InputBox( _
        Prompt:="Please select the columns that you wish to include in the chart.", _
        Title:="Selected columns", _
        Default:=rngSelRange.Address, _
        Type:=8)

However for me, this displays the range in A1 style. Is there an easy way to tell Address (or AddressLocal) to use the language of the ActiveSheet? I switch all the time between R1C1 & A1, so I cannot hardcode it. Also, is there a way to not crash if the user presses Cancel?

[HansV]

Like this:

Code: Select all

    Set rngSelRange = Application.InputBox( _
        Prompt:="Please select the columns that you wish to include in the chart.", _
        Title:="Selected columns", _
        Default:=rngSelRange.Address(ReferenceStyle:=Application.ReferenceStyle), _
        Type:=8)

[p45cal]

one way:

Code: Select all

Dim rngSelRange As Range, rngtemp As Range
'Set rngSelRange = Selection
Set rngtemp = Nothing
On Error Resume Next
Set rngtemp = Application.InputBox( _
              Prompt:="Please select the columns that you wish to include in the chart.", _
              Title:="Selected columns", _
              Default:=rngSelRange.Address(ReferenceStyle:=Application.ReferenceStyle), _
              Type:=8)
On Error GoTo 0
'test what's in rngtemp:
If rngtemp Is Nothing Then
  MsgBox "Cancelled"
Else
  Set rngSelRange = rngtemp
  rngSelRange.Select
End If

[SammyB]

Once I spelled Resume & Prompt correctly, it works perfectly (I should learn how to copy & paste). Thanks, Hans!!!