Referencing column headings

[Noz2k]

Currently I have a form which posts data to a worksheet like

txtFirstName.Value = Sheets("Sheet1").Range("B" & cboUniqueID.ListIndex +3)

or

txtFirstName.Value = Sheets("Sheet1").Range("A" & cboUniqueID + 3).Offset(, 1)

but what I want to do is use the column headings in row 1 of the worksheet to reference the column, this is because other columns may be added to the worksheet in the future.

all column headings are related to their textbox/combobox like ???Columnheading e.g. the column heading "FirstName" would have a related textbox with the name "txtFirstName".

Any ideas on how to use this to reference the column?

 

[doofusboy]

First thing that occurs to me is to give each column heading a 'name'. Look into Excel's named ranges as it will allow you to refer to those names in your formulas.

 

[Noz2k]

Would it not be possible to derive the column name from the name of the controls

something like

Dim ColumnHeading As String
Set ColumnHeading = Right(UserForm1.Controls.Name, (Len(UserForm1.Controls.Name) - 3)

except that .Controls.Name isn't the way to reference the name of a control (I don't know how to do this)

and then use a Find function to look for the ColumnHeading in Row1 of the worksheet

 

[doofusboy]

Sorry, my bad, missed that you were working with a form.

The following should get you started.

Dim ctl As Control
    For Each ctl In Controls
        MsgBox ctl.Name
    Next ctl