Retrive Records in UserForm and Edit - from shahzad

[shahzad4u_ksa]

Hi,

I have some data in Worksheet, and I have a userform, so I can retrive
my data in userform by using the following code.

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


I want to Edit my records in Userform after retrive my data.

Thanks and Regards


Syed Shahzad Zafar
Madinah - KSA.

 

[Dave Peterson]

Maybe you could use this for an idea:

Option Explicit
Private Sub ComboBox1_Change()

dim iCtr as long

With Me.ComboBox1
If .ListIndex < 0 Then
'do nothing
Else
For iCtr = 1 To .ColumnCount
me.controls("Textbox" & ictr).value _
= .List(.ListIndex, iCtr - 1)
Next iCtr
End if
End With
End Sub

(Untested, uncompiled.)

 

[shahzad4u_ksa]

Maybe you could use this for an idea:

Option Explicit
Private Sub ComboBox1_Change()

dim iCtr as long

With Me.ComboBox1
If .ListIndex < 0 Then
'do nothing
Else
For iCtr = 1 To .ColumnCount
me.controls("Textbox" & ictr).value _
= .List(.ListIndex, iCtr - 1)
Next iCtr
End if
End With
End Sub

(Untested, uncompiled.)



























--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi, Dave,

I changed my code for the combobox as per your instruction, but it not
doing any thing. not retriving my data from sheet and I am still
unable to change any record from Userform.

Pls send me other solution.

Regards.

syed shahzad
Madinah - ksa

 

[Dave Peterson]

I don't have another suggestion.

Hi, Dave,

I changed my code for the combobox as per your instruction, but it not
doing any thing. not retriving my data from sheet and I am still
unable to change any record from Userform.

Pls send me other solution.

Regards.

syed shahzad
Madinah - ksa