Set a Navigation menu in Excel

[hergy]

Hello there,




I request your help to modify this code a little bit.

Because, i utilise it to create a sort of navigation menu between excel worksheets: we select a value in combobox1 first, and other values display in listbox1 depending of the value of combobox, then by clicking on any value in listbox1, we get redirected to the matching worksheet. In each sheet, i set the same navigation menu, and every time we move to another sheet, combobox is clear. Just fine.


However, the concern is that when we open the file, combobox is empty instead of display values for navigation, but if i go to another sheet and go back, combobox values display; may be it's because of Worksheet_Activate() instruction (please note that i'm not a developper).


I set a VBA that redirect user to a specific worksheet when opening the file before, now i'd like find the way to get values displayed in combobox too when opening the file.


Help please.


Here is my code:

Option Explicit


Private Sub Worksheet_Activate()
  Dim c As Range
  ComboBox1.Clear
For Each c In Range(Range("B70"), Range("B" & Rows.Count).End(xlUp))
        If c.Value <> vbNullString Then ComboBox1.AddItem c.Value
    Next c
End Sub


Private Sub ComboBox1_Change()
  With ListBox1
    .Clear
    'Setup 2 columns in the listbox
    .ColumnCount = 2
    'The .Value property should return the data from the 2nd column
    .BoundColumn = 2
    'Hide the second column
    .ColumnWidths = .Width - 5 & ";0"
    Select Case ComboBox1.Value
      Case "Activités, SIG et Résultat"
        'Add an item (this string is visible)
        .AddItem "Objectifs et Réalisation"
        'Store the reference in the 2nd column for the last added item
        .List(.ListCount - 1, 1) = "BUDGETS!A1"
        'Next one, same as above
        .AddItem "Evolution et Répartition"
        .List(.ListCount - 1, 1) = "DECOMPOSITION!A1"
        .AddItem "Suivi des Ecarts"
        .List(.ListCount - 1, 1) = "ECARTS!A1"
        
      'Case London
      '  same as above
    End Select
  End With
End Sub


Private Sub ListBox1_Click()
  Dim Ref As String
  'Where should we go?
  Ref = ListBox1.Value
  'Possible?
  If RangeExists(Ref) Then
    'Select the sheet
    Application.Range(Ref).Parent.Select
    'Then the cell(s)
    Application.Range(Ref).Select
  End If
End Sub


Private Function RangeExists(ByVal Ref As String) As Boolean
  On Error Resume Next
  RangeExists = Not Application.Range(Ref) Is Nothing
End Function

 

[hergy]

Up