I thought that I had posted on this subject a while ago but I can’t find the post - either way, I never found a solution but desperately need to now!
I have a UserForm that captures data via a number of different controls and when cmdSave is selected, writes the data using eg …
Cells(NEXTROW, 1).value = TextBox1 (NB I have already calculated NEXTROW earlier in the routine)
Cells(NEXTROW, 2).value = TextBox2
Cells(NEXTROW, 3).value = ComboBox1
Etc
The problem I now have is that following a business reorganisation (!), I have been asked to add a number of new fields/corresponding columns, but the new columns need to be inserted in the underlying table rather than added to the end of the table - this, of course!, throws all column references out, requiring me to go through and manually change them (and make sure they’re all working as intended without impacting the existing data!) …
And I already know similar requests will follow for a number of other Workbooks …
What I would like to do is reference columns by the table header which is on row 2 in the Worksheet (row 1 has a number of buttons/macros) eg …
Cells(NEXTROW, Header1).value = TextBox1
… which allows me to move the Header1 column without needing to edit the save statement …
Anyone got any ideas?
Thanks …
I have a UserForm that captures data via a number of different controls and when cmdSave is selected, writes the data using eg …
Cells(NEXTROW, 1).value = TextBox1 (NB I have already calculated NEXTROW earlier in the routine)
Cells(NEXTROW, 2).value = TextBox2
Cells(NEXTROW, 3).value = ComboBox1
Etc
The problem I now have is that following a business reorganisation (!), I have been asked to add a number of new fields/corresponding columns, but the new columns need to be inserted in the underlying table rather than added to the end of the table - this, of course!, throws all column references out, requiring me to go through and manually change them (and make sure they’re all working as intended without impacting the existing data!) …
And I already know similar requests will follow for a number of other Workbooks …
What I would like to do is reference columns by the table header which is on row 2 in the Worksheet (row 1 has a number of buttons/macros) eg …
Cells(NEXTROW, Header1).value = TextBox1
… which allows me to move the Header1 column without needing to edit the save statement …
Anyone got any ideas?
Thanks …
