View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JLGWhiz[_2_] JLGWhiz[_2_] is offline
external usenet poster
 
Posts: 1,565
Default 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