In the attached workbook, there is a modeless userform which has a combox with a rowsource pointing to the first worksheet "Sheet1!$B$1:$B$2".
I have added a change procedure (event) to the combox, so that if a combox value changes, this will be triggered and the second worksheet will be deleted.
What is good about this event, is that when you rename/delete any worksheet inside the workbook this event will be triggered. However, in this case, I am getting the following errors when it wants to delete the second worksheet:
Run-time error '1004':
Delete method of Worksheet class failed
When I press End button on the error message, the following error is thrown:
---------------------------
Microsoft Visual C++ Runtime Library
---------------------------
Runtime Error!
Program: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
R6025
- pure virtual function call
Any help to resolve this issue is appreciated. The code is also given below:
'ThisWorkbook
Private Sub Workbook_Open()
UserForm1.Show (vbModeless)
End Sub
'UserForm1
Dim EnableEvents As Boolean
Private Sub ComboBox1_Change()
If EnableEvents Then
MsgBox "Running change event", vbInformation, "Test"
givenVal = ComboBox1.Value
EnableEvents = False
MsgBox "Trying to delete the second worksheet", vbInformation, "Test"
ThisWorkbook.Worksheets(2).Delete
EnableEvents = True
End If
End Sub
Private Sub UserForm_Initialize()
EnableEvents = False
UserForm1.ComboBox1.RowSource = "[TestComboBox.xlsm]Sheet1!$B$1:$B$4"
UserForm1.ComboBox1.ListIndex = 3
EnableEvents = True
End Sub
Bookmarks