Table Data from userform??

[GordonM]

Hi,

I have a table in a worksheet and a userform that colects the data from the table using vlookup, if i change the data on the userform how can i send it back to the table just using the userform????????

Here is what i have so far

Private Sub ComboBox1_dropbutt*******()
On Error Resume Next
With Me
.TextBox1 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 6, 0)
.TextBox2 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 7, 0)
.TextBox3 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 3, 0)
.TextBox4 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 9, 0)
.TextBox5 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 4, 0)
.ComboBox2 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 5, 0)
.ComboBox3 = Application.WorksheetFunction.VLookup((Me.ComboBox1), Sheet4.Range("table2"), 2, 0)
End With
TextBox3 = Format(TextBox3.Text, "dd mmm yyyy")
TextBox4 = Format(TextBox4.Text, "dd mmm yyyy")
TextBox5 = Format(TextBox5.Text, "dd mmm yyyy")

End Sub

Private Sub CommandButton2_Click()
Unload vehicle_list

End Sub


Private Sub TextBox3_afterupdate()
TextBox3 = Format(TextBox3.Text, "dd mmm yyyy")
End Sub

Private Sub TextBox4_afterupdate()
TextBox4 = Format(TextBox4.Value, "dd mmm yyyy")
End Sub

Private Sub TextBox5_afterupdate()
TextBox5 = Format(TextBox5.Value, "dd mmmyyyy")
End Sub

 

[Roxxien]

From what I see, it will really help to create an invisible label in your userform.

By doing that, you are going to be able to save row where you picked your value. The main issue in that you "range" for the match function need to be only 1 column.

invisiblelabel= application.match(ComboBox1, Range("table2"),0)

Then in this sub, you will be able to use the index formula instead of vlookup.

.TextBox1=Application.Index(Range("table2"),invisiblelabel,6)

In your second sub to write your data, you will be able to reuse that label to offset from a certain cell.

 

[GordonM]

I have tried that but i must be doing something wrong, (I told you I am not too good with VBA )

Here is what i did:

Private Sub ComboBox1_DropButt*******()

Label11 = Application.Match(ComboBox1, Range("table2"), 1)
.TextBox1 = Application.Index(Range("table2"), Label11, 6)
.TextBox2 = Application.Index(Range("table2"), Label11, 7)
end sub

 

[Roxxien]

As I writed, match won't work if the range is more than 1 column/row. Other than that, if you want the exact value, you need to put 0 instead of 1.

If you are not working with "with", you don't need the dot before TextBox1 and TextBox2

 

[GordonM]

its still not working for me.
label11 is the invisable label.

here is what i have so far

Private Sub ComboBox1_DropButt*******()

With Me
Label11 = Application.Match(ComboBox1, Range("table2"), 0)
.TextBox1 = Application.Index(Range("table2"), Label11, 6)
End With
TextBox3 = Format(TextBox3.Text, "dd mmm yyyy")
TextBox4 = Format(TextBox4.Text, "dd mmm yyyy")
TextBox5 = Format(TextBox5.Text, "dd mmm yyyy")

End Sub

any ideas??

 

[Roxxien]

Your named range table2 contains how many columns?

 

[GordonM]

it has 11

 

[Roxxien]

Just like I said 2 times, you can't have more than 1 columns for the match function. It will give you #N/A otherwise.

 

[GordonM]

sorry my table has 11 columns(Table2) the named range for the combobox has 1 colum

 

[Roxxien]

Hum ok.

In the match function, you need 1 column which seem to be correct. However, index need all your data in the range (same as lookup). So, you should have 2 different range for those function.