Variable List Box Range
 

Hi all, thanks for the help on my previous questions.
I have two new ones:

1. First, I would like it if whenever someone scrolls down on a listbo
using the arrow keys that the linked textboxes would automaticall
update their info.

2. Second, is there a way to code the rowsource of a listbox so tha
its range is always equal to the rows of its source spreadsheet? Th
spreadsheet is generated aoutomatically and the number of record
varies from customer to customer.

Thanks!
Pete

--
peter_river
-----------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...fo&userid=2449
View this thread: http://www.excelforum.com/showthread.php?threadid=38159

Variable List Box Range
 

#1. I put a listbox from the control toolbox toolbar on a worksheet. I
assigned its listfillrange to A1:a4. I assigned its linkedcell to B1

When I used the arrow key to scroll through the listbox, the linkedcell changed.

#2. You could assign the listbox's listfillrange when the workbook opens.

Option Explicit
Private Sub Workbook_Open()
With Sheet1
.ListBox1.ListFillRange = ""
.ListBox1.List = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With
End Sub

peter_rivera wrote:

Hi all, thanks for the help on my previous questions.
I have two new ones:

1. First, I would like it if whenever someone scrolls down on a listbox
using the arrow keys that the linked textboxes would automatically
update their info.

2. Second, is there a way to code the rowsource of a listbox so that
its range is always equal to the rows of its source spreadsheet? The
spreadsheet is generated aoutomatically and the number of records
varies from customer to customer.

Thanks!
Peter

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson

Variable List Box Range
 

Thanks for the code help, but I am still encountering errors with my
userform.

The main problem is that Sheet1 is a sheet which name is generated by
another userform. For example, from a list of customer types
(gov't/business/etc...) a sheet with all the accompanying data is
created. I need to pass the name of the sheet created by userform1

The following is a portion of the code I am using:

Public g_Country As String


Select Case ListSponsorBox.Value

Case "AUSTRIA"
g_Country = "AUSTRIA"
Worksheets("Prior_User_Input_Data").Activate
Call DataSort
Call CountrySheet

Sheets.Add.Name = g_Country
Worksheets(g_Country).Activate


Any thought as to how I can link this with the code:

With Sheet1
..ListBox1.ListFillRange = ""
..ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

that Dave has told me? Thanks very much for all of your help!!!


--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

Variable List Box Range
 

I'm kind of confused, but maybe replacing:

With Sheet1
with
With worksheets(g_country)

would do it.

peter_rivera wrote:

Thanks for the code help, but I am still encountering errors with my
userform.

The main problem is that Sheet1 is a sheet which name is generated by
another userform. For example, from a list of customer types
(gov't/business/etc...) a sheet with all the accompanying data is
created. I need to pass the name of the sheet created by userform1

The following is a portion of the code I am using:

Public g_Country As String

Select Case ListSponsorBox.Value

Case "AUSTRIA"
g_Country = "AUSTRIA"
Worksheets("Prior_User_Input_Data").Activate
Call DataSort
Call CountrySheet

Sheets.Add.Name = g_Country
Worksheets(g_Country).Activate

Any thought as to how I can link this with the code:

With Sheet1
ListBox1.ListFillRange = ""
ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

that Dave has told me? Thanks very much for all of your help!!!

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson

Variable List Box Range
 

I figured what my problem was. I was declaring a public variable in th
wrong place. I have another problem though as the code:

Private Sub Listbox1_Change()

With Worksheets(g_Country)
.ListBox1.ListFillRange = ""
.ListBox1.List = .Range("a1", .Cells(.Rows.Count
"A").End(xlUp)).Value
End With

does not work. Any thoughts? Thanks

--
peter_river
-----------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...fo&userid=2449
View this thread: http://www.excelforum.com/showthread.php?threadid=38159

Variable List Box Range
 

It looks ok to me. What part of it breaks?



peter_rivera wrote:

I figured what my problem was. I was declaring a public variable in the
wrong place. I have another problem though as the code:

Private Sub Listbox1_Change()

With Worksheets(g_Country)
ListBox1.ListFillRange = ""
ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

does not work. Any thoughts? Thanks!

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson

Variable List Box Range
 

I didn't notice that this was in the Listbox1_change() sub.

Is that were you wanted it? That seems pretty unusual to me.



peter_rivera wrote:

I figured what my problem was. I was declaring a public variable in the
wrong place. I have another problem though as the code:

Private Sub Listbox1_Change()

With Worksheets(g_Country)
ListBox1.ListFillRange = ""
ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

does not work. Any thoughts? Thanks!

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson

Variable List Box Range
 

Hi Dave, this is the code:

Private Sub Listbox1_Change()

With Sheets(g_Country)
..ListBox1.ListFillRange = ""
..ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Dim rng As Range
Set rng = Range(ListBox1.RowSource).Columns(1).Cells
Set rng = rng(ListBox1.ListIndex + 1)

Dim rng As Range
Set rng = Range(ListBox1.RowSource).Columns(1).Cells
Set rng = rng(ListBox1.ListIndex + 1)
Let text1.Text = rng.Text
Let text2.Text = rng.Offset(0, 1).Text
etc....


However, everytime this is run I get an error 438 as ListFillRange is
not supported.
How do I correct this? Thanks!!


--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

Variable List Box Range
 

Your code worked ok for me.

With Sheets(g_Country)
.ListBox1.ListFillRange = ""
.ListBox1.List = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With

(I did have to insert dots in front of the .listbox1.listfillrange and
..listbox1.list lines, though.)

You sure you have a listbox from the control toolbox toolbar on that g_Country
worksheet?

peter_rivera wrote:

Hi Dave, this is the code:

Private Sub Listbox1_Change()

With Sheets(g_Country)
ListBox1.ListFillRange = ""
ListBox1.List = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Dim rng As Range
Set rng = Range(ListBox1.RowSource).Columns(1).Cells
Set rng = rng(ListBox1.ListIndex + 1)

Dim rng As Range
Set rng = Range(ListBox1.RowSource).Columns(1).Cells
Set rng = rng(ListBox1.ListIndex + 1)
Let text1.Text = rng.Text
Let text2.Text = rng.Offset(0, 1).Text
etc....

However, everytime this is run I get an error 438 as ListFillRange is
not supported.
How do I correct this? Thanks!!

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson

Variable List Box Range
 

Hi Dave,

Thanks for your help so far. I still get the dreaded 438 error from th
code.
A few things:

1. g_Country is a spreadsheet generated by a user choices fro
Userform1. After the country choice is made, then this spreadsheet i
created and another userform appears.

2. The second userform is necessary so the user can only look at th
information related to his country. It is not a worksheet.

Basically, when the country spreadsheet is generated, I want the secon
userform to pop up displaying only the records found on the worksheet.
hope this makes things clearer. I really appreciate your help thus far

--
peter_river
-----------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...fo&userid=2449
View this thread: http://www.excelforum.com/showthread.php?threadid=38159

Variable List Box Range
 

I don't have any other guesses.

Sorry.

peter_rivera wrote:

Hi Dave,

Thanks for your help so far. I still get the dreaded 438 error from the
code.
A few things:

1. g_Country is a spreadsheet generated by a user choices from
Userform1. After the country choice is made, then this spreadsheet is
created and another userform appears.

2. The second userform is necessary so the user can only look at the
information related to his country. It is not a worksheet.

Basically, when the country spreadsheet is generated, I want the second
userform to pop up displaying only the records found on the worksheet. I
hope this makes things clearer. I really appreciate your help thus far.

--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=381591

--

Dave Peterson