UserForm:ComboBox1 select show and change data ListBox and ComboBox2

[sengsara]

Please someone help me with this userform
thanks

[mikerickson]

Put this in the userform's code module
[VBA]Private Sub ComboBox1_Change()
Call FillBox(ComboBox1.ListIndex + 1)
End Sub

Private Sub OptionButton1_Click()
Call FillBox(1)
End Sub

Private Sub OptionButton2_Click()
Call FillBox(2)
End Sub

Private Sub OptionButton3_Click()
Call FillBox(3)
End Sub

Sub FillBox(index As Long)
Dim currentSelection As Long
Dim fillList As Variant
If index < 1 Or 3 < index Then Exit Sub
With Range("g2").Cells(1, index)
fillList = Application.Transpose(Range(.Cells(1, 1), .End(xlDown)).Value)
End With
With Me.ListBox1
currentSelection = IIf(.ListIndex < UBound(fillList), .ListIndex, -1)
.List = fillList
.ListIndex = currentSelection
End With
With Me.ComboBox2
.List = fillList
.ListIndex = currentSelection
End With
Me.Controls("OptionButton" & index).Value = True
ComboBox1.ListIndex = index - 1
End Sub

Private Sub ComboBox2_Change()
ListBox1.ListIndex = ComboBox2.ListIndex
End Sub

Private Sub ListBox1_Change()
ComboBox2.ListIndex = ListBox1.ListIndex
End Sub

Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem OptionButton1.Caption
.AddItem OptionButton2.Caption
.AddItem OptionButton2.Caption
End With
End Sub[/VBA]

[sengsara]

Hi Mike thanks for replay
I put the code into userform but when i run it
is run time error"70" permission denied
what wrong Mike

[mikerickson]

What line gets the error?

[sengsara]

error line...


----> UserForm1.Show

but am using Excel2007

[Kenneth Hobs]

F8 through the code to see that problem is when it tries to add an item to combobox1 in the userform's Initialize event. This is because Mike used the RowSource property in the userform's Comobox1. Not sure why Mike used it and AddItem. Remove the A25:A27 in it to fix it. If you need that added, you will need to use the List property to fill it or AddItem for each cell initially.

Press F4 in the Userform if you do not have the Properties window displayed so that you can edit the property.

[mikerickson]

...Mike used the RowSource property in the userform's Comobox1. .....

Thats odd, RowSource isn't supported on my Mac, so I didn't use it. When I open the file, I don't see RowSource in my code. Possibly the OP file has a combobox with RowSource set from the propreties window that doesn't show on my Excel2004. In which case, it should be set to null.

Sengara, you have this functionality in the sheet you put as an example. Why put it in a userform? The sheet you have will be easier to maintain than a UF.