Adapt ComboBox (Form Control) to ComboBox (Activex Control)

[desmonda]

Hi,

Following a YouTube video I have setup a basic navigation ComboBox.
However, when viewing in my spreadsheet, the text in the form is to small.

I want to adapt the function of the Form Control to and Activex Control, but the code doesn't convert over.

Can someone please assist with this?
I was hoping to attached a sheet as an example, but it doesn't appear MrExcel allows this.
I've attached a picture of the setup, hoping it will demonstrate what i'm doing and trying to achieve.
Below is a link to the YouTube video I used to create it.
Setup video link

Thanks.

 

[Artik]

1 From the Developer/Insert/ActiveX Controls ribbon, insert Combobox into the sheet. At this point, Excel will enter Design Mode (the Design Mode icon will be “pressed”).
2. from the ribbon, select Developer/Properties. In the window that appears, find the ListFillRange field and type Hyperlinks (hopefully you have already named the range containing the hyperlinks). Also find the Font field and after expanding the field, set the appropriate font size.
3. double-click on the ComboBox control. The code window for the sheet module that contains this control should open and the predefined ComboBox1_Change event procedure will appear.
4. paste the following code into this procedure.

Private Sub ComboBox1_Change()
    Dim lIdx As Long

    lIdx = Me.ComboBox1.ListIndex

    If Len(Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Name) > 0 Then
        Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If
    
End Sub

5. close the Properties window, press the “Design Mode” icon to exit design mode.
6. enjoy life.

You do not need a linked cell for this task.

Artik

 

[desmonda]

1 From the Developer/Insert/ActiveX Controls ribbon, insert Combobox into the sheet. At this point, Excel will enter Design Mode (the Design Mode icon will be “pressed”).
2. from the ribbon, select Developer/Properties. In the window that appears, find the ListFillRange field and type Hyperlinks (hopefully you have already named the range containing the hyperlinks). Also find the Font field and after expanding the field, set the appropriate font size.
3. double-click on the ComboBox control. The code window for the sheet module that contains this control should open and the predefined ComboBox1_Change event procedure will appear.
4. paste the following code into this procedure.

Private Sub ComboBox1_Change()
    Dim lIdx As Long

    lIdx = Me.ComboBox1.ListIndex

    If Len(Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Name) > 0 Then
        Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If
   
End Sub

5. close the Properties window, press the “Design Mode” icon to exit design mode.
6. enjoy life.

You do not need a linked cell for this task.

Artik

Thanks Artik. This is exactly what I was after.
Another query - do you know how to integrate a Collapse groups function into code.
I have a seperate Command button setup using the below code, however when I add the code "ActiveSheet" line into the code you provided, I get a "Runtime Error: ShowLevels method of Outline class failed"

Sub Collapse_All()
    ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End Sub

Private Sub ComboBox1_Change()
    
    Dim lIdx As Long

    lIdx = Me.ComboBox1.ListIndex

    If Len(Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Name) > 0 Then
        Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If
    
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

End Sub

 

[Artik]

The command to collapse all groups triggers the ComboBox1_Change event again. Try the following version of the code.

Private Sub ComboBox1_Change()
    Dim lIdx As Long
    Static bolStop As Boolean

    If bolStop Then Exit Sub

    lIdx = Me.ComboBox1.ListIndex

    If Len(Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Name) > 0 Then
        Range("Hyperlinks").Offset(lIdx).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If

    On Error Resume Next
    bolStop = True
    Me.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    bolStop = False
    On Error GoTo 0
End Sub

Artik