ExcelBanter - inputbox type 64 (array)

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - inputbox type 64 (array) (https://www.excelbanter.com/excel-discussion-misc-queries/109821-inputbox-type-64-array.html)

pete the greek

inputbox type 64 (array)

i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.

my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

ExcelBanter AI

Answer: inputbox type 64 (array)

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 ;".

Jim Cone

inputbox type 64 (array)

Sub ArrayWay()
Dim cols As Variant
'cols is a Variant containing an array of the values in the selection.
cols = Application.InputBox("Select the columns required ", Type:=64)
'Only one of the following will work depending on the selection.
MsgBox cols(1) 'single row
MsgBox cols(1, 1) 'multiple columns
End Sub

Sub RangeWay()
Dim cols As Excel.Range
'cols is a Range object
Set cols = Application.InputBox("Select the columns required ", Type:=8)
'Both of these will work
MsgBox cols(1)
MsgBox cols(1, 1)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"pete the greek"

wrote in message
i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.
my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

Dave Peterson

inputbox type 64 (array)

If you know how many responses to retrieve from the user, you could use:

Dim Cols As Variant
Cols = Application.InputBox("type in columns required", _
Type:=64, Default:=Array(1, 3, 5))

But this means that the user will be seeing 3 different inputboxes with this
single command.

====
You could prompt them for a string and split the response yourself:

Dim Cols As Variant
Dim resp As String
Dim iCtr As Long
resp = InputBox("type in columns require sepArated by commas ")

If resp = "" Then
Exit Sub
End If

Cols = Split(resp, ",")

For iCtr = LBound(Cols) To UBound(Cols)
'do something
MsgBox Cols(iCtr)
Next iCtr

(Split was added in xl2k--it won't work with xl97, but there is an easy fix.)

====
But if you're asking for columns, I'd try to let the user just use the mouse to
select the range:

Dim Cols As Range
Dim myRng As Range
Dim myCell As Range

Set Cols = Nothing
On Error Resume Next
Set Cols = Application.InputBox("Point at the columns you want to use", _
Type:=8)
On Error GoTo 0

If Cols Is Nothing Then
Exit Sub 'user hit cancel
End If

With Cols.Parent
Set myRng = Intersect(.Rows(1), Cols.EntireColumn)
End With

For Each myCell In myRng.Cells
MsgBox myCell.Column
Next myCell

pete the greek wrote:

i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.

my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

--

Dave Peterson

pete the greek

inputbox type 64 (array)

hi jim thanks for this i still get errors how should the user type the column
in the input book
a:a or 1 what would they used to seperate muiltiple columns as they input them

what i am trying to achieve is i have a 220 column spreadsheet and would
like to be able to ask the user which columns they would like to see and hide
the rest. i can do this by manually creating the array "cols" but thought the
input box would make it more flexible

Set rng = ActiveSheet.UsedRange
lastcol = rng.Columns.Count
lastrow = rng.Rows.Count
cols = Array(2, 56, 79, 122, 165)
rng.Columns.Hidden = True
For Each zz In cols
ActiveSheet.Columns(zz).Hidden = False

Next zz

"Jim Cone" wrote:

Sub ArrayWay()
Dim cols As Variant
'cols is a Variant containing an array of the values in the selection.
cols = Application.InputBox("Select the columns required ", Type:=64)
'Only one of the following will work depending on the selection.
MsgBox cols(1) 'single row
MsgBox cols(1, 1) 'multiple columns
End Sub

Sub RangeWay()
Dim cols As Excel.Range
'cols is a Range object
Set cols = Application.InputBox("Select the columns required ", Type:=8)
'Both of these will work
MsgBox cols(1)
MsgBox cols(1, 1)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"pete the greek"

wrote in message
i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.
my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

pete the greek

inputbox type 64 (array)

thanks dave

ill try some of these i really like the split one i think i can incorperate
that

cheers pete

"Dave Peterson" wrote:

If you know how many responses to retrieve from the user, you could use:

Dim Cols As Variant
Cols = Application.InputBox("type in columns required", _
Type:=64, Default:=Array(1, 3, 5))

But this means that the user will be seeing 3 different inputboxes with this
single command.

====
You could prompt them for a string and split the response yourself:

Dim Cols As Variant
Dim resp As String
Dim iCtr As Long
resp = InputBox("type in columns require sepArated by commas ")

If resp = "" Then
Exit Sub
End If

Cols = Split(resp, ",")

For iCtr = LBound(Cols) To UBound(Cols)
'do something
MsgBox Cols(iCtr)
Next iCtr

(Split was added in xl2k--it won't work with xl97, but there is an easy fix.)

====
But if you're asking for columns, I'd try to let the user just use the mouse to
select the range:

Dim Cols As Range
Dim myRng As Range
Dim myCell As Range

Set Cols = Nothing
On Error Resume Next
Set Cols = Application.InputBox("Point at the columns you want to use", _
Type:=8)
On Error GoTo 0

If Cols Is Nothing Then
Exit Sub 'user hit cancel
End If

With Cols.Parent
Set myRng = Intersect(.Rows(1), Cols.EntireColumn)
End With

For Each myCell In myRng.Cells
MsgBox myCell.Column
Next myCell

pete the greek wrote:

i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.

my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

--

Dave Peterson

Dave Peterson

inputbox type 64 (array)

I don't like the split one. You have to do too much validation to make sure
each entry was actually a column number or letter.

I think using the mouse and getting a range is cleaner.

pete the greek wrote:

thanks dave

ill try some of these i really like the split one i think i can incorperate
that

cheers pete

"Dave Peterson" wrote:

If you know how many responses to retrieve from the user, you could use:

Dim Cols As Variant
Cols = Application.InputBox("type in columns required", _
Type:=64, Default:=Array(1, 3, 5))

But this means that the user will be seeing 3 different inputboxes with this
single command.

====
You could prompt them for a string and split the response yourself:

Dim Cols As Variant
Dim resp As String
Dim iCtr As Long
resp = InputBox("type in columns require sepArated by commas ")

If resp = "" Then
Exit Sub
End If

Cols = Split(resp, ",")

For iCtr = LBound(Cols) To UBound(Cols)
'do something
MsgBox Cols(iCtr)
Next iCtr

(Split was added in xl2k--it won't work with xl97, but there is an easy fix.)

====
But if you're asking for columns, I'd try to let the user just use the mouse to
select the range:

Dim Cols As Range
Dim myRng As Range
Dim myCell As Range

Set Cols = Nothing
On Error Resume Next
Set Cols = Application.InputBox("Point at the columns you want to use", _
Type:=8)
On Error GoTo 0

If Cols Is Nothing Then
Exit Sub 'user hit cancel
End If

With Cols.Parent
Set myRng = Intersect(.Rows(1), Cols.EntireColumn)
End With

For Each myCell In myRng.Cells
MsgBox myCell.Column
Next myCell

pete the greek wrote:

i assume this will let the user input an array of values. what should the
user use as a seperater as they enter the array values.

my code is

Set cols = Application.InputBox("type in columns require seperated by
commas", Type:=64)

i get a formula error box no matter what i type in the input box i have
tried commas, space quotes

--

Dave Peterson

--

Dave Peterson

Jim Cone

inputbox type 64 (array)

PTG,
Try playing around with this sub.
Note the instructions in the Input Box.
Another way would be to use a Textbox on a UserForm and
limit which characters can be entered in the textbox.
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

Sub WhichColumns()
Dim cols As Excel.Range
Dim rngC As Excel.Range
Dim rngA As Excel.Range

ActiveSheet.Columns.Hidden = False
On Error Resume Next
Set cols = Application.InputBox(vbCr & _
"Hold down the Ctrl key and select the columns required. ", Type:=8)
If Err.Number < 0 Then
Exit Sub
End If
ActiveSheet.UsedRange.EntireColumn.Hidden = True
For Each rngA In cols.Areas
For Each rngC In rngA.Columns
rngC.Hidden = False
Next 'rngC
Next 'rngA
ActiveWindow.ScrollColumn = 1
End Sub
'-------------

"pete the greek"

wrote in message
hi jim thanks for this i still get errors how should the user type the column
in the input book
a:a or 1 what would they used to seperate muiltiple columns as they input them

what i am trying to achieve is i have a 220 column spreadsheet and would
like to be able to ask the user which columns they would like to see and hide
the rest. i can do this by manually creating the array "cols" but thought the
input box would make it more flexible

Set rng = ActiveSheet.UsedRange
lastcol = rng.Columns.Count
lastrow = rng.Rows.Count
cols = Array(2, 56, 79, 122, 165)
rng.Columns.Hidden = True
For Each zz In cols
ActiveSheet.Columns(zz).Hidden = False

Next zz

Jim Cone

inputbox type 64 (array)

Add "On Error GoTo 0" just before...
"ActiveSheet.UsedRange.EntireColumn.Hidden = True"
Jim Cone

All times are GMT +1. The time now is 11:49 PM.