I need to update a VBA marco within a excel spreadsheet.
The current system allows salemen to build estimates in an Excel file by using a VBA macro and form to pick products from another excel file.
Using the macro they select products from a combo box which inserts the product in to the estimate.
However, I have a problem that can result in a saleman using a copy of an old spreadheet with incorrect prices and obsolete parts.
The VBA macro I would like written will
Look in? (within the [login to view URL]) a range of speified sheets/cells? for part numbers,
1 For each part number found, pefform a lookup in [login to view URL] sheet to identify if the product is listing in a sheet called "OBSOLETE PRODUCTS".
? If true - format the cell range colour to YELLOW. Add a comment to the Product description cell "the products X is obsolete, select another part"
2 Check the price for the part is current/correct (as listed in the [login to view URL] file) If EstimatePrice < CurrentPrice THEN?
Update EstimatePrice to CurrentPrice
Add comment to EstimatePrice Cell "The estimated price strOldPrice? is less than the current price strNewprice (" & Date() & ")
format the cell range to RED.
The macro may not be in the [login to view URL] supplied by the salesman so any VBA macro may be called from another open sheet.
## Deliverables
I will supply a smaple of the two files mentioned to allow you to review the existing VBA code.
[login to view URL] means the currently focused and open excel workbook.
Not all sheets within a estimate workbook will contain parts to be checked. Usually "sheet 1" & "sheet 2"
Running the check/macro should show Progress so the user does not think the app is hanging.
Running the macro should be linked to a new button on the existing form.
Current prices are linked to a path recorded in the existing macro. The new macro must use the value listed here. i.e. If we change the path in new versions of the file we need to confirm the correct [login to view URL] is used.
[login to view URL] is a multi sheet workbook. Each Sheet is a product grouping.
Using the macro withing the [login to view URL] Take care to look at the values recorded in the config form for file path and source costs. These will be the valuse you look up.