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]

Hi,

Just some thoughts

You could add all required textboxes at design time in the frame & in code, set the Frame Scrollbar property by the number entered in the Items on quote textbox. This would allow user to scroll within the frame, the number of textboxes entered - Remaining textboxes would be set to be hidden.

To Output to Sheet you could enter all data in form to an array & post to bottom of your sheet, you could then sort the data descending order.

As your project is quite complex, I would suggest you place copy of your workbook with dummy data in a file sharing service like dropbox & provide link which would make it easier for forum to assist you.

Others here may have alternative ideas.

Dave

 

[MrACED]

Thanks Dave,

If I am understanding you correctly, you are recommending having all of the textboxes (99rows of them) there already and only, make active (un-hiding), the number that is entered into the "How man items.." Text box?

I would like to output the Item rows in order but then merge the left most cells so all of those rows of items belong to the same quote #.

I will try to work on posting a dummy file to this thread soon.

Thanks for the help!

 

[Norie]

Couldn't you use a listbox?

The user fills out the INFO frame and below that there's a, let's say, a DETAILS frame with 2 textboxes for description and quantity and a command button.

The user fills out the quantity and description, clicks the command button to add them to the listbox, the textboxes are cleared and they enter the next set of details.

 

[dmt32]

Thanks Dave,

If I am understanding you correctly, you are recommending having all of the textboxes (99rows of them) there already and only, make active (un-hiding), the number that is entered into the "How man items.." Text box?

I would like to output the Item rows in order but then merge the left most cells so all of those rows of items belong to the same quote #.

I will try to work on posting a dummy file to this thread soon.

Thanks for the help!

I was just thinking about possible way you might be able to do it without too much coding - It is possible to create the specified number of textboxes at runtime but in order to assist, would be helpful to see a copy of your workbook.

Alternative suggestion by @Norie may also work for you.


Dave

 

[MrACED]

Here is a basic picture of a Dummy File. What is the best way to get you or post a dummy file to work with?

I also created an example for how I would like Multiple item quotes to be added to the list, note how some cells are merged and some are not? If I do it like this my thought in the future to export this information "Export to Quote" button to automatically create other documents and header information on our quote letters or quote summary sheets.

 

[MrACED]

Merge Example

 

[dmt32]

Here is a basic picture of a Dummy File. What is the best way to get you or post a dummy file to work with?

Hi you need to use a file sharing utility like dropbox Dropbox Basic (Free account)
but there are others you can search for

Dave

 

[MrACED]

Quote Log 2.0_Dummy.xlsm (dropbox.com)

Does this link work?

 

[MrACED]

Couldn't you use a listbox?

The user fills out the INFO frame and below that there's a, let's say, a DETAILS frame with 2 textboxes for description and quantity and a command button.

The user fills out the quantity and description, clicks the command button to add them to the listbox, the textboxes are cleared and they enter the next set of details.

The only thing I can think of here, Is there might be more than 2 textboxes, and I want them to be dynamically created based on the "Quantity Of items" that the user enters. I already have a second frame called "Items" where I want the textboxes to populate or exist... maybe I don't need another frame? I'm naive when it comes to the coding portion so excuse me if I am mis-speaking!