Variable List Box Range

[peter_rivera]

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

 

[Dave Peterson]

#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]

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!!!

 

[Dave Peterson]

I'm kind of confused, but maybe replacing:

With Sheet1
with
With worksheets(g_country)

would do it.

 

[peter_rivera]

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

 

[Dave Peterson]

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

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!

 

[Dave Peterson]

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]

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!!

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

I don't have any other guesses.

Sorry.

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.