I have a userform in my model which displays a list of sheets within the spreadsheet, these can be selected and then either the "delete" button pressed or the "view" button pressed.
The first time this is done it works a charm - perfect!
however, the second time that it opens all the old sheets are still in there even though they've been deleted. Do i need to delete all the previous items in the list, and if so - how?!
I've tried everything i can think of from listbox1.clear to for each item in listbox item.remove etc... Help!
The first time this is done it works a charm - perfect!
however, the second time that it opens all the old sheets are still in there even though they've been deleted. Do i need to delete all the previous items in the list, and if so - how?!
I've tried everything i can think of from listbox1.clear to for each item in listbox item.remove etc... Help!
Code:
Private Sub UserForm_Initialize()
Application.Calculate
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
