Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Save combobox value for future need

0

Hi all

I have 3 dependant comboboxes:

cmb_NumberOfTeams is on Userform_Setting.

cmb_ChosenTeam and cmb_Team are on Userform_Badge.

I'm trying to get the value of cmb_NumberOfTeams each time I launch Userfom_Settings so that I get the last value of Userform_Badge.

Below is the code that link the comboboxes.

Private Sub cmb_NumberOfTeams_Change()

    If Me.cmb_NumberOfTeams = 1 Then

        Me.cmb_ChosenTeam.List = Array("#1")

        Me.cmb_Team.List = Array("#1")

    ElseIf Me.cmb_NumberOfTeams.Value = 2 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2")

        Me.cmb_Team.List = Array("#1", "#2")

    ElseIf Me.cmb_NumberOfTeams.Value = 3 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2", "#3")

        Me.cmb_Team.List = Array("#1", "#2", "#3")

    ElseIf Me.cmb_NumberOfTeams.Value = 4 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2", "#3", "#4")

        Me.cmb_Team.List = Array("#1", "#2", "#3", "#4")

   ElseIf Me.cmb_NumberOfTeams.Value = 5 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2", "#3", "#4", "#5")

        Me.cmb_Team.List = Array("#1", "#2", "#3", "#4", "#5")

    ElseIf Me.cmb_NumberOfTeams.Value = 6 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2", "#3", "#4", "#5", "#6")

        Me.cmb_Team.List = Array("#1", "#2", "#3", "#4", "#5", "#6")

    End If

End Sub

Any suggestion will be greatly appreciated

excelmacros-vba
MS Office Version: 365
posted by: Paul001 (rep: 6) on Thu Nov 3, 2022 at 7:45 pm
Answer
Discuss

Answers

0
Selected Answer

Paul

In the attached file I've recreated*  your situation (nearly) with those Comboxes on a single UserForm. There's then a hidden sheet (VBA code name Sheet2) so that you can restore chosen values, even after a save.

I've added this to the end of your macro...

ElseIf Me.cmb_NumberOfTeams.Value = 6 Then

        Me.cmb_ChosenTeam.List = Array("All", "#1", "#2", "#3", "#4", "#5", "#6")

        Me.cmb_Team.List = Array("#1", "#2", "#3", "#4", "#5", "#6")

    End If

'store the changed value
Sheet2.Range("B1").Value = cmb_NumberOfTeams.Value


End Sub

When the form is launched (by clicking the blue button on Sheet1), the bold bit of this new  code restores that value:

Private Sub UserForm_Initialize()

' set Number of teams list
Me.cmb_NumberOfTeams.List = Array(1, 2, 3, 4, 5, 6)
' restore last value
Me.cmb_NumberOfTeams = Sheet2.Range("B1").Value

End Sub

You just need to do similar for the ChosenTeam and Team selections.

Hope this helps.

* When you next ask a question, please remember to use CODE tags (so the code displays neatly in the question) and to attach a representative Excel file with macro, userforms and test data- that saves us recreating your problem.

posted by: John_Ru (rep: 6142) on Fri Nov 4, 2022 at 2:23 pm
Discuss

Discussion

Perfect.

Thank you so much.
Paul001 (rep: 6) Nov 5, '22 at 10:01 am
Glad it worked. Thanks for selecting my answer, Paul. 
John_Ru (rep: 6142) Nov 5, '22 at 1:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login