Geek Answers Handbook

VBA msoThemeColor store in variable

I am looking to save the colors of the mso theme in a variable so that the colors and patterns of the chart can change dynamically (Th variable in this situation). Here is my current code:

Dim Th As Long Th = "msoThemeColorAccent" & ActiveCell.Offset(-5, 0) If ActiveCell = "Realized" Then ActiveChart.SeriesCollection(Srs).Select With Selection.Format.Fill .ForeColor.ObjectThemeColor = Th .Solid End With With Selection.Format.Line .ForeColor.ObjectThemeColor = Th End With End If

I think the problem is that I am not using the correct categorization of Dim. I saw questions about storing RGB as variables here (using Dim Long seemed to be the solution), but so far nothing has been found on msoThemeColors. Thanks and let me know if I can provide any other details!

+5
source share
2 answers

Since msoThemeColorAccent is an enumeration, the restored code will be used below.

 Dim lThemeColor As Long lThemeColor = ActiveCell.Offset(-5, 0) + 4 'msoThemeColor1 enum is 5, 2 is 6, 3 is 7 ... so add 4 to the value If ActiveCell = "Realized" Then With ActiveChart.SeriesCollection(Srs).Format With .Fill .ForeColor.ObjectThemeColor = lThemeColor .Solid End With With .Line .ForeColor.ObjectThemeColor = lThemeColor End With End With End If
+5
source

It's hard to say what happens because your code is highly dependent on the default members. Anyway:

 Dim Th As Long

It is right.

 Dim themeColorIndex As MsoThemeColorIndex

This is correct and explicit (see MsoThemeColorIndex on MSDN).

As Scott Holtzman said , the possible values ​​here are Enum values, not strings: you simply cannot create the correct value by combining the strings into one of the defined Enum .

So by doing this:

 Th = "msoThemeColorAccent" & ActiveCell.Offset(-5, 0)

I assume that ActiveCell.Offset(-5, 0) should contain a number from 1 to 6. This is a dangerous assumption: ActiveCell can be literally anything. If this value is in a specific cell, refer to it at its address:

 themeColorIndex = Sheet1.Range("B12").Value

This is slightly better, but it is still assumed that the value in B12 can be implicitly converted to an integer Long .

 Dim selectedValue As Variant selectedValue = Sheet1.Range("B12").Value If Not IsNumeric(selectedValue) Then MsgBox "Invalid value!" Exit Sub End If If Sheet1.Range("E12").Value <> "Realized" Then Exit Sub Dim themeColorIndex As MsoThemeColorIndex themeColorIndex = selectedValue

If you are only interested in msoThemeColorAccent1 through msoThemeColorAccent6 , then you want B12 contain the values 5 through 10 , which are the base enum values ​​you are looking for.

If your worksheet should have a value of 1 through 6 for usability reasons, you can do this:

 Dim themeColorIndex As MsoThemeColorIndex 'msoThemeColorAccent1 underlying value is 5, so we add 4 to the value: themeColorIndex = selectedValue + 4

Then you make another dangerous assumption: you assume that there is an active diagram! Assuming a chart exists in Sheet1 , you'd better access it, again, explicitly:

 Dim theChart As ChartObject Set theChart = Sheet1.ChartObjects(1) With theChart.SeriesCollection(srs) 'whatever srs means With .Format .Fill.ForeColor.ObjectThemeColor = themeColorIndex .Fill.Solid .Line.ForeColor.ObjectThemeColor = themeColorIndex End With End With
+8
source

More articles:


All Articles