inputbox type 64 (array)

[pete the greek]

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

 

[Jim Cone]

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"
<[email protected]>
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]

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]

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

 

[pete the greek]

thanks dave

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

cheers pete

 

[Dave Peterson]

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.

 

[Jim Cone]

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"
<[email protected]>
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]

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