Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Triggering a macro (Excel 2000 SP3)

    I have a job costing model comprising several linked worksheets.

    One of the sheets calculates labour costs by entering the workers name, and the hours they have worked on a particular day.

    Another worksheet contains a master list of people and their hourly rates at a whole range of different overtime scenarios.

    This master list sheet is a copy of a single master sheet stored in a separate master file. I update the sheet in this master file each time a new worker joins, or someone's pay rate changes.

    I have a macro built into my job costing model so that each time I enter the model, the payrates sheet is overwritten by a copy of the current master sheet.

    This works fine most of the time, but can cause problems, for example, when I open an old job costing model, for a job which finished a year ago. All the results change becuase my macro brings in a new master, which isn't applicable to the old job I'm looking at.

    I get the impression from an article in WOW last week, that I may be able to set up the macro so it triggers when I update the relevant data - say add in some more hours to the labour data sheet. But I don't want it to run every time I enter a figure in a cell in that sheet. I also have auto-save enabled, so I'd rather not link the running of the macro to saving the file.

    Anyone got any bright ideas as to how best to autorun my macro only at "appropriate" times?!

    Thanks

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Triggering a macro (Excel 2000 SP3)

    You will have to define what "appropriate" is - we can't do that for you. When you have, we can help you with the code.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Triggering a macro (Excel 2000 SP3)

    It sounds like you should be using an Excel template to create the workbooks rather that using that macro. Without knowing more about what your are doing, this might work:

    1- Create a new workbook that contains the job costing worksheet and the worksheet that contains the people and rates.

    2- Save this workbook as an Excel template (it should have an extension of .xlt instead of .xls).

    3- You can then create a new job costing workbook by double clicking this template, or by using File/New and selecting the template.

    4- When you update the rates, update them in the template.

    Using this process, rates will not change when you open old job costing workbooks, but new workbooks will get the new rates.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Triggering a macro (Excel 2000 SP3)

    Thank you both for your replies.

    Yes, Hans, you are right - I didn't explain it very well! I'll try again:-

    I want to only worry about keeping one master list of payrates up to date, and this is stored in a separate file.

    (I have made the assumption that although someone's rate changing mid-job will mean that the labour cost element will be slightly overstated - because the model will re-calculate their pre-raise hours at their new rate - I can live with that in the scheme of things.)

    I have separate costing models for each job. Each contains a copy of the master table as a worksheet.

    Currently every time I open any job model, the master table sheet is overwritten by a copy of the current master sheet from the master file.

    This may not be appropriate - for example if I open a model for a 2 year old job to review the outturn costs, the macro will cause the model to recalculate all the labour costs at current rates, which is not what I want.

    But similarly, I would like to automate the model so that if someone's rate changes, or we take on a new employee, I only have to update the master file, and don't have to worry about any of the individual job models.

    The article in WOW made me realise that I can trigger the macro to only run when (for example) I changed the data in my schedule of actual hours worked worksheet in the individual model. But I don't want the macro which take 2 or 3 seconds to run, to trigger everytime I change one single cell.

    Perhaps the ideal scenario for me would be for my model to generate a prompt on saving the file (albeit only when the user has updated the hours sheet), which would read something like:-

    "You have updated the labour hours table. Do you want to update the master rates table? Y/N"

    If they return on the default 'Yes', my existing macro will run. If they say "no", the model will retain the current master table, and if they open an old model just to review the data, they won't receive a prompt at all, since they won' t change any data in the hours table.

    Perhaps a bit too ambitious..............?!!

    Any help would be much appreciated, as ever.

    Cheers

    Neil

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Triggering a macro (Excel 2000 SP3)

    You can run code if a cell in a specific range changes. Say that you want to trigger the macro if the user enters or changes the value of cell D5.
    Right-click the sheet tab.
    Select View Code from the popup menu. This opens the worksheet module in the Visual Basic Editor.
    Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D5")) Is Nothing Then
    Application.EnableEvents = False
    Call MyMacro
    Application.EnableEvents = True
    End If
    End Sub

    Change D5 to the cell or range that should trigger the macro, and replace MyMacro with the name of the macro that imports the master list.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Triggering a macro (Excel 2000 SP3)

    One other possibility would be to put a date of last update in the worksheet containing the master rate table. Then, in the Workbook Open event routine for the Job Cost workbooks compare the date on the rate sheet in that workbook to the date in the Master Rate Table worksheet. If the dates are not the same, then ask the user if the rate table in the Job Cost workbook should be updated.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •