Code For COMBOBOX

[LearnMeExcel]

hi
i add combobox in my sheets
i want to add the sheets name to Combobox
i wrote this code

Private Sub ComboBox1_GotFocus()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Main" Then
        ComboBox1.AddItem ws.Name
    End If
Next ws
End Sub

but the problem every time i click the combobox the sheets name repeat,for example
first time the result
Sheet 1
Sheet 2
Sheet 3
the second time
Sheet 1
Sheet 2
Sheet 3
Sheet 1
Sheet 2
Sheet 3
how i can solve it

 

[VoG]

Try

Private Sub ComboBox1_GotFocus()
Dim ws As Worksheet
Dim i As Long
For i = 1 To ComboBox1.ListCount
    ComboBox1.RemoveItem 0
Next i
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Main" Then
        ComboBox1.AddItem ws.Name
    End If
Next ws
End Sub

 

[LearnMeExcel]

Thanx Peter

Dim i As Long
For i = 1 To ComboBox1.ListCount
ComboBox1.RemoveItem 0
Next i

why you put 0


i have another Point
i want to add HyperLink for every sheets in my workbook to back to main
i test this code

Sub AddHyperLink()
Dim ws As Worksheet
For Each ws In Sheets
    If ws.Name <> "Main" Then
        ws.Hyperlinks.Add Anchor:=Range("a1"), Address:="", _
        SubAddress:="Main!B2", TextToDisplay:="Back To Main"
    End If
Next ws
End Sub

it just add HyperLink to Main
should i activate every sheet in sheets before add Hyperlink

 

[VoG]

0 means row 1 in the combobox entries.

Try a new thread for your other question. I am about to go out for the day.

 

[LearnMeExcel]

thanx peter
i added new thread