Excel VBA: Userfom - textbox value depends on combobox value

Question

I am creating userform which I will be using to insert data and then make some other stuff.

I have a Userform with ComboBox and few TextBoxes. ComboBox is filled with data from range. I want to change values of TextBoxes depending on ComboBox value. Values of TextBoxes should be filled with specific values from worksheet. I thought about creating For Each loop to determine Row of chosen ComboBox value and then change TextBoxes using Row number and setting proper offset.

Worksheet is table with headers and filled with data such as name, city etc.

However my code does not work within Userform.

Any ideas what is wrong or maybe a different approach to a problem?

klient = ComboBox name

Private Sub klient_Change()

Dim MyCell As Range, MyRange As Range
Dim wiersz As Long

Set MyRange = Range("klienci")

For Each MyCell In MyRange
    If klient.Value = MyCell.Value Then
    wiersz = MyCell.Value
    Exit For:
    End If
Next

MsgBox (wiersz)

End Sub

how do you initialize your combobox? something like this Klient.List = Range("klienci")? in that case you could use sth like this to get index of klient from named range: Klient.ListIndex — Dmitry Pavliv
@simoco I'm pretty new to Userforms... My initialization is written in ComboBox RowSource Property. Do you recommend to put this statement to Userform_initialize? — lowak
using RowSource is fine:) About your main question you could use this line to get address of selected item of combobox in range: MsgBox Range("klienci").Cells(1 + Klient.ListIndex, 1).Address (I suppose that your rowSource is exactly klienci range) — Dmitry Pavliv
so in essence - do you want to display the value selected in the combo box in another text box or a MsgBox ... your IF asks for same value ... so you can drop the IF and directly assign klient.Value to the text box ... otherwise your range must be at least 2 columns wide, you traverse the 1st comumn and return the 2nd column (or so ...)? — MikeD
@simoco your code: MsgBox Range("klienci").Cells(1 + Klient.ListIndex, 1).Address was excacly what I was looking for. Now referencing to this cell I can display different values. I modified it for my needs and it's great! Please post an anwser so I can accept it ;) — lowak

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-02-27T07:56:22

As follow up from comments to the questions, this code works:

Private Sub klient_Change()
    MsgBox Range("klienci").Cells(1 + Klient.ListIndex, 1).Address
    'do something else, e.g. get element one to the right:
    MsgBox Range("klienci").Cells(1 + Klient.ListIndex, 1).Offset(,1).Address
End Sub

Excel VBA: Userfom - textbox value depends on combobox value

1 Answers