I created a Combo box with a list of names , that when clicked, goes to
that persons worksheet. This works fine except, if you click on a name,
go to that worksheet then come back to the Combo Box, the same name is
still highlighted and when clicked, nothing happens. You have to click
on a different name for it to work.
Is there a way around this so that you can click on whatever name
appears in the box?

I have the combo box in conjunction with Option Buttons.

What I have (some of) at present is...

If cboHouse.Text = "Joe Blow" And optHouse.Value = True Then
Application.Goto Reference:=Worksheets("Sheet1").Range("A213"), _
Scroll:=True
End
End If
If cboHouse.Text = "John Smith" And optHouse.Value = True Then
Application.Goto Reference:=Worksheets("Sheet1").Range("A244"), _
Scroll:=True
End
End If

End Sub

Any help appreciated.
Cheers
Graham.


---
Message posted from http://www.ExcelForum.com/

Your code needs to change the combo box value to "" so
that when you return and select the same name from the
list it will initiate the Change event. This apparently
is a requirement for the Click event as well (oddly). My
read of your code is that "cboHouse" is an object variable
that refers to the combo box. Therefore, I suggest:
cboHouse.Value = ""

You might also consider exploiting the combo box's
ListIndex property to select the worksheet range instead
of using If Then code. Perhaps something like:

Cells(cboHouse.ListIndex + 50, 1).Select

Regards,
Greg


-----Original Message-----
I created a Combo box with a list of names , that when
clicked, goes to
that persons worksheet. This works fine except, if you
click on a name,
go to that worksheet then come back to the Combo Box, the
same name is
still highlighted and when clicked, nothing happens. You
have to click
on a different name for it to work.
Is there a way around this so that you can click on
whatever name
appears in the box?

I have the combo box in conjunction with Option Buttons.

What I have (some of) at present is...

If cboHouse.Text = "Joe Blow" And optHouse.Value = True
Then
Application.Goto Reference:=Worksheets("Sheet1").Range
("A213"), _
Scroll:=True
End
End If
If cboHouse.Text = "John Smith" And optHouse.Value = True
Then
Application.Goto Reference:=Worksheets("Sheet1").Range
("A244"), _
Scroll:=True
End
End If

End Sub

Any help appreciated.
Cheers
Graham.


---
Message posted from http://www.ExcelForum.com/

.