Comman Range with Multiple Combo Box and Not mixed in all combo box

N

nandhavnk

New Member
Joined
Jul 18, 2019
Messages
14
Office Version
  1. 2007
Platform
  1. Windows
Hi,

I need experts support.

In userform there are 7 combo boxes and each combo box values from Common from Cell A1:A.
Cell A1:A Values are
Peter
Micheal
Nick
Jump
Mary
Catherine
.....
.....
.....
etc.,

My question is When I selected 1st combo box value = "Peter" and then all other 6 combo boxes not display the "Peter". and When Selected 2nd Combo box = "Micheal" then other 5 combo boxes not display the "Micheal".

my Code is,

Private Sub UserForm_Initialize()
Dim f
With Worksheets("Name")
f = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With
For i = 1 To 7
With Me("cboname" & i & "N") ' Combo box names are cboname1N, cboname2N, ...
.BoundColumn = 0
.TextColumn = 1
.ColumnCount = 1
.List() = f
End With
Next i
End Sub

I invite experts look my code & suggest right codes.

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
nandhavnk said:
My question is When I selected 1st combo box value = "Peter" and then all other 6 combo boxes not display the "Peter". and When Selected 2nd Combo box = "Micheal" then other 5 combo boxes not display the "Micheal".
Click to expand...
Check an example "Pookiemeister - CW_Schedule 2.xlsm", in this post:
www.mrexcel.com

Populate other combo boxes based on previous combo boxes selection

Currently on a user form, I have 6 combo boxes. The first combo box gets its values from a spreadsheet. The first combo box pulls from the code below. What I am wanting to do is to have the other combo boxes remove any name that was already chosen from the previous combo box. Is this possible...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Thank you for valuable reply.

I used the code and still not working my combo boxes. I have no idea how to correct it.

Could look my sample file and what made to solve this issue. Combo Selection File
Akuini said:
Check an example "Pookiemeister - CW_Schedule 2.xlsm", in this post:
www.mrexcel.com

Populate other combo boxes based on previous combo boxes selection

Currently on a user form, I have 6 combo boxes. The first combo box gets its values from a spreadsheet. The first combo box pulls from the code below. What I am wanting to do is to have the other combo boxes remove any name that was already chosen from the previous combo box. Is this possible...
www.mrexcel.com www.mrexcel.com
Click to expand...
 
Upvote 0
I amended the code in this part (check the blue line):

Rich (BB code): 
Private Sub UserForm_Initialize()

    Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare
    
    With Worksheets("Name")
'        va = .Range("A1:C", .Cells(.Rows.Count, "A").End(xlUp)).Value
       va = .Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
cmdins1.Visible = True
cmdins2.Visible = False
cmdins3.Visible = False
cmdins4.Visible = False
cmdins5.Visible = False
cmdins6.Visible = False

cbname1.Visible = True
cbname2.Visible = False
cbname3.Visible = False
cbname4.Visible = False
cbname5.Visible = False
cbname6.Visible = False
cbname7.Visible = False

txtsal1.Visible = True
txtsal2.Visible = False
txtsal3.Visible = False
txtsal4.Visible = False
txtsal5.Visible = False
txtsal6.Visible = False
txtsal7.Visible = False

lblgen1.Visible = True
lblgen2.Visible = False
lblgen3.Visible = False
lblgen4.Visible = False
lblgen5.Visible = False
lblgen6.Visible = False
lblgen7.Visible = False

Call ini

End Sub


Rich (BB code): 
Private Sub toPopulate(n As Long)
    
    Dim i As Long
    Dim tx As String
    Dim x
    
    d.RemoveAll
    e.RemoveAll
    
'    For i = 1 To 32
    For i = 1 To 7  'number of combobox is 7 not 32
        tx = Me.Controls("cbname" & i).Text
        If tx <> "" And i <> n Then e(tx) = Empty
    Next
    
    If e.Count <> 0 Then
        For Each x In va
            If Not e.Exists(x) Then d(x) = Empty
        Next
    Else
        For Each x In va
            d(x) = Empty
        Next
    End If
    
    Me.Controls("cbname" & n).List = d.keys

End Sub

2022-10-22_nandhavnk.jpg
 
Upvote 0
Solution
Akuini said:
I amended the code in this part (check the blue line):

Rich (BB code): 
Private Sub UserForm_Initialize()

    Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare
   
    With Worksheets("Name")
'        va = .Range("A1:C", .Cells(.Rows.Count, "A").End(xlUp)).Value
       va = .Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
cmdins1.Visible = True
cmdins2.Visible = False
cmdins3.Visible = False
cmdins4.Visible = False
cmdins5.Visible = False
cmdins6.Visible = False

cbname1.Visible = True
cbname2.Visible = False
cbname3.Visible = False
cbname4.Visible = False
cbname5.Visible = False
cbname6.Visible = False
cbname7.Visible = False

txtsal1.Visible = True
txtsal2.Visible = False
txtsal3.Visible = False
txtsal4.Visible = False
txtsal5.Visible = False
txtsal6.Visible = False
txtsal7.Visible = False

lblgen1.Visible = True
lblgen2.Visible = False
lblgen3.Visible = False
lblgen4.Visible = False
lblgen5.Visible = False
lblgen6.Visible = False
lblgen7.Visible = False

Call ini

End Sub


Rich (BB code): 
Private Sub toPopulate(n As Long)
   
    Dim i As Long
    Dim tx As String
    Dim x
   
    d.RemoveAll
    e.RemoveAll
   
'    For i = 1 To 32
    For i = 1 To 7  'number of combobox is 7 not 32
        tx = Me.Controls("cbname" & i).Text
        If tx <> "" And i <> n Then e(tx) = Empty
    Next
   
    If e.Count <> 0 Then
        For Each x In va
            If Not e.Exists(x) Then d(x) = Empty
        Next
    Else
        For Each x In va
            d(x) = Empty
        Next
    End If
   
    Me.Controls("cbname" & n).List = d.keys

End Sub

View attachment 76822
Click to expand...
Thank you. Blue Blink with Clarity @Akuini

It working!​

Here I have to populate Column A value in combo boxes, column B & C is label captions. But Combo box show all column a,b,c.

is this possible Column A value to Combo boxes, Column B value to Label ID caption, Column C value to Label Gender Caption.
 
Upvote 0
nandhavnk said:
Thank you. Blue Blink with Clarity @Akuini

It working!​

Here I have to populate Column A value in combo boxes, column B & C is label captions. But Combo box show all column a,b,c.

is this possible Column A value to Combo boxes, Column B value to Label ID caption, Column C value to Label Gender Caption.
Click to expand...
Hi,

I add one variant as va1 to Label items as below

VBA Code: 
Private Sub UserForm_Initialize()
    
    Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare
    
    With Worksheets("Name")
        va = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
        va1 = .Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
End Sub

Label Caption in cbname1 to 7 Click Changing Code is,
VBA Code: 
Private Sub cbname1_Click()
    With Me.cbname7
        .List() = va1
        Me.lblid1.Caption = .List(.ListIndex, 1)
        Me.lblgen1.Caption = .List(.ListIndex, 2)
    End With
End Sub

Thank you.

Its Solved.
 
Upvote 0
Here's how I would do it:
www.mediafire.com

nandhavnk - Combo Selection

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.
www.mediafire.com
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
@Akuini

Here I have found in auto color change in first textbox, when i enter last text box entry. also it comes, when 4th text box entry.

Here Screenshot

Textbox color changed.png


Can you guide me to solve this.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA & Combo Box (Form Control) Lock
Replies
3
Views
645
Jaafar Tribak
Jaafar Tribak
T
  • Question Question
Reset multiple dependent drop down boxes in a spread sheet.
Replies
2
Views
186
Tarnz
T
J
  • Question Question
Consolidate data from multiple sheets based on values selected in dependent list boxes
Replies
0
Views
540
jconda
J
N
  • Question Question
Auto Selection of Combo Box from Text Box value in Activecell Row
Replies
1
Views
643
nandhavnk
N
P
  • Question Question
Populate Combo Box on Sheet with range of values
Replies
0
Views
398
psycoperl
P

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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