Posts by Jong

    it would be

    AVERAGE(B1, B2, B3, B4, B5) 'etc..

    Basically, you just need the cell name to reference the values.

    Alternatively, if it resides in another sheet:

    AVERAGE(Sheet!B1, Sheet!B2) 'etc..

    Each bar on a chart corresponds to a series.

    To do what you are trying to achieve, you may be able to incorporate some of the below code:

    Please note, this code is untested, just trying to point you into the right direction

    in order for VBA to read the " character, you will need to use:


    for example:

    MsgBox "The " & Chr(34) & " character has been printed"

    Also, if your unsure what you final formula will actually look like, try doing a MSGBOX of the formula your setting, and it will show you exactly what is wrong with it

    I never encountered this, although do you need to use the Shell to copy folders?

    If not, you could also try using "Microsoft Scripting Runtime"

    To do so, in your VBA editor, click" Tools > References" and add the reference for "Microsoft Scripting Runtime"

    Now you can do the following:

    Dim mySystemObject As New FileSystemObject
    Call mySystemObject.CopyFolder("\\Server1\setup\tools", "\\Server2\Tools")

    I had a similar problem, and to resolve this issue, I did the following:

    Whenever a cell is found blank, set the formula of that cell to "=NA()"

    A chart will not plot anything if the cell contains #N/A

    The problem for macros is related to the users who will be using my software.

    Let's face it, not all users are good computer users. Now when the user wants to open a new workbook, he is going to click "File>OPEN" and then it will prompt to Enable macros, which they will probably do, and then all hell breaks loose :)

    So, there is my problem.

    I've also thought about not allowing a new workbook to open, but that would just cause total chaos.

    I'm trying to come up with a solution, if anyone else has other suggestions, im open ears.

    There is another problem with that above code. Whenever you try to close the active workbook, its opens another copy of itself. When you try to close the copy, it does the same. It just keeps spawning copies.

    This is a great help steve

    Although, there is still a problem.
    When the macros of the new workbook get activated, they are also copied into the old workbook.

    Any suggestions as to how to get the macros to only open in their respective workbook?

    Okay, I've managed to create a new instance of Excel, and to create a workbook in that instance. I still haven't figured out how to load my custom workbook in that new instance of excel.

    I have the pathname to the workbook.

    Any help will be appreciated.

    Here is my code:

    Dim objExcel As Excel.Application
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True

    Can anyone explain to me how to open a new instance of excel using VBA code?

    What I am trying to achieve is, IF a user tries to open a new workbook in the existing instance of Excel, the application will open a new instance of Excel, and open the workbook at that location. Up to now, I have the following:

    Public WithEvents App As Application
    Private Sub App_NewWorkbook(ByVal Wb As Workbook)
        'Code should go here
    End Sub

    The above code is the event handler for when a new workbook is opened. I need to somehow stop the workbook from opening in my current workbook, and open it in a new instance of excel instead.

    Any suggestions will help.

    all custom commandbars are created directly in my VBA code... As a matter of fact, nothing is created by hand in this particular application. Everything is dynamically creating via VBA code.

    When certain people choose "Save-as" on the workbook I made, and save it as a different name, when they try to run a macro right afterwards, it opens the previous workbook (the old name), and runs the macro in the "old" workbook.

    I tried to reproduce the problem on my computer, and I cannot. Although, I have been to the users computers, and the problem persists.

    Has anyone encountered this problem in the past?
    Any suggestions as to what I could try to do to fix it?

    It seems the problem is originating from the MACRO COMMAND BAR that i have setup. The command bar doesn't use the current workbook, but the old one instead.

    Can someone tell me how to edit the text displayed in the column headers (A - ZZ) and the text displayed in the Row headers (1-6553?)

    Basically, I have done this in the past, and i do not remember how to do it. I am trying to figure out how to get the text in these sections to be smaller. I'm not sure how to explain what I am trying to achieve.

    Let me know if i need to explain further.

    Again, i overlook things. I managed to figure it out:

    If mFileSysObj.folderexists(ThisWorkbook.Path & "\Help\") = False Then
            Call mFileSysObj.CreateFolder(ThisWorkbook.Path & "\Help\")
        End If

    How do you create a new directory in VBA code?

    I want to be able to check if the directory exists, and if it doesn't to create it.

    ThisWorkbook.Path & "\Help" is an example of what needs to be created if it does not yet exist