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

desmonda

New Member
Joined
Aug 3, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
Capture.JPG
 
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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"


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

VBA Code:
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
 
Upvote 0
The command to collapse all groups triggers the ComboBox1_Change event again. Try the following version of the code.
VBA 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
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top