MORE combobox questions
 

I have the following code:

With Me.ComboBox1.TopLeftCell
..Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

MORE combobox questions
 

Dim v as Variant
v = worksheets("Sheet1").Range("C2:C5").Value

v is now a 4 x 1 2-dimensional array.

but why not do

With Me.ComboBox1.TopLeftCell
..Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End with

--
Regards,
Tom Ogilvy






"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

MORE combobox questions
 

THANK YOU SO MUCH. I was struggling with this problem for over two hours!!
THANK YOU TOM
"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

MORE combobox questions
 

ONE MORE QUESTION...
WHat is the function of the command line: Resize(, 4) in my code below?
It seems to limit my list to only displaying 4 outputs. How would I go about
not having a limit but rather to show simply the range C2:C5 or any other
range without me having to change the Resize(,4) value

"Tom Ogilvy" wrote:

Dim v as Variant
v = worksheets("Sheet1").Range("C2:C5").Value

v is now a 4 x 1 2-dimensional array.

but why not do

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End with

--
Regards,
Tom Ogilvy






"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

MORE combobox questions
 

somerng.resize(x,y)
will resize that original range (somerng) so that it's x rows by y columns.

If you don't give it an x or y, then that row or column isn't changed.

So in Tom's code:
Me.ComboBox1.TopLeftCell.Offset(0, 5).Resize(, 4)
He started in the .topleftcell
went 5 columns to the right, but stayed on the same row (.offset(0,5))
and then made the range the same number of rows (1) but with 4 columns.

And he used 4 because there are 4 cells in C2:C5.




N.F wrote:

ONE MORE QUESTION...
WHat is the function of the command line: Resize(, 4) in my code below?
It seems to limit my list to only displaying 4 outputs. How would I go about
not having a limit but rather to show simply the range C2:C5 or any other
range without me having to change the Resize(,4) value

"Tom Ogilvy" wrote:

Dim v as Variant
v = worksheets("Sheet1").Range("C2:C5").Value

v is now a 4 x 1 2-dimensional array.

but why not do

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End with

--
Regards,
Tom Ogilvy






"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

--

Dave Peterson

MORE combobox questions
 

Ok I see. Now that youre here Dave, I was wondering if you know how to have a
"Reset" command button that will erase the list that was displayed by my
following code:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "F" Then
With Me.ComboBox1.TopLeftCell
..Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End With
End If
End Sub





"Dave Peterson" wrote:

somerng.resize(x,y)
will resize that original range (somerng) so that it's x rows by y columns.

If you don't give it an x or y, then that row or column isn't changed.

So in Tom's code:
Me.ComboBox1.TopLeftCell.Offset(0, 5).Resize(, 4)
He started in the .topleftcell
went 5 columns to the right, but stayed on the same row (.offset(0,5))
and then made the range the same number of rows (1) but with 4 columns.

And he used 4 because there are 4 cells in C2:C5.




N.F wrote:

ONE MORE QUESTION...
WHat is the function of the command line: Resize(, 4) in my code below?
It seems to limit my list to only displaying 4 outputs. How would I go about
not having a limit but rather to show simply the range C2:C5 or any other
range without me having to change the Resize(,4) value

"Tom Ogilvy" wrote:

Dim v as Variant
v = worksheets("Sheet1").Range("C2:C5").Value

v is now a 4 x 1 2-dimensional array.

but why not do

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End with

--
Regards,
Tom Ogilvy






"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

--

Dave Peterson

MORE combobox questions
 

Put a button from the Control Toolbox toolbar and use this code under it:

Option Explicit
Private Sub CommandButton1_Click()
With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4).clearcontents
End With
End Sub

N.F wrote:

Ok I see. Now that youre here Dave, I was wondering if you know how to have a
"Reset" command button that will erase the list that was displayed by my
following code:

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "F" Then
With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End With
End If
End Sub

"Dave Peterson" wrote:

somerng.resize(x,y)
will resize that original range (somerng) so that it's x rows by y columns.

If you don't give it an x or y, then that row or column isn't changed.

So in Tom's code:
Me.ComboBox1.TopLeftCell.Offset(0, 5).Resize(, 4)
He started in the .topleftcell
went 5 columns to the right, but stayed on the same row (.offset(0,5))
and then made the range the same number of rows (1) but with 4 columns.

And he used 4 because there are 4 cells in C2:C5.




N.F wrote:

ONE MORE QUESTION...
WHat is the function of the command line: Resize(, 4) in my code below?
It seems to limit my list to only displaying 4 outputs. How would I go about
not having a limit but rather to show simply the range C2:C5 or any other
range without me having to change the Resize(,4) value

"Tom Ogilvy" wrote:

Dim v as Variant
v = worksheets("Sheet1").Range("C2:C5").Value

v is now a 4 x 1 2-dimensional array.

but why not do

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = Application.Transpose( _
Worksheets("Sheet1").Range("C2:C5"))
End with

--
Regards,
Tom Ogilvy






"N.F" wrote:

I have the following code:

With Me.ComboBox1.TopLeftCell
.Offset(0, 5).Resize(, 4) = _
Array("NIN", "NOUT", "ZALT", "ZDTAMB")
End With

How do i tell the program that instead of me inputing the "NIN, "NOUT",
"ZALT" values myself in the Array command to simply have an something like:

Array("Sheet2!C2:C5)
The reason I want this is because I have a very long list I would other wise
need to input in the Array Command.

--

Dave Peterson


--

Dave Peterson