Insert data from userform into table depending on headers of that table

[bryner]

Hi all, was working on this problem AGES ago, and could never find a simple solution. i'm SURE there has to be an easy way to do this, it would only make sense given the scenario.

Scenario:

Userform with 5 x textboxes.

a NAMED 5 column table "Table1" that has headers, obviously real data starts from row 2 eg:


[TABLE="width: 500"]
<tbody>[TR]
[TD]header1
[/TD]
[TD]header2
[/TD]
[TD]header3
[/TD]
[TD]header4
[/TD]
[TD]header5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]a
[/TD]
[TD]1q
[/TD]
[TD]11
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]b
[/TD]
[TD]2w
[/TD]
[TD]22
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]c
[/TD]
[TD]3e
[/TD]
[TD]33
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to write code that can read what the header is and input the data under that header regardless if i add other columns in the future or move them etc. I want this to be as futureproof as possible.

Also, since i started making this userform about 18 months ago (the same time i started learning VBA from a textbook) and am only now being asked to finish it, I've forgotten how to do a whole lot of stuff... AND I made it more complicated by not having the table start on cell A1, it actually starts at about B5

something along the logic of:


private sub Submit_Click()

Table1([findRowWithHeader = "header1"] & [nextEmptyRow]).Value = TextBox1.Value
Table1([findRowWithHeader = "header2"] & [nextEmptyRow]).Value = TextBox2.Value
Table1([findRowWithHeader = "header3"] & [nextEmptyRow]).Value = TextBox3.Value
Table1([findRowWithHeader = "header4"] & [nextEmptyRow]).Value = TextBox4.Value
Table1([findRowWithHeader = "header5"] & [nextEmptyRow]).Value = TextBox5.Value

end sub



I was hoping for a way to call the table specifically, and therefore manipulate it without having to consider where the table sits on the sheet.

Thanks heaps everyone.

 

[Norie]

I'm a little confused, you say you want to find one header but then post psuedo-code that is putting values in all 5 headers of your example table.

Also, where do the header names come from if there are only 5 textboxes on the userform?

 

[bryner]

i usually want to populate the whole table, though there will be instances where textboxes are empty... anyway, point is, in future, i may need to add other columns to this table or even move the columns around. I just wanted to find a way that would avoid me (or more importantly, anyone else playing with this) having to redo the code because of those sorts of changes.

Also, the header names are hardcoded into the procedure. Actual Headers will be things like "Date", "Name", "Result", "Value" etc etc

 

[Norie]

Sorry, I still don't get it.

Will specific textboxes be tied to a specific header?

Is there any header/textbox that will always be filled in?

It's easy enough to find the columns using Match, if you know which row they are in.

 

[bryner]

hmmm... well, first of all, thanks for trying to help. I really do appreciate it.

Yes, specific textbox, checkbox objects will be tied to a specific header... (though eventually I'll want to make a list (that corresponds with headers) with checkboxes on each item on the list... but let's leave that for another day.)

There will be certain textboxes that will always be filled in.

for absolute clarification, let me use a different example... imagine wanting to track the presence of items in a basket with just Date, Name, and Boolean values for whether that item is present or not.

simplification of userform :
-------------------
Date: [10/11/12] (textbox)
Name: [Bob Jones] (textbox)
Banana: [TRUE] (checkbox)
Apple: [FALSE] (checkbox)
Pear: [TRUE] (checkbox)
-------------------

I want to add the userform data to the next line of this existing table:
----------------------------------------------------------------------
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]NAME[/TD]
[TD]BANANA[/TD]
[TD]APPLE[/TD]
[TD]PEAR[/TD]
[/TR]
[TR]
[TD]10/10/10[/TD]
[TD]John Smith[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
-----------------------------------------------------------------------

... currently i KNOW the only possible things that can be in that basket, however, in future, it may be possible to have a PINEAPPLE in that basket or perhaps the order of the headers needs to be APPLE, PEAR, BANANA... In actual fact, these can change very regularly, perhaps even on a weekly basis.
I want to be able to simply add another PINEAPPLE checkbox and label on the userform and 1 more line of code in the Submit_Click() procedure.

That code, translated into a sentence would be: "In Table1, on the next empty row, go across until you reach the column with the header PINEAPPLE and input True/False depending on the Pineapple Checkbox's Value"

I basically want to NOT use range.offset(x,y),select = blahblah.value as this leaves a lot of possibility for things to be inserted incorrectly in the future.

I believe i was working on a solution last time that had a procedure "getCol(headerName as String)" that was given the title (eg: "PINEAPPLE") and would return the column... I imagine I would've used it like this:
Cells(nextAvailRow, getCol("PINEAPPLE")) = checkBoxPineapple.value

I get the feeling i am 9/10ths of the way there, but am ignorant as to which procedure to call and/or how to use it. What I really need is what to put in the getCol() procedure so that it will look in the first line of Table1 for the header given and return the Column.

 

[Norie]

bryner

You seem to be trying to set a database rather than a spreadsheet.

What are you doing with the data?

 

[bryner]

Actually, I am. I know access would be a better tool, but unfortunately we don't have that program.

 

[bryner]

... The data is summarised on another worksheet for easy analysis.

... This tool that I'm doing is a non essential part of my role... But if i can make it impressive enough, and show potential, then yes, I may be able to get Access.