Excel - Active X Combobox, Selecting Sheets

[Solved/Closed]
Report
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,

Am a newbie in trying to automate Excel.
I have a workbook with 50 worksheets and a menu sheet.
On the menu sheet I created a Actixe X combobox filling it
with the fifty sheets.
I wish to be able to select from the combo box to go to a
particular sheet. I am using the following code which works fine
if I select and click but when I enter an alphabet it goes direct
to the first sheet which starts with what is entered in the combo
box instead of giving me options to select.


  

Private Sub ComboBox1_Click()If Me.ComboBox1.Value <> "" Then Sheets(Me.ComboBox1.Value).Activate
ComboBox1.Value = ""
End Sub




Any ideas of what the code should be?


Please note I have posted this on other blogs.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try LostFocus event
or use keyupevent to look for enter key and then trigger
Thanks for the suggestion
Tried putting the code in both the
Lost focus and the keyup events
But can not resolve the problem

Any other ideas
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Thye both work for me

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)       
     If ComboBox1.Value = vbNullString Then Exit Sub  
     If (KeyCode <> 13) Then Exit Sub  
       Sheets(ComboBox1.Value).Select        
End Sub  

Private Sub ComboBox1_LostFocus()  
    If ComboBox1.Value = vbNullString Then Exit Sub  
    Sheets(ComboBox1.Value).Select  
End Sub
Thank you very much
It does work
You are a star

Can you please help with what additional code I will
need to put in and were if a sheet does not exist
ie a Message Box displaying "NO SUCH SHEET EXISTS"

Once again THANK YOU
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can use this function to test if a sheet is present or not

Public Function isSheetPresent(sheetName As String) As Boolean
Dim tempValue     As String

On Error Resume Next
   tempValue = "This is only a dummy value for test isSheetPresent."
   tempValue = Sheets(sheetName).Cells(1, 1)
On Error GoTo 0
   Err.Clear
   isSheetPresent = (tempValue <> "This is only a dummy value for test isSheetPresent.")
End Function