how to remove selected sheet from CommandBarComboBox

[vicky]

i have a code which adds sheet names to combo box

Dim ctrl As CommandBarControl
Dim wks

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Sheets
'If wks.Visible = xlSheetVisible Then
ctrl.AddItem wks.Name
'End If
Next wks
End Sub

i need a code which deletes the selected sheet from combo box... the
following code is throwing me an error .........

Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Delete Sheets"
.OnAction = "'" & ThisWorkbook.Name & "'!DeleteSheets"
End With
End With

Sub DeleteSheets()
Dim myWksName As String
Dim wks

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else

.Delete .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
On Error GoTo 0

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub

 

[Peter T]

With the snippets of code you posted it's difficult to know what you have
and what you want to do. Your Button is assigned to 'DeleteSheets' in which
you have

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then

So is that a button or a combo

Guessing at what you have, and I could be completely wrong, you've got a
button and a combo, the combo holds a list of sheet names, user presses the
button to delete the sheet selected in the combo. Maybe you want something
like


Sub DeleteSheets()
Dim myWksName As String

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__") ' the combo

' If ctrl.ListIndex = 0 Then
' MsgBox "Please select an existing sheet"
' Exit Sub
' end if
with ctrl
' first get confirmation from user
' error handling etc
Application.DisplayAlerts = False
myWksName = .Text
Worksheets(myWksName).Delete
Application.DisplayAlerts = True

.RemoveItem (.ListIndex)

End With


Regards,
Peter T

 

[Dave Peterson]

Untested...

Sub DeleteSheets()
Dim myWksName As String
Dim wks

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else

application.enableevents = false 'no warning message
on error resume next
activeworkbook.worksheets(.List(.ListIndex)).delete
if err.number <> 0 then
err.clear
msgbox "delete failed"
end if
application.enableevents = true
End With

'do clean up the list yourself--let this sub do the work
Call RefreshTheSheets

End Sub

 

[JLGWhiz]

I think you want to use the RemoveItem method. Here is a snippet from the
VBA help file that might help.

Private Sub CommandButton2_Click()
'Ensure ListBox contains list items
If ListBox1.ListCount >= 1 Then
'If no selection, choose last list item.
If ListBox1.ListIndex = -1 Then
ListBox1.ListIndex = _
ListBox1.ListCount - 1
End If
ListBox1.RemoveItem (ListBox1.ListIndex)
End If
End Sub