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:
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:
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