Forms to fill in a table and assigns data

I have a quote tracking spreadsheet where we manually assign quote numbers. I was hoping to use a form to automate/simplify it. The quote number is already predetermined in the first column so I would like the form to locate the next open number and then prompt the user to fill in fields that it will put into the table and save. the next step then would to be potentially be able to open our quoting spreadsheet and add those details into that spreadsheet. The goal is to have a form that the user opens, fills out to claim a number and set up the quotation sheet.

I don't know what tools to use to make forms so any direction to tutorials/tools for form making that would allow me to go this route would be great. I am not great at programming, I am just starting to write my own macros after copying the record macros coding and figuring out what it is doing.

The quote tracking table looks roughly like this.

Quote | Estimated By | Description | Requote (Y/N) | Company | Customer | Request Date |


The first column has the preassigned number and the estimator needs to fill in the reset of the data. The form should locate the next open row and fill in the information with what the estimator inputs.

Thank you

.
This sample project will get you going. The form textboxes (sheet fields) can be expanded as required.

Please Login or Register  to view this content.

A couple of approaches.
The simple one merely produces a printable template filled with basic details.
The second approach produces a full listing record and complete template quotation.
The save routine is not complete as the 'client' dropped off the radar.
The simple file - click on customer to choose from listing - click on blank 'item name' and choose from stock items.
To start off the 'gate' quoter press green button in A1 to launch form then click 'new' to allocate a new quotation number.
See if any ideas are useful.
torachan.

Hey, So this is working really well for me so far. I am now wonder how I can copy a folder and rename it based off of what is being input into the text boxes. I have a button that is tied to a macro and that macro is successfully copying the folder and placing it in the correct spot but I don't know how to get it to name the folder based on what is written in specific boxes in the form. Is that possible?


I don't know how to get the code to format nice in a post but this is what I have

Private Sub CommandButton2_Click()

Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.copyFolder "C:\Customers\Acoustic\AMI - Texas\Qxxxxxx", "C:\Customers\Acoustic\AMI - Texas\" & "Me.txtDescription.Value"
End Sub

Before moderators 'smack' your knuckles, place your code between code tabs, as per forum rules.
See big yellow banner - how to upload your file - its a lot easier to troubleshoot a workbook rather than a random piece of code.

I think I attached the workbook here.4

The first button "claims" a quote number

The second button closes the window.

The third button opens a new quote sheet.

It's the 4th button I am stuck on. I'd like it to make a new folder that is located in an folder based on what was input into the fields. I can get it to copy and paste the new folder but I cannot get it to change the name of the folder to the contents listed in the form. Is that possible?

.
First ... how do you know the user will already have this path on their computer :

"C:\Customers\Acoustic\AMI - Texas\Qxxxxxx"

This is just a testing location. It will be going to a network location and that location can be derived by the info that gets input into the form fields. That's why I am trying to be able to reference that data. At hte very least, I could have it claim the quote number by inputting the data into the table and then pull the data from the info that has been input. Maybe it can be done in one button click. Claim the quote number and then use the information in the table to rename a folder.

P:/Users/Customers - This is the common part for all locations Then it goes off the following

P:/Users/Customers/"Company"/"Customer"/"Q21600 - Description"

In each customer folder are the projects for that customer. We have a finite number of customers that come back over and over for projects and we start a new folder each time to document the project. A project starts when we fill out this table. So each customer folder has a Qxxxxx folder as the blank folder to copy and paste to start a new project. The folder gets named with the claimed quote number and then the project description.

Let me know if that makes sense.

.
Ok ... another question ... Why is the goal to copy an existing folder then rename it ?

Why not just create a new folder with the name you are desiring ?

Because the Qxxxxx folder has sub folders inside it along with blank project documents. I want that format to be copied over for every new project

.
Then you are wanting to copy the folder and the files within as well ?

Are there other folders inside the main folder ?

Yes, which this is doing successfully already but I cannot get it to rename the folder properly.

.

Please Login or Register  to view this content.

Hopefully this portion of the path (AMI - Texas) won't be an issue with the existing spaces and the dash symbol.

That worked!! I've got it really rocking now! Thank you!

If I could ask for some more insight on one more thing... When claiming the new quote number, is it possible to to reference that number in the naming portion of the folder? I am thinking it would need IF THEN Statements which I am not good with. I can do the reading, research and learning but would you be able to confirm that what I want to do is doable and I am going down the right path?

What I am trying to accomplish, is this doable and am I on the right path with If Than's?

Basically column A has the list of quote numbers that is theoretically endless. this form is set to fill in the open row from column B through Column G. this is the "Claiming" of a quote number. Once that spot is claimed, I make the folder with the button click. When that folder is made and being renamed (The Me.txtdescription.Text that you helped me with.) can it also pull the associated value from column A, the quote number that has been claimed? In short, the form claims a number in Column A, makes a folder in the appropriate customer folder and then renames it. Currently it renames it with the description in the form but I would also like it to have the quote number in Column A in the row that this is submitted to. Let me know if that question makes sense. Thanks!

.
Untested here ... see if this works :

Please Login or Register  to view this content.

So that definitely works. Is there a way to pull it without having to enter the number manually?

.
This is one method :

Please Login or Register  to view this content.

This should give you a starting point toward your goal.

Ok, I made a few tweaks to it but boy am I really getting somewhere now. I appreciate it. I combined everything into one button and I have it so it fills in the table, Makes a new folder based off the inputs and new quote number. Opens the quote worksheet and saves a new copy in the appropriate sub folder inside the new project folder that had just been created. I honestly think that is all I need to have it do. I really appreciate all of your help on this, seriously, thank you!

.
You are welcome.