Userform Combobox associated values

[AnnetteTR]

Hi
I have a Userform with more three comboboxes.
When the user has chosen a value in combobox "Forstevalg" I also want to get the value next to the value from the list.

Excel Workbook
	G	H
15	Overskrifter	Enheder
16	areal	m
17	rumhjde	m
18	luftskifte	/h
19	Antal personer	pers
20	Luftmngde per person	m/h pr pers
21	Sikkerhedsbnk antal	stk
22	Sikkerhedsbnk bredde	mm
23	Sikkerhedsbnk Min	m/h
...

Forstevalg is chosen from the list "Overskrifter" with this code:

With cbxSortering
.RowSource = "Overskrifter"
.ListIndex = 4
End With

I want to get the associated value "Unit" from the list next to it (named "Enheder")

Can you tell med the code for that?

Regards Annette

 

[RoryA]

I would change the combobox's columncount to 2, remove the RowSource, hide the second column and then put both columns of data into it:

With cbxSortering
.List = Range("Overskrifter").Resize(, 2).Value
.ListIndex = 4
End With

You can then simply refer to:

With cbxSortering
  varVal = .List(.Listindex, 1)
End With

 

[AnnetteTR]

Hi rorya<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thank you for your answer.<o></o>
I can see that initial the value of varVal is the corresponding "Enheder" to the initial value of "Overskrifter", but when the user changes the value of "Overskrifter" i want varVal to change correspondantly.<o></o>
<o></o>
ie. I got a makro called "Sub OutputPrAnlag()" this calls the userform "frmOutput"<o></o>
in "frmOutput i got the comboboxes - and the code shown above.<o></o>
<o></o>
On the form i got the sub:<o></o>

<o:p></o:p>
Private Sub CmdOK_Click()<o:p></o:p>
<o:p></o:p>
Me.Hide<o:p></o:p>
<o:p></o:p>
End Sub 

<o></o>
<o></o>
and<o></o>

<o:p></o:p>
Private Sub UserForm_Activate()<o:p></o:p>
<o:p></o:p>
With cbxSortering<o:p></o:p>
.RowSource = "Overskrifter"<o:p></o:p>
.ListIndex = 4<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
With cbx1Vis<o:p></o:p>
.List = Range("Overskrifter").Resize(, 2).Value<o:p></o:p>
.ListIndex = 39<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
With cbx1Vis<o:p></o:p>
varVal = .List(.ListIndex, 1)<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
MsgBox " " & varVal<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
With cbx2Vis<o:p></o:p>
.RowSource = "Overskrifter"<o:p></o:p>
.ListIndex = 40<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>

<o></o>
<o></o>
back in the macro "OutputPrAnlag"<o></o>
I get the value Forstevalg with this code:<o></o>

Forstevalg = frmOutput.cbx1Vis.Text<o:p></o:p>

<o></o>
but how do I get the new value of varVal ?<o></o>
Regards Annette<o></o>

 

[RoryA]

You would use:

with frmOutput.cbx1Vis
   Forstevalg = .Text
   varVal = .List(.ListIndex, 1)
End With

in your calling routine.

 

[AnnetteTR]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This works - thank you.<o></o>
<o></o>
But now I got a new question:<o></o>
now I want to use varVal as text in format - I try to use this syntax, but something is wrong:<o></o>

With ActiveSheet.PivotTables("ItemList").PivotFields("Sum af " & Forstevalg)
    .NumberFormat = "#,##0 " & varVal
End With

<o></o>

It works if I replace

.NumberFormat = "#,##0 " & varVal

with

.NumberFormat = "#,##0 ""m³/h"""

Regards Annette

 

[RoryA]

I suspect you want:

.NumberFormat = "#,##0 """ & varVal & """"