InputBox method default parameter (type 8)

This module demonstrates:

Four code statements that can be used to set the Default value for the InputBox Method when entering a Range address:
1. A range name - name object, name property
2. A range name - as a string
3. A range address - address property - ActiveWorksheet address (no WS identified)
4. A range address - name property - Workbook address (WS identified)
The InputBox method arguments are displayed by the VBA Quick Info feature in figure 0

Fig 0: VBA Quick Info for the InputBox method. The [Default] argument is the third parameter [optional]

1. Example and specifications

The demonstration task (abridged version): Write a sub procedure to add a set of new worksheets to the Workbook. … The worksheet names are in the Range name ListWS (with workbook scope) … (and) the run procedure … displays on input box with default value ListWS.

From the Name Manager (ListWS) as used in this example has the following specifications:

Range name: ListWS
Value: {"WS1";"WS2";"WS3";"WS4";"WS5"}
RefersTo: =Sheet1!$B$5:$B$9
Scope: Workbook

2. Setting the Default parameter for InputBox method type 8

The four examples are listed in this section. Only the first two (section 2.1 and 2.2) set the actual ListWS name, while the second two (section 2.3 and 2.4) set the cell reference for the name.

Object property descriptions can be found at the end of this module.

2.1 Name.Name property

This example uses the Name property of the Name object (code 1)

Code 1: Default address string, and default Range.Name.Name

Const sDefAddr As String = "ListWS" ' Default address - WB scope name

Sub IBmDefault()
' Demonstrates InputBox method Default parameter with Range object

Dim InRange As Range
On Error Resume Next

' 1. Range.Name.Name property ==
    Set InRange = Application.InputBox(Title:="Add worksheets from list (1)", _
                                       Prompt:="Enter list location", _
                                       Default:=Range(sDefAddr).Name.Name, _
                                       Type:=8)
    Set InRange = Nothing

About code 1 :

Line 12: Default:= =Range("ListWS").Name.Name The Name property of the Range object Range("ListWS").Name returns a Name object, then the Name property of the Name object returns a string "ListWS". See figure 1a item ① for details of the Name object, and figure 1b for the InputBox as displayed.

ibox name obj — **Fig 1a: Locals Window view of the Range.Name property (a Name object)** ① the Name object Name property (Name.Name) returns the Name as a String, and ④ and the Name object Value property (Name.Value, described in section 2.4) returns the Address as a String

Fig 1b: Name object, Name property Default:=Range("ListWS").Name.Name (string literal form)
● When a Default value is set, the InputBox appears with the value selected. Any subsequent typing, change of worksheet, or range selection will overwrite the value
● Click the OK button, then the value (ListWS range) is assigned to the InRange range object
● This technique ensures that the name exists. If there is no ListWS name then the line 10 to 13 statement returns an error, and the On Error Resume Next statement (line 7) will prevent the InputBox from being displayed

2.2 String type

This example assigns a string (code 2)

Code 2: Default as text string


' 2. String ====================
    Set InRange = Application.InputBox(Title:="Add worksheets from list (2)", _
                                       Prompt:="Enter list location", _
                                       Default:=sDefAddr, _
                                       Type:=8)
    Set InRange = Nothing

About code 2 :

Line 18: Default:= ="ListWS" Uses a String to specify the range object, written here in string literal form. See figure 2
Fig 2: String Default:="ListWS"
The ListWS range is assigned to the InRange range object

To set the Default for an InputBox method as a defined name, use one of these methods:
Default:=Range(sDefAddr).Name.Name, …
Default:=sDefAddr, …
where sDefAddr is a module level String constant with value of the default address

For completeness, variations of the InputBox Default parameter with a reference Address are coded in the following sections.

2.3 Address property

This example assigns a reference with absolute addressing, but is not worksheet specific (code 3)

Code 3: Default as Range.Address


' 3. Range.Address property ====
    Set InRange = Application.InputBox(Title:="Add worksheets from list (3)", _
                                       Prompt:="Enter list location", _
                                       Default:=Range(sDefAddr).Address, _
                                       Type:=8)
    Set InRange = Nothing

About Code 3

Line 28: Use the Address property of the Range object to return the Address in A1 style (absolute)

The Address property of the Range object takes a number of optional arguments. Line 28 can also be written as any of the equivalent statements:

    Default:=Range(sDefAddr).Address(), _

    Default:=Range(sDefAddr).Address(True,True), _

    Default:=Range(sDefAddr).Address(RowAbsolute:=True, ColumnAbsolute:=True), _

Examples of Address property parameters

'
' SYNTAX: Range.Address(RowAbsolute , ColumnAbsolute , ReferenceStyle , External , RelativeTo)
'
'
' Parameter examples
' ===============
'
' Absolute A1 ===
Debug.Print Range(sDefAddr).Address()
                          ' Returns: $B$5:$B$9


' Mixed A1 - absolute column ===
Debug.Print Range(sDefAddr).Address(RowAbsolute:=False)
                          ' Returns: $B5:$B9


' Mixed A1 - absolute row ===
Debug.Print Range(sDefAddr).Address(ColumnAbsolute:=False)
                          ' Returns: B$5:B$9


' Relative A1 ===
Debug.Print Range(sDefAddr).Address(RowAbsolute:=False, _
                                    ColumnAbsolute:=False)
                          ' Returns: B5:B9


' Absolute R1C1 ===
Debug.Print Range(sDefAddr).Address(ReferenceStyle:=xlR1C1)
                          ' Returns: R5C2:R9C2


' Relative R1C1 ===
Debug.Print Range(sDefAddr).Address(RowAbsolute:=False, _
                                    ColumnAbsolute:=False, _
                                    ReferenceStyle:=xlR1C1)
                          ' Returns: R[4]C[1]:R[8]C[1]


' Relative R1C1 with anchor at R4C2 ===
Debug.Print Range(sDefAddr).Address(RowAbsolute:=False, _
                                    ColumnAbsolute:=False, _
                                    ReferenceStyle:=xlR1C1, _
                                    RelativeTo:=Cells(4, 2))
                          ' Returns: R[1]C:R[5]C


' Absolute A1 external form ===
Debug.Print Range(sDefAddr).Address(External:=True)
                          ' Returns: '[ListWS name.xlsm]Sheet1'!$B$5:$B$9
'
' ===============
'

Note: the Address property Value expression is not listed in the Locals view of the Range object (figure 1a). Property expressions with arguments are excluded.

In the example, code 3 displays the InputBox shown in figure 3.

ibox default 3 — **Fig 3: Range object Address property** Default:=Range(sDefAddr).Address

Given that an InputBox type 8 is Modeless, then lack of a Worksheet name may cause selection of the incorrect range if the User displays the InputBox on a worksheet other than the one specific to ListWS. Switching worksheets, however, will delete the default and insert the ActiveWorksheet name. The risk of a wrong worksheet can be mitigated by the technique in section 2.4

2.4 Name.Value property

In this example, the reference with an absolute address style also includes the worksheet name (code 4)

Code 4: Default as Range.Name.Value


' 4. Ramge.Name property =======
    Set InRange = Application.InputBox(Title:="Add worksheets from list (4)", _
                                       Prompt:="Enter list location", _
                                       Default:=Range(sDefAddr).Name, _
                                       Type:=8)
    Set InRange = Nothing

End Sub

About Code 4

Line 38: Use the Name property of the Range object to return the Value property of the Name object - for details, see ④ in figure 1a
Value is the default property of the Name object, so line 38 can also be written as:
```
    Default:=Range(sDefAddr).Name.Value, _
    
```

In the example, code 4 displays the InputBox shown in figure 4. Note the explicit inclusion of the Worksheet name and the leading "=" sign. Equivalent to the Name.RefersTo property

ibox default 4 — **Fig 4: Use the Range object Name property** to return the Name object Value property

Object property descriptions

VBA properties	Description
Name.Name	Returns a String that represents the Name of the Name object
Range.Address(RowAbsolute , ColumnAbsolute , ReferenceStyle , External , RelativeTo)	Returns a String that represents the range reference Default: no parameters; Address or Address() returns an absolute A1-style reference Parameters: RowAbsolute: Optional - a Variant - Boolean True or omitted returns the row component as an absolute reference ColumnAbsolute: Optional - a Variant - Boolean True or omitted returns the column component as an absolute reference ReferenceStyle: Optional - the default is XlReferenceStyle Enumeration: xlA1 (Value 1), returns an A1-style reference XlReferenceStyle Enumeration: xlR1C1 (Value -4150), returns an R1C1-style reference Note: ReferenceStyle is also a property of the Application object External: Optional - a Variant to return an external reference. Boolean False or omitted returns a local reference RelativeTo: Optional - a Variant that uses a Range object to mark the anchor point (starting point) if RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1
Range.Name	Returns a Variant (Name object) representing the name of the Range. The Name object default property is Value. Equivalent to Range.Name.Value

References

Range.Name Property Excel Microsoft (MSDN), Accessed 30 June 2017

Development platform: Excel 2016 (64 bit) Office 365 ProPlus on Windows 10
Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]