CombowBox data based on selection from another CombowBox

[Bernieg]

i have 2 ComboBox's on a userform, how do i referance combobox2
based on the answer from combobox1

ComboBox1 referances cells
Rowsource = cells
Combowbox1 selection is INSPECTION

i want combobox2 to give me data based on named range INSPECTION

Named ranges examples
<TABLE style="WIDTH: 425pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=567 border=0 x:str><COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 5536" width=173><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 5984" width=187><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 6624" width=207><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 130pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=173 height=21>cells</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 140pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=187>HOT_FORM</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 155pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=207>INSPECTION</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>HOT_FORM</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 5</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">INSPECT FINAL</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>INSPECTION</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 6</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CMM MITUTOYO 1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>PROCESS</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 7</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CMM MITUTOYO 2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 8</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CMM BROWN & SHARPE 1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 10</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">INSPECT-BINOCULAR</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" height=30></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hot box 12</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Bernie

 

[capparfrank]

So, i think what you're saying is that the data populated in ComboBox1 is a list of named ranges, and you want ComboBox2 to be filled with the data from whatever named range is specified in Combobox1.

This one way to get it done:

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = ActiveWorkbook.Names("Cells").RefersToRange.Address
End Sub
 
 
Private Sub ComboBox1_Change()
    ComboBox2.Value = " -- (empty) -- "
    ComboBox2.RowSource = ActiveWorkbook.Names(ComboBox1.Value).RefersToRange.Address
 
End Sub

It's a good idea to set ComboBox2.value to something like "" or "please select a value" programatically because it will not automatically clear just because the underlying rowsource has changed. E.G. if you had 'Hot Box 6' selected in ComboBox2 and switched ComboBox1 over to INSPECTION -- you'd still have 'Hot Box 6' sitting in ComboBox2. Stuff like that causes troubles.

 

[Bernieg]

Cheers Frank

That worked a treat.
I had managed it but was long winded
'ComboBox2.RowSource = "cells"
'If ComboBox2.Text = "" Then
'ComboBox1.RowSource = ""
'ElseIf ComboBox2.Text = "pt_1" Then
'ComboBox1.RowSource = "pt_1"
'ElseIf ComboBox2.Text = "pt_2" Then
'ComboBox1.RowSource = "pt_2"
'ElseIf ComboBox2.Text = "pt_3" Then
'ComboBox1.RowSource = "pt_3"

Yours is far more eligant

Bernie