0

How do I put default values on my dropdown?

I tried view code but I'm not sure how to or what codes should I put in there.

I tried

DropDown7.List = Array("Link 1", "Link 2")

in Sub DropDown7_Change() of the module.
I used combo box and button and they are on a sheet.

I need to create a dropdown list that when the user clicks on a specific value, it will do something. I have this on my sheet:

enter image description here

For example, I have "Link 1" and "Link 2" values on the dropdown list and then when I select "Link 1" then "GO TO LINK" button, it will be redirected to the link for Link 1.

Community
  • 1
  • 1
Xev
  • 109
  • 1
  • 1
  • 12
  • What did you try by your own? What control type are the ones you show us? Are them on a `UserForm` or on a sheet? – FaneDuru Jan 26 '22 at 08:57
  • I tried the code "DropDown7.List = Array("Link 1", "Link 2")" and insert in into the Sub DropDown7_Change() of the module. Don't know if I'm doing it right. I used combo box and button and they are on a sheet @FaneDuru. – Xev Jan 26 '22 at 09:04
  • So, if I would say that you are talking about a sheet combo `Form` type, should it be a correct statement? If an `ActiveX` the way you tried should work. A `Form` combo box cannot be loaded in that way. You can use an array but iterate it and add its element using addItem. – FaneDuru Jan 26 '22 at 09:13

1 Answers1

1

Please, try it in the next way:

Sub fillDropDown()
 Dim cB As DropDown, arr, El
 Set cB = ActiveSheet.Shapes("DropDown7").OLEFormat.Object
 arr = Split("https://google.com,https://example.microsoft.com,a wrong link", ",")
 cB.RemoveAllItems
 For Each El In arr
    cB.AddItem El
 Next
End Sub

Copy the above code in a standard module, or in a sheet code module, and run it. If the sheet will be the one keeping the combo, you can use Me instead of ActiveSheet.

It will load in the combo (dropDown) the array elements.

You can build the array in the next way, too:

  arr = Array("https://google.com", "https://example.microsoft.com", "a wrong link")

Then, use the next code to access the hyperlink:

Sub runHyperlink()
    Dim cB As DropDown, arr, El
    Set cB = ActiveSheet.Shapes("DropDown7").OLEFormat.Object
    If cB.Value <> 0 Then
        On Error Resume Next
         ActiveWorkbook.FollowHyperlink Address:=cB.list(cB.Value)
         If Err.Number = -2147221014 Then
            Err.Clear: MsgBox "The used link is not valid..."
         End If
        On Error GoTo 0
    Else
        MsgBox "You should select an option in the combo..."
    End If
End Sub

You can assign the above code to a Form, shape button or place it in a Click event of an ActiveX button. The code or call the sub, simple writing: runHyperlink in the event code.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • @Xev Did you find some time to test the above suggested solution? If tested, doesn't it solve your problem? Are there aspects you do not understand and need clarification? – FaneDuru Jan 26 '22 at 11:02