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.

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

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