How to Add Default List from Dropdown using Excel VBA then Do something when a Submit button is clic-CodePudding

I'm new to excel macro and I'm not sure how to 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. Something like

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

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

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

Can someone help me with this.. Thanks

CodePudding user response：

Dim dd As DropDown Set dd = ActiveSheet.DropDowns("Drop Down 6") Set r = Sheet2.Range("A1:A10")

Set ddValue = r(dd.Value)

CodePudding user response：

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.