Problem with Colors?!

[oleval]

Hi All,

I have created an Excel file, ih which I set my own colors via VBA:

ThisWorkbook.Colors(1)=RGB(15,15,15) ' something like this

It worked until I saved it as an add-in. As an add-in, the VBA ignores my commands on changing some standard colors.

How can I fix it?

Thank you!

Alex

[Bob Phillips]

Is it because you are using ThisWorkbook, so it is changing the colour palette of the addin, not the workbook being worked upon?

[oleval]

Thank you for the insight! And how to fix it?

[Bob Phillips]

Use ActiveWorkbook, not ThisWorkbook.

[oleval]

It also does not work. The matter is -- there is the following code in my xla file:

Private Sub Workbook_Open()
ActiveWorkbook.Colors(1) = RGB(100,100,100)
....
ActiveWorkbook.Colors(56) = RGB(10,200,400)
end sub

It causes a run-time error '91': Object variable or With block variable not set

[Bob Phillips]

1) there is nothing wrong with that code.

2) why would you put it in Workbook_Open, that fires when the addin is opened, which will also be the activeworkbook at that time. You need to create application events, and check each workbook being opened to see if it needs to be acted upon.

[oleval]

Could you make a hint on how to create such an event handler?

Thank you very much indeed!

[Bob Phillips]

[vba]

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'do some test if this workbook as referenced by wb is a target wb
'If target wb then
Wb.Colors(1) = RGB(15, 15, 15)
'etc
'End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[oleval]

Thanks a lot! It solves my problem.

ALex