Posts by AJW

  • Schedule an Excel macro?

    Quote

    Originally posted by jwhitwell
    Will check that, thanks!


    I have a task under Task Scheduler that will open Excel at the right time, and a macro in my Personal Workbook that opens all of the appropriate worksheets with the appropriate actions, then closes them all down with saves - I just don't know how to kick-start that macro once Task Scheduler gets Excel open! ;)


    Will check VBA help for "On Time" - thanks!!:)


    j:-


    From above it sounds like your scheduler launches Excel. So opening Excel happen automatically ?


    If so the answer is simple and you can still use the OnTime event to trigger your code.


    All you should need to do is use the code in


    Private Sub Workbook_Open()
    Application.OnTime TimeValue("17:00:00"), "my_Procedure"
    End Sub


    This goes in VBAProject (Personal.xls) > Microsoft Excel Objects > This Workbook


    What should happen when Excel launches your Personal.xls file is loaded. When this file is loaded the code triggers due to the Workbook_Open event. The OnTime then initiates the "My Procedure" at the specified time.


    The Ontime event will not work if Excel is not open, it does not set a flag in the OS to relaunch Excel and run your macro at a given time. However if you can get your scheduler to launch Excel then you can get the Macro to run.


    Hope this helps.


    AJW

  • Updating Chart with VBA [SOLVED]

    Ivan,


    OK now I'm with you. So you get the formula from the graph i.e


    Dim strLabelA As String
    strLabelA = Application.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text


    And then use Evaluate Method to evaluate the formula within the VBE ??


    Currently I'm puling the formula out and then carrying out a series of Substitue functions to make it acceptable to Excel and then putting it into the worksheet. i.e


    strLabelA = Application.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
    strLabelB = Application.WorksheetFunction.Substitute(strLabelA, "x6", "*C6^6")
    strLabelC = Application.WorksheetFunction.Substitute(strLabelB, "x5", "*C6^5")
    strLabelD = Application.WorksheetFunction.Substitute(strLabelC, "x4", "*C6^4")
    strLabelE = Application.WorksheetFunction.Substitute(strLabelD, "x3", "*C6^3")
    strLabelF = Application.WorksheetFunction.Substitute(strLabelE, "x2", "*C6^2")
    strLabelG = Application.WorksheetFunction.Substitute(strLabelF, "x", "*C6")
    strLabelH = Application.WorksheetFunction.Substitute(strLabelG, "y ", "")
    Application.Range("Y_Axis_Calced").Formula = strLabelH


    Obvoiusly C6 refers to the X axis value to use in the formula.


    I'll need to look into the Evaluate Method a bit further to see if it can do the same but cleaner.


    Thanks


    AJW

  • Updating Chart with VBA [SOLVED]

    Quote

    Originally posted by Ivan F Moala
    Have a look @ Evaluate Method


    Hi Ivan,


    Excuse my ignorance, evalute ???


    Checked it out, it applies to Application, Chart and Worksheet object.


    I'm assuming I apply it to a range within the worksheet that contains my data ?


    And that I use some definer to tell it how to evalute the range ?


    AJW

  • Custom Schedule - Gantt Chart

    Hi All,


    Attached is a little something that I've been using for a few years now on projects.


    It's a Gantt Chart that allows you to show updates on the Forcast % Complete and also the Actual % Complete.


    So what's so great about that? you say.


    Well it's a darn sight easier to use than an un-named projects software package.


    Until yesterday it was simply a gantt chart but with some help from charting GURU Andy Pope it has been developed to show the progress also.


    I submit it in the hope some others out there can benfit from using it as I have.


    Again special thanks to Andy Pope for his invaluable assistance and expertise in charting.


    Download Now


    AJW

  • Updating Chart with VBA [SOLVED]

    Andy,


    THANKYOU ! Thankyou so very much for your help, it is greatly appreciated.


    I messed around for half the day yesterday trying to do what you have achieved. (you make it look so easy).


    I love working data through charts and graphs, I guess it's gonna take a little longer to get to your level of knowledge with them.


    On the topic of charts and graphs I have developed a little equation finder that extracts the equation for line of best fit on plotted data. (I believe there is a function in Excel that does this also but have been unable to "discover" it yet.)


    I attach it for interest of yourself and others more than anything else. perhaps you have a better (easier) way of achieving the same outcome ?


    I would be interested in your comments if you have time to review.......mmmm....kinda feels like I'm submitting my homework ;)


    Best Regards


    Tony (AJW)

  • Updating Chart with VBA [SOLVED]

    Thanks Andy,


    Appreciate it immensly when a charting guru answers........way cool.


    Attached example, it's a work in progress so please feel free to ignor the mess.


    I have removed a few things that reference my workplace.


    Regards


    Tony

  • Bubble Charts and fitted trendlines

    Can you attach an example of you workbook withthe chart or at least a picture?


    I know that you can apply a trendline directly to a bubble plot as per attached and even extract a formula for line of best fit but I think this falls short of what your chasing.


    I'm assuming that this is an accounting function? I'm mainly engineering but will help if I can.


    AJW

  • Updating Chart with VBA [SOLVED]

    Hi All,


    I'm currently using the following code to update an Excel Charts X Axis scale.


    Application.Sheets("Gantt Chart").Select
    Application.ScreenUpdating = False
    Application.Sheets("Gantt Chart").Activate
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)


    .MinimumScale = Application.Sheets("Schedule").Range("D8") - "5"
    .MaximumScale = Application.Sheets("Schedule").Range("F24") + "5"
    .MinorUnit = 1
    .MajorUnit = 7
    .Crosses = xlCustom
    .CrossesAt = Application.Sheets("Schedule").Range("D8") - "5"
    .ReversePlotOrder = False
    .ScaleType = xlLinear
    End With


    This works great with the chart as a sheet within the workbook. The problem now is that due to additional data requirements I've had to place the chart in the Schedule Sheet and then had to superimpose 2 additional charts over top with the same scaling that show updated information.


    I can get the chart numbers ie. ActiveSheet.ChartObjects("Chart 1082").Activate. But because I'm using a template each time a new workbook is created the chart numbers change.


    Ok to cut to the chase - I need a way to return the numbers of the ChartObjects in the Activesheet so that I can loop through them and apply the scaling shown above to the X Axis of each.


    I know it's possible but just can get my head around it.


    Dim ch As ChartObject
    Set ch = Worksheets("Schedule").ChartObjects.????????


    Then something with - for each ch in worksheet ??


    Thanks


    AJW

  • Spammed

    AAHHH !!


    So that's what's been happening !!!


    Couldn't figure out where these morons got my WORK email from. Now I'm on their hit lists............AAAGGGHHH.


    Thing is my email is marked as unviewable by others ???


    What's going on here ??


    AJW

  • Cannot save Data with VBA project protected {SOLVED}

    Had a similar thing once before but can't remember the exact cause or solution, it had something to do with the Workbook Properties settings.


    I'd be checking the VBA Project Properties for This Workbook just incase something was tagged True instead of False by mistake ??


    Also it could be that you have it tagged as an addin in the This Workbook Properties ?? Seem to remember something along those lines with hidden sheets ??


    Hope it helps.


    AJW

  • Excel Filename [SOLVED]

    Try these:


    Application.ActiveSheet.Range("b2").Value = ActiveWorkbook.FullName


    or


    Application.Worksheets("Sheet1").Range("A1").Value = ActiveWorkbook.FullName


    or


    MsgBox ("File Name = " & ActiveWorkbook.FullName)


    Regards


    AJW

  • File Reservation

    Hi All,


    Does anyone know if you can access the file reservation prompt box through VBA code?


    I'm opening a file from a custom menu and in doing so not getting a message that the file is currently in use by "someone else". I need to figure out how to get the "File Reservation" message to trigger when opening through code?


    Excel 97, Windows 98


    Any ideas appreciated.


    Thanks


    AJW