Application.InputBox pre-selects unexpected default range

user_10908769 0
Viewed 33

I want the user to select a Range via Application.InputBox with Type:=8.
I give it a default range, but in some cases it shows an unexpected behaviour and pre-selects a different wrong default range.

My example code:

Dim myDefaultRange as Range
Dim myInputRange as Range

Set myDefaultRange = ActiveSheet.Range("C8")
Set myInputRange = Application.InputBox( _
  Title:="Exampe Title", _
  Prompt:="Example Prompt", _
  Default:=myDefaultRange.Address(0, 0), _
  Type:=8)

I expected my example to select cell C8 and ask like this:

wish

Instead column H is preselected and the user is asked for a new range for $H:$H like this: reality

vba excel
1 Answers

When the default range for the Application.InputBox points to a cell in column C or R and you define it without $, then it's interpreted as R1C1-notation with a given Column or Row.
In your example the cell's address C8 is interpreted as column 8, i. e. column H.

The default range should be defined like this:

Default:=myDefaultRange.Address

By this, the default range in your example will be shown as $C$8 and Application.InputBox will preselect the desired default cell range.

user_109087690
Related