Combo Box Default

[Anniem]

Hi and thanks in anticipation for helping with this problem....

I have a workbook comprising of 16 worksheets each of which has a Combo Box
(from Control Toolbox) which provides links to the other sheets. The code
for the Combo Box(es) is:

Private Sub ComboBox1_Click()
If ComboBox1.ListIndex <> -1 Then
Worksheets(ComboBox1.Value).Select
End If

End Sub

but what do I need to add to make the Combo Box default to ">Select<" (top
line) after the user has clicked on the name of the sheet they want to go to
next.

Apologies if this has already been asked and answered, but I can't find
anything that does the trick.

 

[Dave Peterson]

I put a combobox on a worksheet and used this code behind that worksheet:

Option Explicit
Dim BlkProc As Boolean
'I used _change, not _click
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
With Me.ComboBox1
If .ListIndex <> -1 Then
Worksheets(.Value).Select
BlkProc = True
.ListIndex = 0
BlkProc = False
End If
End With
End Sub
Private Sub Worksheet_Activate()
Dim wks As Worksheet
With Me.ComboBox1
BlkProc = True
.Clear
.AddItem ">Select<"
For Each wks In ThisWorkbook.Worksheets
If wks.Name = Me.Name Then
'skip it
Else
.AddItem wks.Name
End If
Next wks
.ListIndex = 0
BlkProc = False
End With
End Sub

An alternative from Debra Dalgleish's site:
http://contextures.com/xlToolbar01.html

 

[Anniem]

Hi Dave,

Many thanks for responding to this.

I've entered the code as you've given it but am getting a run time error
with the '.Clear' line highlighted. (I've also tried to run the code without
the .AddItem ">Select<" line, as just leaving the box blank is OK)

This is the only problem as everything is doing exactly as I wanted it to.

Any suggestions as to what may causing this problem?

Much appreciated,

Anne

 

[Dave Peterson]

The .clear removed all the items that I added with .additem.

I bet you have a .listfillrange that points to a range on the worksheet.

So you can either remove the .clear or change the way you populate the
combobox. (Removing the .clear is simpler!)

..listindex = 0
selects the first item in the list

You'll want to use:
..ListIndex = -1
instead.

 

[Anniem]

Hi Dave,

Thanks for you patience and persistance....

You're right about the ListFillRange on the worksheet so I delted the .Clear
and .AddItem lines and changed both ListIndex = 0 to ListIndex = -1 but now
am getting 'Permission Denied' msg.
But again, everything else is functioning as it should......

Thanks,

Anne

 

[Anniem]

Thank you Dave, I think I've sorted it by removing the AddItem ">Select<"
and AddItem wks lines..

It is now doing what is required without any error messages...happiness is!!

Hi Dave,

Thanks for you patience and persistance....

You're right about the ListFillRange on the worksheet so I delted the .Clear
and .AddItem lines and changed both ListIndex = 0 to ListIndex = -1 but now
am getting 'Permission Denied' msg.
But again, everything else is functioning as it should......

Thanks,

Anne