• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Prevent listing a spreadsheet

Experts,
I received this solution and need a slight modification. I do not want to list the workskeet labeled "Home" in my list:

If ComboBox1.ListIndex <> -1 Then
   Application.Goto Worksheets(ComboBox1.Value).Range("A7"), True
 End If
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
1 Solution
 
nutschCommented:
the listing of the home sheet would be in the initialization of the user form, you've listed the code for the validation / on click event.

Can you list the initialize code?

Thomas
0
 
Frank FreeseAuthor Commented:
Here's the code when the worksheet opens:
Private Sub Worksheet_Activate()
Dim ws As Worksheet

    cboSelectSpreadsheet.Clear
    
    For Each ws In ThisWorkbook.Worksheets
        cboSelectSpreadsheet.AddItem ws.Name
    Next ws
    
End Sub

Open in new window


Here's the code when the combo box is selected
Private Sub cboSelectSpreadsheet_Change()
  If cboSelectSpreadsheet.ListIndex <> -1 Then
    
        Application.Goto Worksheets(cboSelectSpreadsheet.Value).Range("A7"), True
        
    End If
End Sub

Open in new window

0
 
nutschCommented:
Updated

Private Sub Worksheet_Activate()
Dim ws As Worksheet

    cboSelectSpreadsheet.Clear
    
    For Each ws In ThisWorkbook.Worksheets
       if ws.name<>"Home" then cboSelectSpreadsheet.AddItem ws.Name
    Next ws
    
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
ok...that got it. thank you
0
 
Frank FreeseAuthor Commented:
thank you
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now