UserForm Help: Combo Box List & TextBox Value

Greetings all experts,

By subject its seems very common question asked, I have looked onto many, nothing matches my requirement.

1. I have created UserForm where I have a ComboBox where I would like to get list of range of cells from worksheet, this range should be from B2 till the last row of the same column.

2. Once I select particular code from above ComboBox, I would like to have its data from Column N of the same sheet in this Text Box.

I would also like to know do I really need to create a text box, or we can get its data un highlighted in the UserForm.
LVL 31
ShumsDistinguished Expert - 2017Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Author Commented:
Sorry Again,

I would also like to add another requirement:

3. As we get data into above TextBox1 & while inputting data in TextBox2, if the number exceeds the value of TextBox1, I would get a prompt message "Entered Qnty Is More Than The Available Qnty"

Hope my requirement is clear.

Thanking you in advance
0
NorieAnalyst Assistant Commented:
Perhaps.
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If CSng(TextBox2.Value) > CSng(TextBox1.Value) Then
        MsgBox "Entered Qnty Is More Than The Available Qnty"
        TextBox2.SelStart = 0
        TextBox2.SelLength = Len(TextBox2.Value)
        Cancel = True
    End If

End Sub

Private Sub UserForm_Initialize()

    ComboBox1.List = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value

End Sub


Private Sub ComboBox1_Change()
Dim idx As Long

    idx = ComboBox1.ListIndex

    If idx = -1 Then Exit Sub    ' nothing selected

    TextBox1.Value = Range("N" & idx + 2).Value

End Sub

Open in new window

0
ShumsDistinguished Expert - 2017Author Commented:
Thanks Norie,

Do I need to specify Worksheet name? as I have numerous sheets in this workbook.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

I copied your code as it is without specifying worksheet name:

ComboBox List was perfect, but I got an error on below

TextBox1.Value = Range("N" & idx + 2).Value

Open in new window

0
NorieAnalyst Assistant Commented:
What error are you getting?

You should specify both workbook and worksheet name otherwise all the ranges will refer to the active sheet in the active workbook.

I didn't include them because I didn't know what they were named.
0
ShumsDistinguished Expert - 2017Author Commented:
Surprisingly I haven't got error now as I was on the active sheet from where the data is extracted.

Workbook Name is Sales.xlsm & Worksheet name is Inventory.

Where do I need to put Workbook & worksheet details?
0
NorieAnalyst Assistant Commented:
I've updated the code to use the workbook and worksheet specified.
Dim wbSales As Workbook
Dim wsInv As Worksheet
Dim Closing As Boolean

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Closing = True Then Exit Sub
    
    If CSng(TextBox2.Value) > CSng(TextBox1.Value) Then
        MsgBox "Entered Qnty Is More Than The Available Qnty"
        TextBox2.SelStart = 0
        TextBox2.SelLength = Len(TextBox2.Value)
        Cancel = True
    End If
    
End Sub

Private Sub UserForm_Initialize()

    Set wbSales = Workbooks("Sales.xlsm") ' ThisWorkbook
    Set wsInv = wbSales.Worksheets("Inventory")

    With wsInv
        ComboBox1.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    End With

End Sub


Private Sub ComboBox1_Change()
Dim idx As Long

    idx = ComboBox1.ListIndex

    If idx = -1 Then Exit Sub    ' nothing selected

    TextBox1.Value = wsInv.Range("N" & idx + 2).Value

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Closing = True
End Sub

Open in new window


By the way, if the userform is in the workbook Sales.xlsm you can use ThisWorkbook instead of Workbooks("Sales.xlsm") - handy if the workbook name could change.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Author Commented:
Sorry Norie,

It doesn't populate list in ComboBox from Inventory sheet, it still bring up the list of active sheet.
0
NorieAnalyst Assistant Commented:
Are you sure?

I tested it with a workbook with a worksheet named Inventory.
0
ShumsDistinguished Expert - 2017Author Commented:
Yes Norie,

If I remove workbook and wsInv.Activate then it works, I don't want to activate, it must populate list, whichever sheet I am.
0
NorieAnalyst Assistant Commented:
wsInv.Activate isn't in the code I posted and it's not needed.

The code should populate the list no matter which workbook and worksheet is active.

Did you make any changes to the code I posted?
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

I am attaching file, please check.
Sales.xlsm
0
NorieAnalyst Assistant Commented:
Why did you move the variable declarations?

They should be at the top of the module, not in the Initialize sub.

If you move them to the top of the module then everything will work.
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

Your code works perfect as per my subject query, I wanted to give more ranking, so I started with one topic each time and I couldn't get through with this code in my original sheet.

I will rank you for such wonderful code.

I will start a new thread where I can give you more rewards.

Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.