I apologize that this is very poorly formatted and my thoughts are a bit ramble-y.
I have an idea of what I want to do in general, but I'm having trouble with putting it all together.
I have a large database of possible light bulb changes.
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>X</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Code</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Watts</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Conversion</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Code</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Watts</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">QTY</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Electronic 2 Lamp 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx2Lx4'-IS N</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">59</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">0</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Elec. (High Pow.) 2L 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx2Lx4'-IS H</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">77</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">0</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Elec. (Low Pow.) 4 Lamp 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx4Lx4'-IS R</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">102</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">18</td></tr></tbody></table>
I want to be able to enter multiple lines of data with qualifiers for that list
example:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="border-bottom: 1px solid black;;">old</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="border-bottom: 1px solid black;;">new</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="border-right: 1px solid black;;">Area</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">quantity</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">lamps</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">length</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">lamps</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">length</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">power</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Main</td><td style="text-align: right;border-top: 1px solid black;;">5</td><td style="border-top: 1px solid black;;">T12</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="border-top: 1px solid black;;">T8</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;;">l</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">storage room</td><td style="text-align: right;;">1</td><td style=";">T12</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: right;;">
</td><td style=";">T8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style=";">n</td></tr></tbody></table>
So I think I'm okay getting that to enter into the table.
I want to make hidden columns on the first table that are formatted like the 2nd table and have some code that goes row by row and filters out the data then adds to the quantity column. If you think that's a good way to do it, then what I need help with is how to enter that data easiest (if you don't, please tell me an easier/better way).
There is going to be a different number of lines to enter every time.
Ideally I want to enter them via the VBA Form I have set up to enter all of the customer data, but I'm not sure how to get text boxes based on the number of lines I need.
I need all the text boxes at once, but I only want as many as I need so that I can quickly tab through (vertically, not horizontally) without having to click the top of the next column every time I get to the bottom.
I wanted to have a field where I could input the number of rows I'd need and then show/hide text boxes and change the size of the form based on that, but I don't know quite how to do that.
I'm under the impression there isn't a way to do something like: textbox#VARIABLE.visible = true, and when I tried reading about textbox/label arrays, I got very confused (people started talking about creating classes and things that were beyond my comprehension).
Should I just give up and enter them into the Excel sheet? Either way I'm going to need vba to add up all the quantities as well as alerting the user if a row had an error (nothing matched, or more than one matched) , so I wanted to just do it when I added the info off the VBA form.
Anyone with any help or better ideas please share.
I have an idea of what I want to do in general, but I'm having trouble with putting it all together.
I have a large database of possible light bulb changes.
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>X</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Code</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Watts</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Conversion</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Fixture Code</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Watts</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">QTY</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Electronic 2 Lamp 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx2Lx4'-IS N</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">59</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">0</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Elec. (High Pow.) 2L 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx2Lx4'-IS H</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">77</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">0</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT12-34Wx4Lx4'-2MG</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T12 Magnetic 4 Lamp 34 Watt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">144</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4 Foot T-8 Elec. (Low Pow.) 4 Lamp 32 Watt</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FLT8-32Wx4Lx4'-IS R</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">102</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CC00;;">18</td></tr></tbody></table>
Fixture Data
I want to be able to enter multiple lines of data with qualifiers for that list
example:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="border-bottom: 1px solid black;;">old</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="border-bottom: 1px solid black;;">new</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="border-right: 1px solid black;;">Area</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">quantity</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">lamps</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">length</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">lamps</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">length</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">power</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Main</td><td style="text-align: right;border-top: 1px solid black;;">5</td><td style="border-top: 1px solid black;;">T12</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">
</td><td style="border-top: 1px solid black;;">T8</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;;">l</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">storage room</td><td style="text-align: right;;">1</td><td style=";">T12</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: right;;">
</td><td style=";">T8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style=";">n</td></tr></tbody></table>
Master
So I think I'm okay getting that to enter into the table.
I want to make hidden columns on the first table that are formatted like the 2nd table and have some code that goes row by row and filters out the data then adds to the quantity column. If you think that's a good way to do it, then what I need help with is how to enter that data easiest (if you don't, please tell me an easier/better way).
There is going to be a different number of lines to enter every time.
Ideally I want to enter them via the VBA Form I have set up to enter all of the customer data, but I'm not sure how to get text boxes based on the number of lines I need.
I need all the text boxes at once, but I only want as many as I need so that I can quickly tab through (vertically, not horizontally) without having to click the top of the next column every time I get to the bottom.
I wanted to have a field where I could input the number of rows I'd need and then show/hide text boxes and change the size of the form based on that, but I don't know quite how to do that.
I'm under the impression there isn't a way to do something like: textbox#VARIABLE.visible = true, and when I tried reading about textbox/label arrays, I got very confused (people started talking about creating classes and things that were beyond my comprehension).
Should I just give up and enter them into the Excel sheet? Either way I'm going to need vba to add up all the quantities as well as alerting the user if a row had an error (nothing matched, or more than one matched) , so I wanted to just do it when I added the info off the VBA form.
Anyone with any help or better ideas please share.
