Posts by Jong


    Im sorry I wasn't clear enough.

    What I want to try and figure out is how to remove cell references from a RANGE OBJECT.

    By this I mean,

    -I have a Range type object that holds for example ("A1:A10")
    -I want to remove "A3" from that Range object, and not the spreadsheet
    -This will result in a Range with two areas, ("A1:A2" and "A4:A10")

    The spreadsheet is unafected, just the range object.

    Is there a way to check if a specific range is within another range?

    IE> Range1 = A1:A10
    Range2 = A5

    -> Check to see if Range2 exists in Range1

    I'm wondering if there is a function/method that exists in the Range class.

    neat code

    I actually ended up writing something that is pretty neat. You can define any type of error message you want from this code when the user modifies any range you specify. This is put in the "worksheet" object that needs to be checked against illegal range editing.

    What is the best way to specify a range of cells that cannot be modified by the user?

    I have a worksheet with a specific range of cells that i want to remain read-only.

    my method

    This is how I do what you have asked in my current project:


    This is possible, yet it is not a simple task for a beginner.

    HEre is the basis of how to do it:

    ActiveChart.SeriesCollection(1).points(25).ApplyDataLabels AutoText:=True, _
            LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
            ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False

    The above code will format the First series, Point number 25 to show data labels. Now, lets say you wanted to do a check for each point, and set the label. Here is a better approach:

    I stumbled upon this while searching for icons to use for my custom macro toolbar. Its a site that contains all the FaceID values of all the icons you can use in Excel.


    Hope someone else will find this as useful as me.


    I think these custom shapes shouldn't be used for charts like the ones I'm making. Since my charts are so dynamic, its creating too many problems.

    I'm going to try and figure out a better color coding system instead. Thanks for all the help.



    If you mean to center it so the text appears from top to bottom:


    Then, you will have to code your own class to handle the drawing on the screen of the text.

    If you mean to center it in the TextBox itself, then this is quite simple, you can just do:

    TextBox.TextAlign = fmTextAlignCenter


    Okay, I managed to get my Points to display the custom markers.... NOW I have a bigger problem.

    It seems once the points are set to custom, they cannot be converted back to a regular point.

    Has anyone run into this problem before? I need to convert a custom marker back to a regular marker.

    How can you specify the color Orange for the Interior color of a cell?

    Iv'e tried with .Interior.Color = RGB(255, 153, 0)
    This resulted in a Yellow color

    Iv'e tried with .Interior.ColorIndex = 45 and 44
    this resulted in a brownish color

    Anyone else has had this problem?


    Shortly after posting, I have found the solution.

    ActiveWorkbook.Colors(45) = RGB(255, 153, 0)

    This will set the 45th color of the 56 color palette to Orange, the color it should have been in the first place.


    Thanks Andy,

    I have solved the problem using your above mentioned suggestions. I would post my final code, although it is customized for my app, and it would be worthless for anyone else.

    Anyways, Thanks again,

    macro recorder

    I recorded a macro to change a single Point with a custom shape, and this is what I got:

    ActiveSheet.Shapes.AddShape(msoShape4pointStar, 261.75, 12.75, 20.25, 19.5).Select
        Selection.ShapeRange.Fill.Visible = msoTrue
        Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 153, 0)
        ActiveSheet.ChartObjects("Chart 1").Activate

    It somewhat does what I need, but it kinda looks messy, creating and deleting the shapes like that. I don't think I want to use this.

    single point

    Do you know if there would be a way to change a single point on the chart with a custom shape?

    For example,

    With ActiveChart.SeriesCollection(1)
        .points(iPts).MarkerStyle = xlTriangle

    Code was extremely trimmed, just to show. But basically, I go through my series one Point at a time, and check against various things. I need to be able to flag a single point as shown above.

    Now, I understand MarkerStyle is just a integer representation of a shape, would there be any values that are not displayed that could be used ?

    Easy way is not an options

    This is not an option in my situation. I need to programatically create this marker since this workbook will be sitting on multiple PCs when i'm done with it.

    My charts are all created dynamically, and there is no fix number of charts for each analysis that will be done using this workbook.

    My main problem is, i need to flag so many thigns in the charts, that I'm running out of markers. (I'm working for chemists, they have quite a few things to check for in the charts)

    If anyone can explain to me how to programatically insert a custom marker, this would be my goal. It doesn't matter if it is complicated, I have lots of time on my hands.

    Can anyone tell me how to create a custom data point in a chart? My version of excel only allows me to use rectangles/triangles/circles/etc...

    I was wondering if it would be possible to create my own shape, such as a "Plus sign" to incorporate in my charts.

    If anyone can help on this subject, please do :)

    Thanks in advance,