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!

 

[MrACED]

Okay, we can avoid merging cells for now, I really didn't want to have a bunch of repeating lines. Probably still needs some sort of work-around.
I didn't know if maybe there was a way to code each item as it's own "Carriage Return" keeping it in one cell and then later on pull information based on Carriage Return Lines/Strings?

QUOTE NO. BACKGROUND AND FLOW
Example Quote No.: AAA-2020-1207-01
Our quote number is described as.... three digit customer abbreviation --> 4digit Year --> 4digit month/day --> 2digit quote ID # on that specific Day.

I still think I need the user to have the ability of selecting (If not a default) the position of where they want to enter this information.... example if I send a quote out on 12/7/2020 as AAA-2020-1207-01 but then the customer request a revision on 12/22/2020 I want to be able to enter that revision next to the original as
AAA-2020-1207-01A.

Or if I am entering the data to the list on 12/22/20 but the actual request from the customer came in on 12/7/20 I need to put the quote in the list as AAA-2020-1207-01 not AAA-2020-1216-01. And multiple quotes could have been entered in the list over that time period

The basic flow is that this number should match the date we actually receive a request from the customer.... meaning Customer AAA sent an email on 12/7/2020 for some items and it was the first "-01" Request on that day from customer AAA. Its possible we could have another customer (BBB) the same day but since it is a different customer it would be Quote NO.: BBB-2020-1207-01. (This is also the Number we save our quote letters as)

 

[dmt32]

I am happy to assist with a little of my time with your original requirement of making the textbox idea work for you & will offer my suggestion of posting data to worksheet - you can determine then if this is something you can work with - further development of your project is something you will need to take forward to meet specific requirements.

Dave

 

[MrACED]

Oh yes, no problem. I definitely appreciate whatever help and insight you're willing to contribute. No worries at all!
This is obviously a starting block to a system in my head that is more involve than just this quote log so I definitely appreciate the help.

 

[dmt32]

Hi,

Sorry about time taken to get back to you – busy time of year.

Try attached workbook & see if does what you want: Quote Log 2.1.xlsm

The creation of textboxes on the fly, their sizing & placing on the userform requires much more code than the listbox suggestion – it’s not an issue just a matter of preference, either approach can work.

I have broken the code down as much as possible & added comments which should make it a little easier to understand what I have done.

The userform data when output will post to the next blank cell in the range – if you want the new data to top of your table you can try sorting it manually or writing some code to do it automatically – You can achieve this by turning macro recorder on which will provide some basic code.

You will also note that Data from Info Textboxes in Columns A-H is repeated for each Item (description & qty) - unlike a database where you can create a one-to-many relationship, you only have one table (flat file database) for your data and this is the only way I know how to create a relationship in Excel for each item row - I certainly would not use the merge cell idea – programmers avoid merge cells at all costs as they are just a nightmare.

some further embellishments

ADDRFQ commnadbutton1 should only be enabled when

• the Quote number in format (AAA-YYYY-MMDD-00) is entered in textbox
• the number of items on quote textbox has a value > 0.

The Quote textbox should also display RED until correct quote format entered & then change GREEN.

. Borders are added to each new record.

My workings have only had limited testing & may need some adjustments – I Hope though, what I have done goes in right direction & enables you to progress with you project

good luck

Dave

 

[MrACED]

Thanks For all the help Dave, I appreciate all the work that went into this!

It looked like there was some text after your coding that was preventing this from working. So I deleted it and then it seemed to work good.
I'll have to read through the comments to see If I am understanding it better. Any pointers on where I should start to train on some VBA for excel?

I like the enabling of the Quote number for the correct format, nice addition to try and help eliminate some human error!
I think another thing I would add here is not allow a duplicate entry, that way someone can't enter the same number someone else has already entered.

I think from here I will try to get some help on setting the default entry to go at the top of the list or best case scenario having the entry go above or below a previous similar number or maybe at least allowing the user to choose where the entry is going to go. I'm guessing I should likely post a new thread or search others for more input.

Thanks again Dave, Have a Merry Christmas and a Happy new year!

 

[dmt32]

> It looked like there was some text after your coding that was preventing this from working. So I deleted it and then it seemed to work good.
> I think another thing I would add here is not allow a duplicate entry, that way someone can't enter the same number someone else has already entered.

Can you let me have copy of text you deleted so I can update my copy

For duplicate entries, try adding following code to your userforms code page & see if does what you want

Private Sub TextBox1_Change()
If Application.CountIf(wsQuoteLog.Columns(1), Me.TextBox1.Text) > 0 Then MsgBox "Dulplicate Entry", 48, "Duplicate"
End Sub

Glad solution goes in right direction & appreciate feedback

Dave