Adding Data Entry Form for a company Quote Log

[MrACED]

Good afternoon,

I have an extensive Quote Log on a single sheet that keeps track of all the active and in progress quotes for our company.
Data is entered row by row. Column headings as shown below (Image1). As you can see I have Buttons I would like to add to do different things.

SetFocus on the first button... "Add RFQ" a user would click this button and a form pops up to enter the initial information Columns A thru I as shown in Image2 in the "INFO" frame. As you can see I have successfully created and formatted the user form (Image2 below) and linked the data to the correct cells once I click the command button within the user form called "Add RFQ."

The complexity with the last text box in the user form where the user is asked "How many items on this quote?"
I would like to have the user enter an integer from 1 to 99. and then dynamically in the frame to the Right ("ITEMS") have that integer of text boxes pop up for the user to fill with the Item description and another text bock adjacent with the Qty for that respective item.

Now, Once the user Has all the items filled in they can finally click "Add RFQ" and then what I need the cells to look like is also a little complex.
I'm thinking that I want those Items inserted to the worksheet as their own rows for linking to down stream worksheets. Therfore, If each item is inserted as it's own row I then need to to merge the left most cells Coulumns (A thru H) so the quote # and "INFO" (A thru H) still encompasses however many rows ("ITEMS") are inserted for that respective quote #.

One additional thing to think about is having the Command Button insert all this information at the top of the list instead of the next open row at the bottom, that way most recent information is always at or near the top.

Thanks guys,
Any input is much appreciated. This is the first step towards the master plan of this excel spreadsheet!

 

[dmt32]

telling

Quote Log 2.0_Dummy.xlsm (dropbox.com)

Does this link work?

telling me folder does not exist - suspect you have not uploaded it correctly

 

[MrACED]

telling

telling me folder does not exist - suspect you have not uploaded it correctly

Quote Log 2.0_Dummy.xlsm

Shared with Dropbox

www.dropbox.com

Maybe this one?

 

[Norie]

What other textboxes might there be than Quantity and Description?

The idea is instead of a frame with rows of multiple textboxes you have a listbox with quantity and description columns.

Under the existing INFO frame you add another with 2 textboxes and a command button.

The user enters the quantity and description in those textboxes, clicks the button and the are added as a new row in the listbox.

No need to create multiple textboxes, no limit to how many rows the user can add and easy to transfer the data from the listbox to a worksheet.

 

[dmt32]

Maybe this one?

Yes that link worked OK

Before can offer any help need to understand the data being posted from TextBoxes in Frame 1 to your worksheet
Ignoring textbox 8 user enters item qty value - You have 7 textboxes that you want to post values to columns A-H which is 8

FRAME1

Quote # TextBox1 > Column A

Project # TextBox2 > Column B

Request Date TextBox3 > Column C

Quote Due TextBox4 > Column D

WIP – BY ?? > Column E

Customer TextBox5 > Column F

Contact TextBox6 > Column G

End User TextBox7 > Column H

Can you tell me where Column E gets its values from.

Also, you need to decide which approach you want to take forward with your project i.e.

- Create Textboxes on the fly

OR

- using a listbox suggested by @Norie

either is doeable

Dave

 

[MrACED]

Yes that link worked OK

Before can offer any help need to understand the data being posted from TextBoxes in Frame 1 to your worksheet
Ignoring textbox 8 user enters item qty value - You have 7 textboxes that you want to post values to columns A-H which is 8

FRAME1

Quote # TextBox1 > Column A

Project # TextBox2 > Column B

Request Date TextBox3 > Column C

Quote Due TextBox4 > Column D

WIP – BY ?? > Column E

Customer TextBox5 > Column F

Contact TextBox6 > Column G

End User TextBox7 > Column H

Can you tell me where Column E gets its values from.

Also, you need to decide which approach you want to take forward with your project i.e.

- Create Textboxes on the fly

OR

- using a listbox suggested by @Norie

either is doeable

Dave

Column E would be filled in Manually or maybe using another button down the road. It gets filled in after the head of the department assigns quotes to other individuals using a two or three character name abbreviation. So I don’t need that data entered in this first user form yet.

let’s start with the text boxes on the fly approach. Unless you think the listbox is a better option. I just thought the text boxes on the fly were a good way to lay it out.

 

[dmt32]

let’s start with the text boxes on the fly approach. Unless you think the listbox is a better option. I just thought the text boxes on the fly were a good way to lay it out.

There are pros & cons with either suggestion for example -

with your textbox idea
Pro - user can type directly in each textbox & edit entry directly if required
Con - code for creating & placing textboxes is a little more involved

Listbox idea

Pro - you just have two textboxes to enter values in & no limit to no entries you want to make
Con - code will be needed to allow user return entry from listbox to textbox if edits are required

its a matter of choice & either suggestion can be made to do what you are asking but it would be a little unfair on any contributor to make a "start" assisting developing code only to switch to another at later stage - have think which you prefer.

Dave

 

[MrACED]

There are pros & cons with either suggestion for example -

with your textbox idea
Pro - user can type directly in each textbox & edit entry directly if required
Con - code for creating & placing textboxes is a little more involved

Listbox idea

Pro - you just have two textboxes to enter values in & no limit to no entries you want to make
Con - code will be needed to allow user return entry from listbox to textbox if edits are required

its a matter of choice & either suggestion can be made to do what you are asking but it would be a little unfair on any contributor to make a "start" assisting developing code only to switch to another at later stage - have think which you prefer.

Dave

Okay!
well let’s go with the dynamic text boxes. That’s what I think I’ll like better.

 

[dmt32]

Okay!
well let’s go with the dynamic text boxes. That’s what I think I’ll like better.

Just playing at moment but to give you an idea form would look something like image:

You will note that I have added back in the missing WIP field (which is greyed out) as this makes indexing array to capture data easier
Also, I am not following your suggestion of using merged cells for Info data - merged cells are regarded as an abomination for programmers & best avoided.

When I have all working, will post link

Dave

 

[MrACED]

Just playing at moment but to give you an idea form would look something like image:

You will note that I have added back in the missing WIP field (which is greyed out) as this makes indexing array to capture data easier
Also, I am not following your suggestion of using merged cells for Info data - merged cells are regarded as an abomination for programmers & best avoided.

When I have all working, will post link

Dave


View attachment 27485

Perfect,

Looks exactly how I wanted, and it dynamically updates based on the "How many items cell" perfect.
WIP is a good addition in case Dept. Head wants to assign an estimator at time of entry, so it would be good to have.

1.) Is there a way to give the user the option of where they want to enter the next RFQ in the list?
I think I want the default to always be the first row, as to have most recent quotes at the top of the list instead of the bottom!
But sometimes there would be a need to insert the RFQ above or below a pre-existing entry. such as revised quotes for example.

2.) I know merging cells can be frustrating for downstream formulas and coding! Maybe you can provide a better options.
What I am trying to describe visually when you're looking at the Quote log, is that all those 10 items belong to the Single RFQ described in the "Info" frame.

So when I look at the sheet from left to right (Using your Example) I was thinking I would need 10 rows to cover all the items, but then merge other cells that describe that one RFQ. (See attached Picture)

3.) Eventually My goal is to eliminate double/triple data entry on downstream documents like quote summary's or final quote letters.
I thought that if I kept the items, quantities, prices, in individual rows/cells it would be easier to pull or link those cells to those other documents.

Let me know your thoughts! Looks like we are on a great path!

 

[dmt32]

Hi,
- WIP I will leave for data entry rather than grey out
- For 10 items you will post 10 lines to your worksheet with Columns A - H repeated for each line.
- all entries will post to next blank row at the end but data can be sorted (date order or RFQ?) if needed.
- after each entry, form will clear & depending on your numbering system, possible to display next number in sequence

Just avoid merged cells they are more trouble than worth.

Dave