asked on

VB.net Excel Add-in Property Color is ReadOnly

Hi

In my VB.net Excel Add-in I am trying to fill a chart with a two color gradient
I get the Error 'Propert Color is ReadOnly ' at the two lines marked error

   Sub oFill_Chart()
        Dim cht As Object
        cht = Globals.ThisAddIn.Application.ActiveChart.Parent


        Dim oFill As Excel.FillFormat
        oFill = Globals.ThisAddIn.Application.ActiveSheet.Fill


        With oFill
            .TwoColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 1)
            With .GradientStops(1)
                .Color = RGB(234, 122, 17) 'ERROR

                .Position = 0
                .Transparency = 0
            End With
            With .GradientStops(2)
                .Color = RGB(55, 255, 122) 'ERROR
                .Position = 1
                .Transparency = 0
            End With
            .GradientStops.Insert(RGB:=RGB(128, 122, 201), Position:=0.5, Transparency:=0)
            .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
        End With

    End Sub

Open in new window

AndyAinscow

Is the chart (sheet, workbook) opened or locked as readonly ?

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nasir Razzaq

Does changing

.Color = RGB(234, 122, 17) 'ERROR

to

.ForeColor.RGB = RGB(234, 122, 17)

work?

AndyAinscow

Just noticed something - you talk of modifying the chart BUT use this code which is the sheet not the chart:

        Dim oFill As Excel.FillFormat
        oFill = Globals.ThisAddIn.Application.ActiveSheet.Fill

Open in new window

Should it not be something like this instead:
oFill = Globals.ThisAddIn.Application.ActiveSheet.ActiveChart.Fill
(or another possibility: oFill = Globals.ThisAddIn.Application.ActiveSheet.Shapes(1).Fill )

Murray Brown

ASKER

Hi Andy. Thanks for spotting that and CodeCruiser that unfortunately doesn't work

Professor J

did you try

Sub test()
Dim oFill As FillFormat
    Set oFill = ActiveSheet.Shapes(1).Fill
    With oFill
        .TwoColorGradient msoGradientHorizontal, 1
        With .GradientStops(1)
            .Color = RGB(234, 122, 17)
            .Position = 0
            .Transparency = 0
        End With
        With .GradientStops(2)
            .Color = RGB(55, 255, 122)
            .Position = 1
            .Transparency = 0
        End With
        .GradientStops.Insert RGB:=RGB(128, 122, 201), Position:=0.5, Transparency:=0
        .Visible = msoTrue
    End With

End Sub

Open in new window

AndyAinscow

Back to my original posting:
Is the chart (sheet, workbook) opened or locked as readonly ?

( Instead of having that code in your add in what happens if you add a macro to the excel workbook directly and run the code in that ? )

Murray Brown

ASKER

Hi
I think the main issue is that this is VB.net not VBA. The code works in VBA but not VB.net

excelismagic

i am not very good with .net language

Here's an article that discusses migrating VBA to VB.NET:
http://msdn.microsoft.com/en-us/library/aa192490%28v=office.11%29.aspx

>> Non-compliant content removed.
>> kaufmed, Topic Advisor

Murray Brown

ASKER

Thanks