Hi,

I have a data table in sheet1, and I have a userform, containing all
textboxes conected to worksheet for Data Entry.

I want to make a procedure to Retrive Data by selecting Part No. so
that I retrive all the information of the specific part in the
userform.

I want to use Textbox or Combobox to do this in userform.

Thanks and regards.

Shahzad

Hi Shahzad

One way you could do it would be. Say you have data in sheet1 in a
range A1:C10 you can pass that range to a combobox but only have it
show the first column A1:A10 in the combobox from there you can set
the change event for the combobox to put the related data into your
textboxes using the listindex and columns. Add two textboxes and a
combobox to a userform then paste the following code should give you a
clearer idea of what i'm trying to say.

Option Explicit
Dim i As Integer

Private Sub ComboBox1_Change()

i = ComboBox1.ListIndex 'Pass the current listindex to an Integer
'keep in mind that the columns start from 0
'pass the rest of your columns to the textboxes

TextBox1.Value = ComboBox1.Column(1, i) 'This is column B
TextBox2.Value = ComboBox1.Column(2, i) 'This is column C

End Sub

Private Sub UserForm_Initialize()

ComboBox1.RowSource = "A1:C10" 'Set your range

End Sub


hope this helps

On Jun 24, 11:42 pm, Incidental wrote:
HiShahzad

One way you could do it would be. Say you have data in sheet1 in a
range A1:C10 you can pass that range to a combobox but only have it
show the first column A1:A10 in the combobox from there you can set
the change event for the combobox to put the related data into your
textboxes using the listindex and columns. Add two textboxes and a
combobox to a userform then paste the following code should give you a
clearer idea of what i'm trying to say.

Option Explicit
Dim i As Integer

Private Sub ComboBox1_Change()

i = ComboBox1.ListIndex 'Pass the current listindex to an Integer
'keep in mind that the columns start from 0
'pass the rest of your columns to the textboxes

TextBox1.Value = ComboBox1.Column(1, i) 'This is column B
TextBox2.Value = ComboBox1.Column(2, i) 'This is column C

End Sub

Private Sub UserForm_Initialize()

ComboBox1.RowSource = "A1:C10" 'Set your range

End Sub

hope this helps


Hi,

Thank you for prompt reply for me. Exectly what I want you send me the
code. Thank you once again for your support. this is working very
fine.

I would appreciate if you could tell me how to Edit these records
after Retrive the data into the textboxes in userform.

I am preparing a Store Inventory Program I need to Edit the Retrived
records in userform. pls help me.

If you could send me small example file I will be thankfull to you.

Thanks and Regards.

Shahzad

On Jun 26, 8:38 pm, wrote:
On Jun 24, 11:42 pm, Incidental wrote:





HiShahzad

One way you could do it would be. Say you have data in sheet1 in a
range A1:C10 you can pass that range to a combobox but only have it
show the first column A1:A10 in the combobox from there you can set
the change event for the combobox to put the related data into your
textboxes using the listindex and columns. Add two textboxes and a
combobox to a userform then paste the following code should give you a
clearer idea of what i'm trying to say.

Option Explicit
Dim i As Integer

Private Sub ComboBox1_Change()

i = ComboBox1.ListIndex 'Pass the current listindex to an Integer
'keep in mind that the columns start from 0
'pass the rest of your columns to the textboxes

TextBox1.Value = ComboBox1.Column(1, i) 'This is column B
TextBox2.Value = ComboBox1.Column(2, i) 'This is column C

End Sub

Private Sub UserForm_Initialize()

ComboBox1.RowSource = "A1:C10" 'Set your range

End Sub

hope this helps

Hi,

Thank you for prompt reply for me. Exectly what I want you send me the
code. Thank you once again for your support. this is working very
fine.

I would appreciate if you could tell me how to Edit these records
after Retrive the data into the textboxes in userform.

I am preparing a Store Inventory Program I need to Edit the Retrived
records in userform. pls help me.

If you could send me small example file I will be thankfull to you.

Thanks and Regards.

Shahzad

- Hide quoted text -

- Show quoted text -
===================================


Hi,

Thank you for sending me this procedure, I tried it and it is working
well, but still I have one small problem with my userform.

When I tried to type any thing in Combobox1 and it is not in my
worksheet, then my userform is closed and showing this message, and if
I will type some thing correct matching with the sheet cell then it
retrive all the data in userform in all text boxes.


"Could not get the column property, Invaled property Arry Index"


I could not understand why it happen, like this.


and my data sheet is like this it is not full sheet, it is just
sample.....

-------------------------------------------------------------------------------------------
Date Material Name ItemCode Category
-------------------------------------------------------------------------------------------
01-Feb Thermostat for A/C A10001 Air Conditioning
02-Feb Halogen Bulb H0001 Bulb
03-Feb Halogen Bulb small B20003 Bulb
04-Feb Halogen Bulb small C30001 Carpentry
------------------------------------------------------------------------------------------



Option Explicit
Dim i As Integer

Private Sub ComboBox1_Change()

i = ComboBox1.ListIndex

TextBox1.Value = ComboBox1.Column(1, i) 'This is column B
TextBox2.Value = ComboBox1.Column(2, i) 'This is column C
TextBox3.Value = ComboBox1.Column(3, i) 'This is column D
TextBox4.Value = ComboBox1.Column(4, i) 'This is column E
TextBox5.Value = ComboBox1.Column(5, i) 'This is column F
TextBox6.Value = ComboBox1.Column(6, i) 'This is column G
TextBox7.Value = ComboBox1.Column(7, i) 'This is column H
TextBox8.Value = ComboBox1.Column(8, i) 'This is column I
TextBox9.Value = ComboBox1.Column(9, i) 'This is column J
TextBox10.Value = ComboBox1.Column(10, i) 'This is column K
TextBox11.Value = ComboBox1.Column(11, i) 'This is column L
TextBox12.Value = ComboBox1.Column(12, i) 'This is column M

End Sub

Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub UserForm_Initialize()

ComboBox1.SetFocus
ComboBox1.RowSource = "a1:n100" 'Set your range

End Sub


Pls help me in this regard. it is very important for me.

Thanks and Regards

Syed Shahzad Zafar
Madinah - KSA.