'How to Add Default List from Dropdown using Excel VBA then Do something when a Submit button is clicked
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:
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.
Solution 1:[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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 |