Excel - Active X Combobox, Selecting Sheets

Solved/Closed

MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013 - Jan 9, 2013 at 01:37 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 12, 2013 at 03:02 PM

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.

Excel - Active X Combobox, Selecting Sheets
Excel date format dd.mm.yyyy - Guide
How to screenshot excel sheet - Guide
Mark sheet in excel - Guide
Excel free download - Download - Spreadsheets
Number to words in excel - Guide

2 responses

Answer 1 / 2

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 10, 2013 at 05:08 PM

Try LostFocus event
or use keyupevent to look for enter key and then trigger

MRafik
Jan 11, 2013 at 01:47 PM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 11, 2013 at 05:28 PM

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

Answer 2 / 2

MRafik
Jan 12, 2013 at 10:54 AM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 12, 2013 at 03:02 PM

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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel

Similar discussions

Subscribe To Our Newsletter!