Created
August 19, 2020 19:14
-
-
Save Zadigo/7b7267334f13d84e3e74e95530427c8f to your computer and use it in GitHub Desktop.
Populates a dropdown list automatically based on the option button that a user has clicked in the form
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Dim wk As Worksheet | |
Private Sub UserForm_Initialize() | |
' 1. Create a list of items to populate the list with | |
' when the user opens the form | |
Me.ComboBox1.AddItem "Kendall Jenner", 0 | |
Me.ComboBox1.AddItem "Kylie Jenner", 1 | |
Me.ComboBox1.AddItem "Kim Kardashian", 2 | |
Me.OptionButton1.Value = True | |
Call set_to_first_value | |
End Sub | |
Private Sub CommandButton1_Click() | |
' 2. Define what to do on validation | |
Set wk = Worksheets(ActiveSheet.Index) | |
' 3. We just want to show the selected value | |
wk.Range("B3").Value2 = UserForm1.ComboBox1.Value | |
End Sub | |
' 4. Define what to show when the user clicks | |
' on the first option button | |
Private Sub OptionButton1_Click() | |
Call clear_combo_box | |
Me.ComboBox1.AddItem "Kendall Jenner", 0 | |
Me.ComboBox1.AddItem "Kylie Jenner", 1 | |
Me.ComboBox1.AddItem "Kim Kardashian", 2 | |
Call set_to_first_value | |
End Sub | |
' 5. Same, when the use clicks on the | |
' on the scond option button | |
Private Sub OptionButton2_Click() | |
Call clear_combo_box | |
Me.ComboBox1.AddItem "Ariana Grande", 0 | |
Me.ComboBox1.AddItem "Taylor Swift", 1 | |
Me.ComboBox1.AddItem "Beyonce Knowles", 2 | |
Call set_to_first_valuea | |
End Sub | |
Private Sub clear_combo_box() | |
' This clears the list selection from | |
' previous one | |
Me.ComboBox1.Clear | |
End Sub | |
Private Sub set_to_first_value() | |
' This sets the list selection to the | |
' first option of the list | |
Me.ComboBox1.ListIndex = 0 | |
End Sub | |
Sub Open_Form() | |
UserForm1.Show | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment