refresh listbox

S

Scottcmu

Board Regular

Joined

Aug 1, 2002

Messages

150

• Aug 30, 2004

• #2

If I'm understanding you correctly, you should change the default value to "Choose page" or something like that. You could then choose the page you want. See if that works.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Aug 30, 2004

• #3

You don't say which type of listbox you are using.

Rather than use a named range for the source of the listbox
use Additem to add each worksheet name to it.

If it is from the forms toolbar you could use the following code to populate it:

Code:

Sub test()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ActiveSheet.ListBoxes(1).AddItem ws.Name
Next

End Sub

If it's from the Control Toolbox you could use the following:

Code:

For Each ws In ActiveWorkbook.Worksheets
       ActiveSheet.ListBox1.AddItem ws.Name
Next

And for one on a userform:

Code:

For Each ws In ActiveWorkbook.Worksheets
       UseForm1.ListBox1.AddItem ws.Name
Next

Obviously change sheet/form/control names as required.

 

Upvote 0

Davers

Well-known Member

Joined

Sep 17, 2002

Messages

1,165

• Aug 30, 2004

• #4

I'm not sure if I explained myself well enough...lets see. I have a named range that is called myRng. A list box has it's "ListFillRange" set to myRng. When I open the workbook....my named range may change from A2:A6 to A2:A7 if I've added a sheet. My list box should now be looking at A2:A7...but it's not, it's still looking at A2:A6. It seems that the property for "ListFillRange" didn't update with the new named range, even though I see it in there.

I hope I've explained that a bit better. Thanks for the help,

Dave

 

Upvote 0

R

rickolh

Board Regular

Joined

Oct 20, 2003

Messages

240

• Aug 30, 2004

• #5

try these

listbox1.listfillrange = ""
listbox1.listfillrange = "myRng"

hope it helps

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Aug 30, 2004

• #6

Dave

Have you tried any of my suggestions, they eliminate the need to use a named range.

 

Upvote 0

Davers

Well-known Member

Joined

Sep 17, 2002

Messages

1,165

• Aug 30, 2004

• #7

Hey guys, you keep answering way too fast.... I'm feverishly trying them.... :wink:

I'll update in a sec....

Thanks again!!!

Dave

 

Upvote 0

Davers

Well-known Member

Joined

Sep 17, 2002

Messages

1,165

• Aug 30, 2004

• #8

Hey Norie...I like this one!

Code:

For Each ws In ActiveWorkbook.Worksheets 
       ActiveSheet.ListBox1.AddItem ws.Name 
Next

Thanks for the help everyone...I appreciate it!

Have a good day,

Dave

 

Upvote 0