Previous Topic Next Topic

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.            
                

    

        



            

                
                
            

            

    

    
            

            

    

    
    

        Related:         
    




    

        
        

                
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

                                                    

                
                    Activity status glitch
                    

                        
    
    

            

    


                        

                                                                                    exorigen19 -  

                            
                                                                                    lvargas1121 - 
                        

                    

                    

                    

                        2 responses
                    

                
            

                                                        

                
                    Date formatting in dd/mm/yyyy
                    

                        
    
    

            

    


                        

                                                                                    nisha -  

                            
                                                                                    appu - 
                        

                    

                    

                    

                        11 responses