Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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 " separatorType:=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.
  1. If the user's regional settings use a comma as the list separator, the prompt will say "Type in columns separated by ,".
  2. If the user's regional settings use a semicolon as the list separator, the prompt will say "Type in columns separated by ;".
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,290
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,290
Default 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


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,290
Default inputbox type 64 (array)

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying an Array BDH Excel Worksheet Functions 0 February 21st 06 05:11 AM
Transpose words and numbers into array of different proportions Manfred Excel Discussion (Misc queries) 5 February 9th 06 02:07 AM
Problem with Vlookup array selection Scott269 Excel Worksheet Functions 2 January 30th 06 06:29 PM
Excel array formulas Les Gordon Excel Discussion (Misc queries) 1 September 3rd 05 04:12 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 11:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"