Empty listbox
 

I have a listbox on my worksheet and have added some items to it
using:

Sub temp()
With Sheets("Month").ListBox1
.AddItem ("Yesterday")
.AddItem ("Today")
.AddItem ("Tomorrow")
End With
End Sub

That works fine but when I close the sheet and open it again the
entries disappear. I can briefly see them and then the list is empty.
I have removed all ThisWorkbook code and I disable macros when the
spreadsheet opens. But the entries still disappear. What's going on?

TIA,
Derek

Empty listbox
 

Derek,

What do you mean by close the sheet? Do you mean you close the workbook
completely? If so, the listbox will not have those values as it will be
re-initialised upon opening the workbook. What you would need to do is put
that code into the Workbook_Open event, so that it is automatically loaded
each time. Either that, or put the values in a worksheet range, and set the
RowSource property to point at that range in the control design.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Derek Gadd" wrote in message
om...
I have a listbox on my worksheet and have added some items to it
using:

Sub temp()
With Sheets("Month").ListBox1
.AddItem ("Yesterday")
.AddItem ("Today")
.AddItem ("Tomorrow")
End With
End Sub

That works fine but when I close the sheet and open it again the
entries disappear. I can briefly see them and then the list is empty.
I have removed all ThisWorkbook code and I disable macros when the
spreadsheet opens. But the entries still disappear. What's going on?

TIA,
Derek

Empty listbox
 

Yes, I meant close the workbook completely. If I were to set the
RowSource property, where would the best place be to place the code?
Workbook_open, initialize? Anyway, RowSource wasn't shown in the
properties window but ListFillRange was and I used this with the
values located in the worksheet. That works fine, is it the same
thing? (I'm using Excel 2000.)

Thanks,
Derek

"Bob Phillips" wrote in message ...
Derek,

What do you mean by close the sheet? Do you mean you close the workbook
completely? If so, the listbox will not have those values as it will be
re-initialised upon opening the workbook. What you would need to do is put
that code into the Workbook_Open event, so that it is automatically loaded
each time. Either that, or put the values in a worksheet range, and set the
RowSource property to point at that range in the control design.

Empty listbox
 

In the Workbook_Open event macro. This goes in the ThisWorkbook code
module. If you select from the left-hand drop-down, you can pick Workbook,
and the then the open event macro is created in base form.

ListFillRange is the correct property, I get the names mixed up.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Derek Gadd" wrote in message
om...
Yes, I meant close the workbook completely. If I were to set the
RowSource property, where would the best place be to place the code?
Workbook_open, initialize? Anyway, RowSource wasn't shown in the
properties window but ListFillRange was and I used this with the
values located in the worksheet. That works fine, is it the same
thing? (I'm using Excel 2000.)

Thanks,
Derek

"Bob Phillips" wrote in message
...
Derek,

What do you mean by close the sheet? Do you mean you close the workbook
completely? If so, the listbox will not have those values as it will be
re-initialised upon opening the workbook. What you would need to do is
put
that code into the Workbook_Open event, so that it is automatically
loaded
each time. Either that, or put the values in a worksheet range, and set
the
RowSource property to point at that range in the control design.