how to remove selected sheet from CommandBarComboBox
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
"vicky" wrote in message
...
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
|