Adding Data Entry Form for a company Quote Log

MrACED

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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."
Image1 Quote Log.png


Image2_Dynamic Items List.png


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!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,377
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,377
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,377
Office Version
  1. 2019
Platform
  1. Windows
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.jpg
 

MrACED

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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!
 

Attachments

  • Merge Example.png
    Merge Example.png
    17 KB · Views: 0

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,377
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top