Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
from Range variable | Excel Discussion (Misc queries) | |||
Condensing a list/range with blank cells to a new list/range without blanks | Excel Worksheet Functions | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Using Variable in RANGE | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |