deleting item from listbox

[sspicer]

I have the following code which will delete a sheet from the spreadsheet when it is selected in the userform, but I need this also to be deleted from the list in the userform so that it is not there next time it is opened.

I have tried:

listbox1.removeitem(lItem) before the endif, but this doesn't seem to work.

Private Sub CommandButton1_Click()
Dim lItem As Long
    
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets(ListBox1.List(lItem)).Delete
        End If
    Next
    
    UserForm1.Hide
    
End Sub

I am not sure that each time the userform is opened that it fills the list with the correct sheets. Will it retain those that were entered on the last open?

I hope you can help me! I've been struggling with this for a few days!

Sam

 

[hatman]

Depending upon how you are calling the userform will determine how the object is being instantiated. If you are explicitly creating a new userform object and setting it equal to your template, and populating your listbox in the intialize event of the userform (the preferred method) then the listbox should only contain entries corresponding to the sheets that exist at that time. If, however, you allow the object to be created implicitly (not preferred, but allowed by VBA and a pretty common usage among many self-taught programmers) then the contents of the listbox will be persistant between calls, unles sthe entire project is reset.

The most stable fix would be to reconstruct your code to explicitly create your object. Assuming the name of your userform is uForm1, the code in your code module may look something like this:

Sub Stuff()
    Dim uf As UForm1
    
    Set uf = UForm1
    
    uf.Show
    
    'do stuff here with the data in the userform, if needed
    
    Set uf = Nothing
End Sub

When the line "Set uf = UForm1" is executed, the code in teh userform's Initialize Event is executed, making that a good place to put the code which populates the listbox. There are other options for this code placement, but unles syou are planning on populating the listbox with a different list depending upon what code is calling the form, this is a good solid architecture.

All that being said, the Removeitem method is the proper one for deleting an entry from a list box. Typically, the parentheses are not needed when implementing a method (unless it's part of a larger expression) but in this case, the method works using either syntax...

 

[sspicer]

Thanks - I've done this and it still opens up the next time the sheets that have already been deleted in the code. I'll show you all the code here.

Code to set up form:

Sub ExistingReports()
    Dim uf As UserForm    
    Set uf = UserForm1
    UserForm1.Show
    Set uf = Nothing
End Sub

Code to run on initialise:

Private Sub UserForm_initialize()
  
    Dim sht As Worksheet
    Dim NoSheets As Integer
    ListBox1.MultiSelect = fmMultiSelectMulti
    For Each sht In Worksheets
    Select Case sht.Name
        Case "Control"
        Case "Scheme Input"
        Case "Member Input"
        Case "Member Data"
        Case "Developer Notes"
        Case "Working Info"
        Case "Claims Input"
        Case Else
            ListBox1.AddItem sht.Name
        End Select
    Next sht
'greys out the action buttons if nothing has been selected
    If ListBox1.ListIndex = -1 Then
        With UserForm1
                .CommandButton1.Enabled = False
                .CommandButton2.Enabled = False
        End With
    Else
        With UserForm1
                .CommandButton1.Enabled = True
                .CommandButton2.Enabled = True
        End With
        
    End If
    
End Sub

and then code for deletion:

Private Sub CommandButton1_Click()
Dim lItem As Long
    
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets(ListBox1.List(lItem)).Delete
        End If
    Next
    
    UserForm1.Hide
    
End Sub

I can't see what i'm doing wrong?!

 

[hatman]

Okay, it looks like your initialize code is adding all sheet names EXCEPT those 7. Is that correct? And yet you are still getting sheets (other than the 7 named) showing up in the listbox that have been deleted when the code was run previously? I won't have a chance to test your code today, but unless you have something else going on (like the RowSource property of the listbox set...), This should work as expected:

1) execute ExistingReports
2) Select a sheet from the list
3) Depress commandbutton (delete selected sheet)
4) hide form
5) Set object to nothing, ths destroying the instance of the form
6) Execute ExistingReports
7) Create a NEW userform object, re-populate the list

 

[Scott.C.Lyerly]

Is your listbox set up as a MultiSelect? If so, you may want to try something as simple as stepping backwards instead of forwards for deleting.

Such as:

Private Sub CommandButton1_Click()
Dim lItem As Long
 
    For lItem = [COLOR=red]ListBox1.ListCount - 1 to 0 Step -1[/COLOR]
        If ListBox1.Selected(lItem) = True Then
              Sheets(ListBox1.List(lItem)).Delete
              [COLOR=red]Listbox1.RemoveItem(lItem)[/COLOR]
        End If
    Next
 
    UserForm1.Hide
 
End Sub

 

[hatman]

You're going to LOVE this one. You actually have several issues going on, related to object persistance and auto-instantiation.

The first problem is my fault: I forgot the NEW keyword in the code snippet I gave you (or more accurately, I forgot to UNLOAD the userform from memory) so the previous instance of the userform could be loaded from memory (along with the OLD list), rather than creating a new instance, which would raise the initialize event and create a NEW list based on teh current workbook configuration.

The second problem is that in implementing the code snippet I gave you, you inadvertantly left several references to UserForm1 in your code (partiallymy own fault since I didn't tell you otherwise). Unfortunately, this has the effect of autoinstantiating additional UserForm1 objects... thus creating additional persistant objects that contain old data.

Try the code below, it should solve the problem. Notice that I replaced UserForm1.Show with UF.Show (eliminating the first redundant instantiation of the form), and in the Form Code, I replaced Userform1 with Me (eliminating the remaining redundant instantiation problems). If you are inetrested in seeing the problem in action, simply step through your current code with teh F8 key... you will notice that the Form Initialize code will run twice... this only happens if you are spawning multiple instances of the form.

Sub ExistingReports()
    Dim uf As UserForm1
    Set uf = UserForm1
    uf.Show
    Unload uf
 
    Set uf = Nothing
End Sub

Private Sub UserForm_Initialize()
    Dim sht As Worksheet
    Dim NoSheets As Integer
    ListBox1.MultiSelect = fmMultiSelectMulti
    For Each sht In Worksheets
    Select Case sht.Name
        Case "Control"
        Case "Scheme Input"
        Case "Member Input"
        Case "Member Data"
        Case "Developer Notes"
        Case "Working Info"
        Case "Claims Input"
        Case Else
            ListBox1.AddItem sht.Name
        End Select
    Next sht
'greys out the action buttons if nothing has been selected
    If ListBox1.ListIndex = -1 Then
        With Me
                .CommandButton1.Enabled = False
                .CommandButton2.Enabled = False
        End With
    Else
        With Me
                .CommandButton1.Enabled = True
                .CommandButton2.Enabled = True
        End With
 
    End If
 
End Sub

Private Sub CommandButton1_Click()
Dim lItem As Long
 
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
            Sheets(ListBox1.List(lItem)).Delete
        End If
    Next
 
    Me.Hide
End Sub