Posts by Jong


    Have you tried using the IsNumeric() function?

    Here's a code snippet:


    Your suggestiong for Application.Caller worked.

    Here is my subroutine

    Sub OnMouseClick()
        Dim Pic As Object
        Set Pic = ActiveSheet.Shapes(Application.Caller)
    End Sub

    It actually returns the name of the picture that was clicked... works like a charm

    My worksheet contains multiple images, in which I have programatically been trying to configure a mouse click event for each.

    Here is what I have up to now:

    FName = ThisWorkbook.Path & "\Charts\" & ws.Name & ".GIF"
    Selection.OnAction = "OnMouseClick"

    My problem is, when I raise the mouse click action on the image, it calls a Sub procedure, but doesnt pass any parameters.. I'm trying to figure out how to get a "FUNCTION" to be called instead, so that I can pass a few parameters along with the mouse click.

    I'm open to new methods and suggestions,


    Here is some code that might help:

    MySheet would be the sheet name you are running the operations on.
    RowPtr is the first row you want to start checking.
    MYVALUE would be the value you want to check for. You can check for more by using the OR operator in your IF statement.

    RowPtr is only incremented if the row was not deleted. If the row was deleted, it basically goes to the next row on its own.


    Just a follow up... if you do use Andy's approach by bumping the application off the screen, or making it visible = false, you may want to use some error handling in your code.

    for example:

    Here is my problem.

    I need to find my CommandBar, and disable certain buttons in it.
    I have a CommandBar in there, but I don't know how to reference it...
    I also don't know how to disable a specific button on that command bar.

    Any help will be appreciated,

    The best way I know of to execute a command when a cell is changed is to utilize the built in events provided by excel. To do so, you can modify the Sheet object that needs to check for a change in your VBA editor.

    For example, if your sheet is called "Data", then you should edit the "Data" object in your VBA editor.

    Add the following:


    You can refer to the worksheet name by using the .Name property of the Sheet.

    for example:

    for each ws in Worksheets
    next ws

    In your case, you can get the name of Worksheet(1) by doing


    This should work for you:

    To minimize Excel you can do the following:

    Application.WindowState = xlMinimized

    To restore Excel you can do the following:

    Application.WindowState = xlNormal

    I'm not sure if your userform will remain on the screen though, give it a try and let us know.

    Welcome to OzGrid,

    If you want to be able to check constantly as the user enters data in these cells, then you will need to use formulas.

    If you rather have it that you can check the cells anytime you want, then you could use a Sub.

    In order to use formulas, you could generate a formula for each cell in question doing the following:

    Sub GenerateFormulas
         'Range, and worksheet to set formulas
         for each cell in worksheets("Sheet 1").Range("A1:Z10")
              'fill the formula for what you need
              cell.formula = "=if(value, true, false)"
         next cell
    end sub

    if you just want to check wheneer you want, you can do the following:

    Sub CheckCells
         'Range, and worksheet to check
         for each cell in worksheets("Sheet 1").Range("A1:Z10")
              if cell.value = 66 then
                     'display message
         next cell
    end sub


    well, if you think about it.. all you could do is code all the possibilities of operations, and then replace the values.

    for example:

    Function GenerateFormulas(Value1, Value2, Value3, Value4)

    Value1 + Value2 + Value3 + Value4
    Value1 + Value2 + Value3 - Value4
    Value1 + Value2 + Value3 * Value4 

    end function

    Then you would have a function call where you can use whatever values you want.

    That would still result in a lot of typing , just half though.
    Regardless, some typing will need to be done.

    i'm someone else

    I'm someone else.

    The only way I can see that this would be possible is to hard code every single possibility and then generate the results.

    If four values are used, and four operators are also used (+, -, *, /) then there would be a total of (4 x 4 x 4 x 4) + (4 x 4 x 4 x 4) permutations.

    This results in a case statement with 512 possibilities.

    Should take you the majority of the morning to finish.

    I did it again

    Sorry about this, but I actually solved my problem after posting this.

    If you set the linestyle, and the markerstyle to NONE, it will basically hide the series.

    Is it possible to Hide a series on a chart?

    What the end effect should be is that the series would be hidden, and could be brought back with the click of an option.

    I do not want to delete the series, just hide it.

    I wasn't able to find any "Visible" or "Hidden" properties associated with the Series Object.

    Any help will be appreciated,


    Regardless of the solution, this is not an easy task. If you are unfamiliar with macros, it would be quite a good time to learn :)

    What an image can do, is if you setup an IMAGE MAP over the image, when you click on certain positions (X,Y coordinates) of the image, you can raise specific events, such as displaying a Comment Box of some sort.

    This method is however useless if your charts are dynamic. If you map the image for certain x,y coordinates, and the chart were to change, you would need to re-map the entire "clickable" points.

    Other than that, I haven't been able to find any other way to capture mouse clicks on a chart. Excel has a built in mouse event when you click on a chart, and as far as i know, they cannot be overriden.

    Sorry I couldn't help out much.

    An alternative solution could be to use the cell that is linked with the chart to display the comment.


    Hey Acl,

    I had a similar problem with my current project. It seems the only way to override the mouse click even in the Chart is to create an image of the Chart, and map the image to the mouse button clicks.

    After realizing that the solution to my problem would require such a long resolve time, I choose not to do this.

    There is however a popup that will appear via Excel events when you use "Mouse Over" chart points.. It will display the Point Name, Point Value, and Point Xvalue. This is done automatically by excel..

    If you wish to convert your chart into an image, and map it, here is some code to hlep you get started. To use the following code, you must first add reference to "Microsoft Scripting Runtime" in Tools > References:

    The above code will generate a GIF of your chart, and save it in /Charts directory where your workbook resides on your HD.