how to get userform texbox text from excel depends on condition

Question

1.00/5 (1 vote)

See more:

Hi All,

I have created one sales related form.
the fields are like Item, price all these data stored in excel sheet.
When select Item in userform the relavent price textbox value need to get price by default how to do this.

Posted 30-Oct-14 19:39pm

rajugknr

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-10-30T23:26:00

Steps to do:

Add new shee and change its name to "Data"
Lets A1:B1 range will be the headers: Item and Price respectively
Add sample data
Go to VBA code editor (ALT+F11)
Add module (Tools->Insert->Module)
Copy and paste below code

VB

Option Explicit
'loads data into 2 columns listbox from desired range
Sub Load2Columns(ByRef oLst As MSForms.ListBox, ByRef wsh As Worksheet, Optional ByVal sFirstColumn As String = "A", Optional ByVal iFirstRow As Integer = 2)
Dim i As Integer

With oLst
    Do While wsh.Range(sFirstColumn & iFirstRow) <> ""
        .AddItem ""
        i = .ListCount - 1
        .Column(0, i) = wsh.Range(sFirstColumn & iFirstRow)
        .Column(1, i) = wsh.Range(sFirstColumn & iFirstRow).Offset(ColumnOffset:=1)
        iFirstRow = iFirstRow + 1
    Loop
End With

End Sub

Add UserForm (Tools->Insert->UserForm
From ToolBox add

one ListBox

one TextBox

two Labels

one Button

Copy and paste below code:

VB

Option Explicit

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_Change()
Me.TextBox1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End Sub

Private Sub UserForm_Initialize()

Load2Columns Me.ListBox1, ThisWorkbook.Worksheets("Data")
With Me.ListBox1
    .ColumnCount = 2
    .ColumnWidths = ";0"
    .ListIndex = 0
End With

End Sub

That's all! Now, when you change selection for ListBox, the price will be loadded to the TextBox

Here [^] you can download an example.