I have three comboboxes that are on a VBA UserForm. Each gets data from
Dynamic Named Ranges (DNR) that are located on a hidden worksheet:

For Each cOffNam In ws.Range("NameList")
With Me.cboOffNam
.AddItem cOffNam.Value
.List(.ListCount - 1, 1) = cOffNam.Offset(0, 1).Value
End With
Next cOffNam

for all three boxes (cboOffName = "NameList" cboOffBadge =
"BadgeList", etc.) I am currently using "Case" statements but when the
information in the DNR change, I have to manually edit the code. Is
there a way to perform the "Case" function using DNR? (I have a
separate UserForm that edits the DNR, so this "new" code would simplify
things tremendously!!!

I am able to populate each box from these dynamic ranges, but would
like to be able to click one box and populate the other two with the
matching information. (Use cboOffName to populate the other two, OR
click cboOffBadge to populate the other two, etc.)

Thank you in advance!!!

I don't understand how case statements are involved or why you would need to
edit code because a named range changed what it refered to.


Me.cboOffNam.List = ws.Range("NameList").Resize(,2)
Me.cboOffBadge.List = ws.Range("BadgeList").Resize(,2)
Me.cboOffETC.List = ws.Range("EtcList").Resize(,2)

so
Private Sub cboOffName_Click()
Me.cboOffBadge.List = ws.Range("BadgeList").Resize(,2)
Me.cboOffETC.List = ws.Range("EtcList").Resize(,2)
End Sub

Private Sub cboOffBadge_Click()
Me.cboOffNam.List = ws.Range("NameList").Resize(,2)
Me.cboOffETC.List = ws.Range("EtcList").Resize(,2)
End sub

--
Regards,
Tom Ogilvy

"L Scholes" wrote:

I have three comboboxes that are on a VBA UserForm. Each gets data from
Dynamic Named Ranges (DNR) that are located on a hidden worksheet:

For Each cOffNam In ws.Range("NameList")
With Me.cboOffNam
.AddItem cOffNam.Value
.List(.ListCount - 1, 1) = cOffNam.Offset(0, 1).Value
End With
Next cOffNam

for all three boxes (cboOffName = "NameList" cboOffBadge =
"BadgeList", etc.) I am currently using "Case" statements but when the
information in the DNR change, I have to manually edit the code. Is
there a way to perform the "Case" function using DNR? (I have a
separate UserForm that edits the DNR, so this "new" code would simplify
things tremendously!!!

I am able to populate each box from these dynamic ranges, but would
like to be able to click one box and populate the other two with the
matching information. (Use cboOffName to populate the other two, OR
click cboOffBadge to populate the other two, etc.)

Thank you in advance!!!

Tom, thank you for your response. I was able to *******ize your code to
get the end result I want but it is going to be very bulky when I get
it done. (I have pasted a small portion below.) The problem is, where
you see the names in the pasted selection, I have the same information
in a NameList on a hidden sheet. (BadgeList DNR in Column A, NameList
in Column B, etc., with all matching information in the same row)(I
hope I explained that correctly.)

If I could get cboOffNam to select the name to the right of the badge
number selected in cboOff I think the formula could be cut down
dramatically. (I have 3 Combo Boxes in each of 3 frames, and 25
officers to select from, so multiply the pasted code by 9 to apply to
each of the frames.) Also, when officer's badge numbers change or
employee's leave or new employee's get hired I will have to change the
code instead of just adding them into my dynamic range as I am
currently doing with a separate UserForm.
Again, thank you very much!

Private Sub cboOff_Change()
If cboOff.Value = "401" Then
cboOffNam.Value = "MELVIN, J.B."
ElseIf cboOff.Value = "402" Then
cboOffNam.Value = "LONG, G.A."
End If
End Sub
Private Sub cboOffNam_Change()
If cboOffNam.Value = "MELVIN, J.B." Then
cboOff.Value = "401"
ElseIf cboOffNam.Value = "LONG, G.A." Then
cboOff.Value = "402"
End If
End Sub

I am currently doing it on Sheet1 as a function where the DNR's are on
Sheet3 which is hidden. I enter the badge number in Sheet1!$A$2--like I
want to do in cboOff--and get the desired result in Sheet1!$A$3--like I
want to appear in cboOffNam

IF(A2="",NameList,INDEX(NameColumn,MATCH(A2,BadgeC olumn,0)))))