ListBox does not support saving

  • Thread starter Thread starter Alok
  • Start date Start date
A

Alok

Hello Experts

I have recently started getting an error in Excel 2003 SP3.

I have a ListBox (from Control Toolbox) with name lstExistingProducts.

When Excel does an automatic save of the workbook or when I do a save,
I get the message that the "Control lstExistingProducts does not
support saving". The message suggests that I should delete the control
and then save the workbook.

Can you please help in case you have come across this problem.
 
Posting the code that causes the error message will usually get a better
response.









- Show quoted text -
Click to expand...

Here is the code:

'Background:
'A list box shows the list of existing products.
'The list box is called lstExistingProducts.
'The ListFillRange of the lstExistingProducts is called ListOfProducts
'DoDeleteProduct routine deletes the Product in the database.
'The routine RefreshCurrentProductList refreshes the ListOfProducts
range from database
'ListExistingProducts.ListFillRange is again set so that a refresh of
the ListBox takes place
'This line causes the problem. "Could not set the ListCursor property.
Not enough storage is available to complete this operation"
'After getting this error, I would get the following error:
'Microsoft Office Excel cannot save this workbook because the control
named lstExistingProducts does not support saving. To save the
workbook, delete this control, and then try the save again."
'Note that the problem happened only if the last item in the
lstExistingProducts had been clicked on for deletion.
'I was able to resolve the problem by first unselecting the selected
item by adding the code
'lstExistingProducts.ListIndex = -1 between
'RefreshCurrentProductList and
'lstExistingProducts.ListFillRange = "ListOfProducts"

Private Sub butDeleteProduct_Click()
Dim sExistingProduct$

sExistingProduct = lstExistingProducts.Text
If sExistingProduct <> "" Then
If MsgBox("Do you want to delete " & sExistingProduct & "?",
vbYesNo) = vbYes Then
DoDeleteProduct sExistingProduct
RefreshCurrentProductsList
lstExistingProducts.ListFillRange = "ListOfProducts"
End If
Else
MsgBox "Please note that you must first select a product and
then click on the 'Delete Product' button."
End If
End Sub

As you will see from the background the problem was solved in a
roundabout way by me. If you have another solution, please let me
know.
 
Glad you found a solution.

Alok said:
Here is the code:

'Background:
'A list box shows the list of existing products.
'The list box is called lstExistingProducts.
'The ListFillRange of the lstExistingProducts is called ListOfProducts
'DoDeleteProduct routine deletes the Product in the database.
'The routine RefreshCurrentProductList refreshes the ListOfProducts
range from database
'ListExistingProducts.ListFillRange is again set so that a refresh of
the ListBox takes place
'This line causes the problem. "Could not set the ListCursor property.
Not enough storage is available to complete this operation"
'After getting this error, I would get the following error:
'Microsoft Office Excel cannot save this workbook because the control
named lstExistingProducts does not support saving. To save the
workbook, delete this control, and then try the save again."
'Note that the problem happened only if the last item in the
lstExistingProducts had been clicked on for deletion.
'I was able to resolve the problem by first unselecting the selected
item by adding the code
'lstExistingProducts.ListIndex = -1 between
'RefreshCurrentProductList and
'lstExistingProducts.ListFillRange = "ListOfProducts"

Private Sub butDeleteProduct_Click()
Dim sExistingProduct$

sExistingProduct = lstExistingProducts.Text
If sExistingProduct <> "" Then
If MsgBox("Do you want to delete " & sExistingProduct & "?",
vbYesNo) = vbYes Then
DoDeleteProduct sExistingProduct
RefreshCurrentProductsList
lstExistingProducts.ListFillRange = "ListOfProducts"
End If
Else
MsgBox "Please note that you must first select a product and
then click on the 'Delete Product' button."
End If
End Sub

As you will see from the background the problem was solved in a
roundabout way by me. If you have another solution, please let me
know.
Click to expand...
 
I realize that this is an old thread, however your solution of:

'I was able to resolve the problem by first unselecting the selected
item by adding the code
'lstExistingProducts.ListIndex = -1 between
'RefreshCurrentProductList and
'lstExistingProducts.ListFillRange = "ListOfProducts"

solved my problem that was making me pull my hair out!

I was getting the dreaded: "Not Enough storage is available to complete this operation" when I was doing anything to a ListBox on a UserForm that was visible. I couldn't change any properties or the RowSource etc...

When I set the ListBox1.ListIndex = -1 before changing any of the properties and then changing it back when needed SOLVED my problem. (I'm not sure why it matters)

Just an FYI in case someone else has a problem with this dreaded error.

Thanks
 
Back
Top