Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

"change Order Form"

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Does anyone have a change order template that I can review?



Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics









Hello

i dont know much about using macros in excel. i have two spreadsheets one called "Order Form" and the other "Update". order details are entered into "order form" but i would like to copy the details into "update" so i can keep a running log of all orders placed. at the present when the order is sent over to England the info in "order form" has to be printed out and kept but this ends up with loads of sheets and is too hard to keep track of what has been ordered and what has arrived/not arrived yet etc. if i could get the order details copied into the "update" sheet would mean i have a list of all orders and can then search or filter when needed. sometimes there will be more than one row of data in the "order form". can i do this by using a command button which will also automatically delete the info in "order form"?



Hi I have the below piece of VBA code which works very well. What it does it calls a image from a worksheet called "Pictures" based on a selection make from a drop down list in cell A9 on worksheet "Sale Order Form". It does all of that perfectly, but I need to change the code so the image is display on both the "Sales Order Form" worksheet like it does now, and on another worksheet called "Customer Sales Order Form" in the same position.

To sum up I don't want to change what this code is doing right now, all I want is to add on to this code to have it display the image both on the current sheet and a second sheet called "Customer Sales Order Form".


Is there anyone out there that can please help me.

Thanks in advance Dave

Example Workbook

example spreadsheet.xlsm

The Code



Please Login or Register  to view this content.





Hi I have the below piece of VBA code which works very well. What it does it calls a image from a worksheet called "Pictures" based on a selection make from a drop down list in cell A9 on worksheet "Sale Order Form". It does all of that perfectly, but I need to change the code so the image is display on both the "Sales Order Form" worksheet like it does now, and on another worksheet called "Customer Sales Order Form" in the same position.

To sum up I don't want to change what this code is doing right now, all I want is to add on to this code to have it display the image both on the current sheet and a second sheet called "Customer Sales Order Form".

Normally I would include the sheet but it has private information on it.

Is there anyone out there that can please help me.

Thanks in advance Dave



Please Login or Register  to view this content.



Hi, I am wanting to create a spreadsheet for my stationary orders with some macro's and I have no idea on how to set this up. All the product information will be in Sheet 1 and the Order Form will be in Sheet 2 of the document.

In "sheet 1" we will make a list of all items that get ordered on a regular basis. This can be up to 500 items but
I have given you an example of 6 to play with.

We need the "QTY", "Unit Price" & "Total" in both "Sheet 1" and "Sheet 2" to sum up with a formula

Once the user has chosen the line item they will click on the "add" button next to the line item.

This will inturn take that particular line item and insert only the "Code", "Product", "Unit Price" & "Total" columns in "Sheet 2"
and leaving out the "Page Number", "Item Number" & "Description" & "QTY" fields to Sheet 2 (Order Form)
The user will manually change the "QTY" field on the "Sheet 2" (Order Form)


When the user selects another product and clicks "add" then this new line item will be added below the first line item
on Sheet2 (Order form) and so on.


Please have a look at my example which I have included In this email.

We will probably only be ordering 10 - 50 items each time so we wont need a huge list on the order form (Sheet 2)

Many thanks in advance everyone

Grant


I want to create a template in Excel for a change order system. Every time I have a new change order I want it to be numbered. I want Excel to automatically keep a log of all the changes orders to date with change order number, date, title, etc.

Please help.

Thanks!


Hi guys,
I hope you can help. I use excel to retrieve information from Synergy through ODBC. I have several sheets for each day of the month. What I do at the begining of every month is to edit every query for every day of the month and update the retrieval date and times. It is quite tedious. I've been doing it for 2 years and I would like to know if there any macro/script that can help me have the dates entered dynamically or with little user interaction. Here is the SELECT statement from the query ran

Code:

SELECT "NO:Release Order"."RO #", "NO:Release Order"."RO Type", "NO:Release Order".Category, "NO:Release Order".Status, "NO:Release Order"."Creation Date", "NO:Release Order"."Date & Time of Work", "NO:Release Order".Function, "NO:Release Order"."Job Type"
FROM "NO:Release Order" "NO:Release Order"
WHERE ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Approved') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='TC') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Approved') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='New') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='TC') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='New') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Re-Scheduled') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'})
ORDER BY "NO:Release Order".Category


Thanks in advanced,

JP


I have a form that feeds a table. This form has a text box which feeds a certain field of the table. The field is the number of a change order.

So I open up the form and begin populating all the info for the change order. The very first thing I manually plug is the number of the change order.....and its I do this in numerical order of course.

I'm up to change order 4003 as I write this. How do I make the text box automatically populate with the next number in line so i do not have to manually plug it.

Granted not a big issue but do this multiple times a day and it gets annoying. I'm trying to automate as much as I can.


Hi all,
I have a workbook template that has 31 sheets in it, the first 2 of which are defined by the template, the others can be user modified.
On the first sheet, there is a list of the names of the other sheets (amongst other data) in B3:B31, and I would like to be able to change the names of the associated sheets by changing these cells contents. The order of names in the list doesn't change, but the order of the sheets in the book can.
I have written the below code, which works, but is really slow, even just changing cells with the keypad causes it run and so hang for a second.
The other issue with it is that it refers to the sheets using their position in the book (sheets (ref)) which can change, or other sheets get inserted, but I dont know how to to apply the sheet# method of reference when the number needs to change. I know I can hard code a lot of this but would prefer a more elegant method.
I know there must be a much better way of doing this, but I cant work it out.



Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    For i = 3 To 31
    
    Set Target = Range("b3").Offset(i - 3, 0)
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    Sheets(i).Name = Range("b3").Offset((i - 3), 0)
    Next i
    Exit Sub

Badname:
    MsgBox "Please revise the name." & Chr(13) _
    & "Do not include /, \, ?, etc, and " & Chr(13) _
    & "keep to less then 31 characters." & Chr(13)
    Range("b3").Offset(i, 0).Activate


End Sub



Thanks,


I have a word document with numerous standard fields that do not change. These fields are in a template format and have been bookmarked. I want to write a macro that allows a user that is in the Word doc to click an 'update' button and it retrieves the latest data from an Excel file. The Excel file will never change name and will always have the same three columns. But the data order will change (row order only) will change over time. The three columns in Excel are as follows:

Column A: Company Name
Column B: Data Field Description (ties back to the bookmarked fields in Word)
Column C: Data Field (this has the value / text that I want to be pulled into Word)

The Excel file is saved on a network drive and will not change location and the name will not change either.

What is the VBA code I need to allow these fields to be updated in Word. I am happy to 'hard code' the company name in the VBA code and then change it for each new template I create. It will only ever need to be done once per company.

This is what I have started with and it's really not working:

Code:

Private Sub CommandButton1_Click()

Dim myWB As Excel.Workbook


Set myWB = GetObject("H:\QPR\Sep 2009\Import Template.xls")

Selection.GoTo What:=wdGoToBookmark, Name:="Text5"


If (myWB.Sheets("DataDump").Cells(A1,A999) = "Company1" Then if myWB.Sheets("DataDump").Cells(B1:999) = "Senior Limit") Then
   
   'Return the value in column 3
   
   'Else leave the field as is
        
    End If

Next

Set myWB = Nothing

End Sub


Is this at all possible?


Greetings everyone...

Can anyone help with a couple of queries I have...

I have a purchase order that is saved when completed using the following code 'Application.ActiveWorkbook.Save' but I'd like it at the same time to save another copy of the workbook as a backup. Is this possible?

Also on the same worksheet ("new purchase order") my code copies it to a new purchase order and gives it a consecutive order number. It then clears the "new purchase order" sheet to start again. My query is this...

Instead of clearing the "new purchase order" sheet, is it possible to delete the "new purchase order" sheet that was just used and then simply create a new "new purchase order" sheet by copying it from a "template" sheet I have created??

Sorry, a bit long winded but I'm getting very confused!! Incidentally can anyone recommend a good book for excel programming for the complete novice??

Thanks in advance


I am trying to copy the information selected in the "Order form" and consolidate it into the "Order Summary" sheet.

If someone could help me with a function/formula that searches the "Order Form" and filters all the items that have had a number added in the "Qty" Column and place them and their correlating txt chosen from drop down box into the "Order Summary" sheet it would be greatly appreciated. (Please excuse the messy sheet but had to remove macro's to reduce file size.)

Thanks in advance

Brad

Say I have order sizes streaming in from another program. If the most recent order is less than the previous order, I want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black.

How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?




Hi All

I have an order form. The first field is unbound and is used to write in the customer surname. on Key down event a second continuous form opens to the required search results through using "Docmd Openform where condition."

you then select a name the form closes transfering the CustomerID to the original order form using "Docmd Openform where condition." then away we go with rest of details for order.

The problem I have is if I want to change the customer it goes to the last record for the newly selected customer. If I manually change the customer ID it retains the order details and puts in new customer however if I select a new customer via the above key down event I loose the details of the order and it brings up the last order attributed to that customer.

Any help would be appreciated.


I have an order form template at work in which I have =TODAY() to give me the date an order was created.
Opening the form subsequently gives that days date of course and the original order date is lost which is a bit of a nuisance.
Is there a formula to give a "one time only creation date" that does not change when re-opened?
Any help on this would be appreciated,
Many thanks,
papillon




Hi I want to try and "total" the purchase price of an order form based on the following example. Each week there will be a different number in the "Your Order" column but the price per item is a constant.
Also I may NOT order every product - every week so only a few of the boxes in "Your Order" may have a value in there.

Any help much appreciated

order6.png

TOTAL PURCHAE PRICE $17.95

We use SAP. From SAP we download a report and use for reporting thru a macro. For the same order reference, there are two customers. The report will show the order no. col.1, customer "A" in col.2, product in col.3, order qty. in col.4 say (qty=100) and shipped qty in col.5 with value "Zero". Once this order is invoiced, then the report will show the first line as per above and in the next line, it will show the same order no. in col.2, customer will be "B" in col.2, same product in col.3, order qty in col. 4 with value "Zero" and shipped qty in col.5 with the "qty=100".

I want to look for these duplicate order nos. for the same product and report the order qty & billed qty in the same line to avoid multiple lines. Below is how it looks. I can change the customer B to Customer A in my macro. But after that i dont know how to bring both order qty and bill qty value showing in one line.

Is there a way to do this using vba code?

Party | Sales Doc. | Material | Order qty | Bill qty
Cus.A | 577001 | Prod.A | 100.00 | 0.00
Cus.B | 577001 | Prod.A | 0.00 | 100.00

I have a form that has multiple input boxes for counting inventory. I need some code to change the tab order if a text box already has a value in it.
I want to have the tab order change to the next empty box.
Count box 1 has value 18 and text box 2 has value 3, I want the tab key to skip text box 1 and 2 and go to text box 3.
Any suggestions?


Hi,

not sure if this is possible but here goes.

I have created a stock sheet using excel, and so far everything is ok. But i would like to change the cell colour based on the result.
e.g. my if statement is this:

=IF(F11<=3,"Need to Order","Ok for Stock")

Basically, if the stock level goes below 3 on any item, it shows "Need to order" and when this is shown, i would like the cell to change to Red, or any different colour which makes is stand out. As we have hundreds of stock items, and it would be helpful if we could just look at the sheet and know exactly what needs ordering straight away, without having to fish out the "need to order" text!

Hope this is possible, but if not, are there other ways around this?

Thanks in advance

Rob




Hi, I'm about to finalize my monthly billing report but am left with 1 more challenge...

On my report, column A conatins an order number.
I am am allowed to charge a fee for services provided, but this is 1x fee of (let's say 100 USD) per order. This fee is included on column B.

The problem is that some order numbers are showing up more than once on that report,
and I am currently manually skimming through the report to add the '100' USD fee to each new order.

The file is already sorted by order number, so I am hoping someone knows the code to 'detect' a change in order number, and then for each 1st line of the order number that is detected to change a 100 USD fee.
Any additional lines should be marked with a value of 0 USD.

Thanks

I have an order log and want to add a status and colour code.

Currently I do this manually.

Columns A-H include general information like customer, country, order number ect.

I = Due Date
J = Despatch Date
K = Invoice Date

When the order is outstanding but not overdue the colour of the whole row (A-L) should stay white.

When it is overdue (or due today) the colour should be orange,

When it is despatched it should change yellow

and when it is invoiced it turns green.

I can't find the right way of doing all this in one forumla. I can get it to change orange automatically when it is over due using

Code:

=IF(L2<>"","",IF(J2<>"","",IF(A2="","",IF(I2="","",IF(I4507<$N$1,"A","")))))

N1 = today()


in M

The conditional format then looks for A in M and changes the colour of the row based on this.

I realise I can right similar If statements in N, O & P and then get the conditional format to monitor these as well but there must be an easier way.


I have an order form that I create but it is too lengthy when it is printed out. Everything on the list prints out including the items not ordered. I want to be able to do the following. This order form contains warehouse supplies used for shipping purposes. There are over 50 items on this list however we do not always order everything on this list so if I can create a formula that will only display the item ordered when a value is entered.

Example:
Description PRODUCT CODE/On Hand Inventory/Inventory/Qty to Order
1/2" METAL STRAPS (ROLLS) 4 10 6

I would like to create an "OK" command button. If a value is entered in the onhand inventory column I would click "OK" and this form will open in another window only displaying that items that was selected. Also I would like to have an option to print.

Can anyone help???


Good day all,

Please in the attached template, I have column named sales order for each of the named sheets. What I want is for the system to check through the sheet named "confirmation page" and return "found" on the column named "confirm" on the sheet named "template" for any Sale Order number present on the two sheets. If the sales order is available on "template" only and not "confirmation page, let the system return blank.

Hope my explanation is understood.

Thanks a lot for your usual assistance.


Jude.


I have a spreadsheet that the company uses, that when opened, inserts today's date in a cell. When we are done filling out the form and save it to our server, I reopen the file and run a macro that creates the order form and sends the pertinent information to our shipping and receiving department, including the date of the order.

The problem is that when I reopen the file to create the order form, it updates today's date, instead of leaving the original order date.

Is there a way to copy the original date to the order form (same workbook) so that it doesn't update?

Currently, I use a macro that creates the order form automatically behind the scenes every time the user collapses the sheet to clean up any blank rows for the customer to approve. Maybe the solution is to have another button that says "Finalize Order" or something and then puts a non-volatile date on the order form.


I am doing a order form and when I enter the oder number if it starts with
"0" after I have entered it and go to the next order it delets the "0"



I am having trouble with a line of code I wrote...Under the Selection Change Sub I have written:

Code:

Dim Ans as integer
If Not IsEmpty(Target) Then
Ans = MsgBox("Do you wish to change your order?", vbYesNo, "Change Order?")
    Select Case Ans
        Case Yes
            Target.Activate
        Case No
            Cells(NextItem, 2).Activate
    End Select
End If


NextItem is a user variable I set to just be a countA+1 as in a new order

However "Case No" is not responding and the target is just activated anyways? Is this code wrong?