| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refreshing a listbox
I have been stumped with a listbox added to a worksheet using the control
toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
See earlier posting the answer is
Sheets("Home").OLEObjects("ListBox1").ListFillRang e = _ Mid(Names("wrkshtrng").Value, 2) Using Range("rng").address doesn't include the sheet name. The only way of getting the sheet name is using names, but names include an equal sign at the beginning. the MID removes the equal sign. "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
I just noticed that the address that is being put into the listfillrange
property from the code that I am using is from the same sheet. The datarange is on a different sheet that the listbox. I tried: Sheets("name").OLEObjects("Listbox1").ListFillRang e = sheets("data").Range("rng").address I still get the same results "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
The sheet that contains the listbox is named 'Home'
The listbox name is 'Listbox1' The sheet that contains the datarange for the listbox is named 'Current DB' The name of the data range is 'wrkshtrng' I noticed that when I ran the code you suggested in the earlier posts Sheets("Home").OLEObjects("Listbox1").ListFillRang e = Range("wrkshtrng").address The range for the ListFillRange changed to $D$6:$D$7 which is in the sheet that contains the listbox. Not the sheet that contains the datarange. So I tried Sheets("Home").OLEObjects("ListBox1").ListFillRang e = sheets("Current DB").Range("wrkshtrng").Address I got the same results. How do I get the address to be in the right worksheet? "Joel" wrote: See earlier posting the answer is Sheets("Home").OLEObjects("ListBox1").ListFillRang e = _ Mid(Names("wrkshtrng").Value, 2) Using Range("rng").address doesn't include the sheet name. The only way of getting the sheet name is using names, but names include an equal sign at the beginning. the MID removes the equal sign. "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
Does this test code get the correct worksheet?
Sub test() With Sheets("current db") Set xcell = .Range("B4") num = 10 .Range(xcell.Offset.Offset(1, 0), _ xcell.Offset(num, 0)).Name = "wrkshtrng" End With a = Mid(Names("wrkshtrng").Value, 2) End Sub "ranswrt" wrote: I just noticed that the address that is being put into the listfillrange property from the code that I am using is from the same sheet. The datarange is on a different sheet that the listbox. I tried: Sheets("name").OLEObjects("Listbox1").ListFillRang e = sheets("data").Range("rng").address I still get the same results "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
Yes it does
"Joel" wrote: Does this test code get the correct worksheet? Sub test() With Sheets("current db") Set xcell = .Range("B4") num = 10 .Range(xcell.Offset.Offset(1, 0), _ xcell.Offset(num, 0)).Name = "wrkshtrng" End With a = Mid(Names("wrkshtrng").Value, 2) End Sub "ranswrt" wrote: I just noticed that the address that is being put into the listfillrange property from the code that I am using is from the same sheet. The datarange is on a different sheet that the listbox. I tried: Sheets("name").OLEObjects("Listbox1").ListFillRang e = sheets("data").Range("rng").address I still get the same results "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
Instead of starting new threads, you should follow up to the original post.
ranswrt wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
I didn't know if they would still get looked at as they got further down the
list. "Dave Peterson" wrote: Instead of starting new threads, you should follow up to the original post. ranswrt wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
Not sure from your response if your problem is solved. The problem must be
that xcell isn't set to the correct worksheet in your code. "ranswrt" wrote: Yes it does "Joel" wrote: Does this test code get the correct worksheet? Sub test() With Sheets("current db") Set xcell = .Range("B4") num = 10 .Range(xcell.Offset.Offset(1, 0), _ xcell.Offset(num, 0)).Name = "wrkshtrng" End With a = Mid(Names("wrkshtrng").Value, 2) End Sub "ranswrt" wrote: I just noticed that the address that is being put into the listfillrange property from the code that I am using is from the same sheet. The datarange is on a different sheet that the listbox. I tried: Sheets("name").OLEObjects("Listbox1").ListFillRang e = sheets("data").Range("rng").address I still get the same results "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
I changed
Sheets("Current DB") to worksheets("Current DB") and added (external:=True) That seemed to have fixed the problem. Thanks for your help "Joel" wrote: Not sure from your response if your problem is solved. The problem must be that xcell isn't set to the correct worksheet in your code. "ranswrt" wrote: Yes it does "Joel" wrote: Does this test code get the correct worksheet? Sub test() With Sheets("current db") Set xcell = .Range("B4") num = 10 .Range(xcell.Offset.Offset(1, 0), _ xcell.Offset(num, 0)).Name = "wrkshtrng" End With a = Mid(Names("wrkshtrng").Value, 2) End Sub "ranswrt" wrote: I just noticed that the address that is being put into the listfillrange property from the code that I am using is from the same sheet. The datarange is on a different sheet that the listbox. I tried: Sheets("name").OLEObjects("Listbox1").ListFillRang e = sheets("data").Range("rng").address I still get the same results "ranswrt" wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing a listbox
They will.
ranswrt wrote: I didn't know if they would still get looked at as they got further down the list. "Dave Peterson" wrote: Instead of starting new threads, you should follow up to the original post. ranswrt wrote: I have been stumped with a listbox added to a worksheet using the control toolbox. I have been having problems refreshing the listbox when the data range is changed. I have been using the following code to refresh it: Sheets("name").OLEObjects("Listbox1").ListFillRang e = Range("rng").address I have been getting a blank listbox. I can manually enter the range in the property window for the listfillrange, but when the range is changed the listbox isn't updated. Is there a better way to update the listbox? Thanks -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Listbox not refreshing on update | Excel Programming | |||
| Refreshing a userform/listbox | Excel Programming | |||
| VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
| listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
| Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Linear Mode
