How do I fill a cell in a user form from a selection on same form?
 

I'm new to creating user forms and I am working with a combo box sample form
I download from www.contextures.com (a great site I must add). I would like
to have data displayed on the same form once a user makes a selection from
the combo box. I.e. If the user selects account number '10-1000,' I would
like the description of this account, 'Petty-Cash' to display in the
adjoining field. This would just be a double check to make sure the desired
account was truly selected before adding data to my table. I'm sure I need a
look-up function, but I'm not sure how to tie the two together on the form.

Any help will be appreciated.

Thanks in advance.
--
T Tipsy

How do I fill a cell in a user form from a selection on same form?
 

I'd create a new worksheet (and hide it???).

Put all those numeric codes in column A and the English description in column B.

Then in the combobox_change event, I'd use something like this to update a label
with the associated description:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim res As Variant
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

Set myRng = ThisWorkbook.Worksheets("myTable").Range("a:b")

res = Application.VLookup(Me.ComboBox1.Value, myRng, 2, False)

If IsError(res) Then
Me.Label1.Caption = "Missing!"
Else
Me.Label1.Caption = res
End If

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long

Me.ComboBox1.Style = fmStyleDropDownList
Me.Label1.Caption = ""

'just some test data
With Me.ComboBox1
For iCtr = 0 To 6
Me.ComboBox1.AddItem "10-10" & iCtr
Next iCtr
End With

End Sub


Terry Tipsy wrote:

I'm new to creating user forms and I am working with a combo box sample form
I download from www.contextures.com (a great site I must add). I would like
to have data displayed on the same form once a user makes a selection from
the combo box. I.e. If the user selects account number '10-1000,' I would
like the description of this account, 'Petty-Cash' to display in the
adjoining field. This would just be a double check to make sure the desired
account was truly selected before adding data to my table. I'm sure I need a
look-up function, but I'm not sure how to tie the two together on the form.

Any help will be appreciated.

Thanks in advance.
--
T Tipsy

--

Dave Peterson

How do I fill a cell in a user form from a selection on same f
 

Dave - Thanks for the information and code. If I understand it correctly, I
could also use the same 'named' range I used to create the combo box, rather
than creating a new sheet?
--
T Tipsy


"Dave Peterson" wrote:

I'd create a new worksheet (and hide it???).

Put all those numeric codes in column A and the English description in column B.

Then in the combobox_change event, I'd use something like this to update a label
with the associated description:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim res As Variant
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

Set myRng = ThisWorkbook.Worksheets("myTable").Range("a:b")

res = Application.VLookup(Me.ComboBox1.Value, myRng, 2, False)

If IsError(res) Then
Me.Label1.Caption = "Missing!"
Else
Me.Label1.Caption = res
End If

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long

Me.ComboBox1.Style = fmStyleDropDownList
Me.Label1.Caption = ""

'just some test data
With Me.ComboBox1
For iCtr = 0 To 6
Me.ComboBox1.AddItem "10-10" & iCtr
Next iCtr
End With

End Sub


Terry Tipsy wrote:

I'm new to creating user forms and I am working with a combo box sample form
I download from www.contextures.com (a great site I must add). I would like
to have data displayed on the same form once a user makes a selection from
the combo box. I.e. If the user selects account number '10-1000,' I would
like the description of this account, 'Petty-Cash' to display in the
adjoining field. This would just be a double check to make sure the desired
account was truly selected before adding data to my table. I'm sure I need a
look-up function, but I'm not sure how to tie the two together on the form.

Any help will be appreciated.

Thanks in advance.
--
T Tipsy

--

Dave Peterson

How do I fill a cell in a user form from a selection on same f
 

Sure.

I just figured that there might be duplicates in the original data and you
wouldn't want those duplicates to show up in the dropdown.

Terry Tipsy wrote:

Dave - Thanks for the information and code. If I understand it correctly, I
could also use the same 'named' range I used to create the combo box, rather
than creating a new sheet?
--
T Tipsy

"Dave Peterson" wrote:

I'd create a new worksheet (and hide it???).

Put all those numeric codes in column A and the English description in column B.

Then in the combobox_change event, I'd use something like this to update a label
with the associated description:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim res As Variant
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

Set myRng = ThisWorkbook.Worksheets("myTable").Range("a:b")

res = Application.VLookup(Me.ComboBox1.Value, myRng, 2, False)

If IsError(res) Then
Me.Label1.Caption = "Missing!"
Else
Me.Label1.Caption = res
End If

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long

Me.ComboBox1.Style = fmStyleDropDownList
Me.Label1.Caption = ""

'just some test data
With Me.ComboBox1
For iCtr = 0 To 6
Me.ComboBox1.AddItem "10-10" & iCtr
Next iCtr
End With

End Sub


Terry Tipsy wrote:

I'm new to creating user forms and I am working with a combo box sample form
I download from www.contextures.com (a great site I must add). I would like
to have data displayed on the same form once a user makes a selection from
the combo box. I.e. If the user selects account number '10-1000,' I would
like the description of this account, 'Petty-Cash' to display in the
adjoining field. This would just be a double check to make sure the desired
account was truly selected before adding data to my table. I'm sure I need a
look-up function, but I'm not sure how to tie the two together on the form.

Any help will be appreciated.

Thanks in advance.
--
T Tipsy

--

Dave Peterson


--

Dave Peterson

How do I fill a cell in a user form from a selection on same f
 

Dave - Thanks again. It may be a few days before I can get back to this
project. I may post back again if I run into difficulties or have more
questions.
--
T Tipsy


"Dave Peterson" wrote:

Sure.

I just figured that there might be duplicates in the original data and you
wouldn't want those duplicates to show up in the dropdown.

Terry Tipsy wrote:

Dave - Thanks for the information and code. If I understand it correctly, I
could also use the same 'named' range I used to create the combo box, rather
than creating a new sheet?
--
T Tipsy

"Dave Peterson" wrote:

I'd create a new worksheet (and hide it???).

Put all those numeric codes in column A and the English description in column B.

Then in the combobox_change event, I'd use something like this to update a label
with the associated description:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim res As Variant
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

Set myRng = ThisWorkbook.Worksheets("myTable").Range("a:b")

res = Application.VLookup(Me.ComboBox1.Value, myRng, 2, False)

If IsError(res) Then
Me.Label1.Caption = "Missing!"
Else
Me.Label1.Caption = res
End If

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long

Me.ComboBox1.Style = fmStyleDropDownList
Me.Label1.Caption = ""

'just some test data
With Me.ComboBox1
For iCtr = 0 To 6
Me.ComboBox1.AddItem "10-10" & iCtr
Next iCtr
End With

End Sub


Terry Tipsy wrote:

I'm new to creating user forms and I am working with a combo box sample form
I download from www.contextures.com (a great site I must add). I would like
to have data displayed on the same form once a user makes a selection from
the combo box. I.e. If the user selects account number '10-1000,' I would
like the description of this account, 'Petty-Cash' to display in the
adjoining field. This would just be a double check to make sure the desired
account was truly selected before adding data to my table. I'm sure I need a
look-up function, but I'm not sure how to tie the two together on the form.

Any help will be appreciated.

Thanks in advance.
--
T Tipsy

--

Dave Peterson


--

Dave Peterson