Excel User Form Macros
 

Pagal

It sounds like you need something like this

Dim i as Long
Dim ColNo as Long

For i = 1 to Me.Controls.Count
ColNo = Application.Match(Me.Controls(i).Tag,Pigs.Range("R ow with
fieldnames"),False)
Pigs.Cells(LastRow of datatable, ColNo).Value = Me.Controls.Text
Next i

The Match function determines which column contains the field name, then
rights the text property of that control to that column.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pagal" wrote in message
om...
Hi,

I am building a user form in Excel. This is brought up on clicking a
button on a form.

In the form, I display data from a data sheet (data table):

Field Name - Name Age Address
Data - ABCD 12 123, ....
Data - EFGH 34 456, ....
Data - IJKL 56 789, ....
Data - MNOP 78 012, ....

using HLOOKUP as
oTextBox.Text = Application.WorksheetFunction.HLookup(oTextBox.Tag ,
Pigs.Range(Pigs.Cells(1, 1), Pigs.Cells(m_iRecordCount + 1,
m_iFieldCount)), m_iCurrentRecord + 1, False)

oTextBox.Tag = Field Name, inorder to avoid writing field-specific
function to populate the UI.

works fine.


Now, I need to insert data into the data sheet (at the end) using the
field name.

What I want to do is - When the users clicks on an 'Insert' button on
the form, I want to go to the last row of the table. Iterate through
the textboxes and using their 'Tag' property to identify the column,
insert its text.

I haven't been able to figure this out yet.

Any pointers on how to do this would be very welcome.

Thanks,
Rajesh