For Each Loop and Case statement

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hello to you all,
I have this code that i am using to populate my combobox when the form initializes. I want to add a new combobox so that in that box i will have the words "one", "two", "three". Then when i select any of those words from the ccombbox, i want only certain sheets to show in the list in the combobox1. How do i code it?

Please help me out with this.
Thanks
Kelly
Code: 
Private Sub UserForm_Initialize()
         Dim ws As Worksheet
                 For Each ws In Worksheets
                           Select Case ws.CodeName
                                      Case "Sheet2"
                                      Case Else
                            End Select
                  Next ws
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Okay i have downloaded the file. Now how do i get the code added to my own? I dont seem to understand how it works. Can you explain a bit?
Thanks
 
Upvote 0
So when i select from the combobox like you have done, i want only some sheets to be in the first combobox which loaded from the start of the form. Hope this is clear.
Thanks
 
Upvote 0
In the Select Case of cmb_Click procedure add your own code populating another combobox (same logic as in UserForm_Initialize procedure).
 
Upvote 0
This is the code populating the combobox.
I am just confused where to place it. Look at it for me please;
Code: 
Private Sub UserForm_Initialize()
         Dim ws As Worksheet
                 For Each ws In Worksheets
                           Select Case ws.CodeName
                                      Case "Sheet2"
                                      Case Else
                            Me.ComboBox1.AddItem ws.Name
                            End Select
                  Next ws
End Sub
 
Upvote 0
I don't know why you show code with iterating over the sheets, but your initial request was populating one combobox based on selection in another combobox. As I wrote, you just need to duplicate filling logic (cmb2 is combobox holding sheets' names):
Code: 
Private Sub cmb_Click()
    Select Case cmb.Value
        Case "one"
            cmb2.AddItem "NAME_OF_SHEET1"
            cmb2.AddItem "NAME_OF_SHEET2"
            cmb2.AddItem "NAME_OF_SHEET3"
        Case "two"
            cmb2.AddItem "NAME_OF_ANOTHER_SHEET1"
            cmb2.AddItem "NAME_OF_ANOTHER_SHEET2"
            cmb2.AddItem "NAME_OF_ANOTHER_SHEET3"
        Case "three"
            cmb2.AddItem "NAME_OF_ONE_MORE_ANOTHER_SHEET1"
            cmb2.AddItem "NAME_OF_ONE_MORE_ANOTHER_SHEET2"
            cmb2.AddItem "NAME_OF_ONE_MORE_ANOTHER_SHEET3"
    End Select
End Sub
 
Upvote 0
Okay it is working but the only issue is that when i select one and later select two, the sheets for one still remain in the cmb2. I want it cleared before loading the new sheets.
Thanks
 
Upvote 0
Code: 
Private Sub cmb_Click()
    [COLOR=#ff0000][B]cmb2.Clear
[/B][/COLOR]....
 
Upvote 0
Oh thanks very cool. I was doing cmb2.value =""
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Getting "object variable or with variable not set for combobox
Replies
6
Views
88
bobsan42
bobsan42
R
  • Question
Creating a loop to rename worksheets
Replies
4
Views
422
Micron
M
K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
160
KalilMe
K
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
344
abdelfattah
A
K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
193
kbishop94
K

Forum statistics

Threads
1,214,891
Messages
6,122,101
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top