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.

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" wrote:

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 wrote:

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

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

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!!

"Anniem" wrote:

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

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson