Yes, the InputBox with Type 64 allows the user to input an array of values. However, the user needs to use a specific separator to separate the values in the array. The separator used depends on the user's regional settings in Windows.
To avoid the formula error box, you can try using the following code:
Formula:
Dim separator As String
separator = Application.International(xlListSeparator)
Set cols = Application.InputBox("Type in columns separated by " & separator, Type:=64)
This code uses the
International function to get the list separator character for the user's regional settings. It then uses this separator in the InputBox prompt.
- If the user's regional settings use a comma as the list separator, the prompt will say "Type in columns separated by ,".
- If the user's regional settings use a semicolon as the list separator, the prompt will say "Type in columns separated by ;".