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.
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.
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 SubASKER
Thanks Norie,
Do I need to specify Worksheet name? as I have numerous sheets in this workbook.
Do I need to specify Worksheet name? as I have numerous sheets in this workbook.
ASKER
Hi Norie,
I copied your code as it is without specifying worksheet name:
ComboBox List was perfect, but I got an error on below
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
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.
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.
ASKER
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?
Workbook Name is Sales.xlsm & Worksheet name is Inventory.
Where do I need to put Workbook & worksheet details?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry Norie,
It doesn't populate list in ComboBox from Inventory sheet, it still bring up the list of active sheet.
It doesn't populate list in ComboBox from Inventory sheet, it still bring up the list of active sheet.
Are you sure?
I tested it with a workbook with a worksheet named Inventory.
I tested it with a workbook with a worksheet named Inventory.
ASKER
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.
If I remove workbook and wsInv.Activate then it works, I don't want to activate, it must populate list, whichever sheet I am.
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?
The code should populate the list no matter which workbook and worksheet is active.
Did you make any changes to the code I posted?
ASKER
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.
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.
ASKER
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.
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.
ASKER
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