jfgoodhew1
New Member
- Joined
- Oct 16, 2012
- Messages
- 19
Hi All,
So I'm new to VBA and started out with the For Dummies book. I've got Excel 2010 and I'm struggling with a loop to change tab colors to color 1, 2, 3, ... of a theme. The problem is almost answered here: Colors question [Archive] - Xtreme Visual Basic Talk
My problem is I looked in Excel Help under ThemeColor property, and it says it is a Read/Write Variant.
I dimension SheetColor As Variant.
I then declare it (set its value?) using a string and the value of SheetNum (integer).
Then TypeName(SheetColor) shows String.
Then I get Type Mismatch error and the code fails.
So my desired result looks like this:
Sheet1.Tab.ThemeColor = xlThemeAccent1
Sheet2.Tab.ThemeColor = xlThemeAccent2
...
Then finally activate Sheet1 again.
I have code I wrote, which I can insert a MsgBox "OK" statement anywhere up to line ThemeColor = SheetColor.
I guess that is the line that has the problem, because SheetColor has type String, but ThemeColor requires a Variant argument.
If I use SheetColor.Value, I get requires an object (Value is a Property so doesn't fit the bill).
I found CStr which can convert anything to a string, but I didn't find an equivalent to help with converting to Variant (should be CVar).
Any help getting this to work?
So I'm new to VBA and started out with the For Dummies book. I've got Excel 2010 and I'm struggling with a loop to change tab colors to color 1, 2, 3, ... of a theme. The problem is almost answered here: Colors question [Archive] - Xtreme Visual Basic Talk
My problem is I looked in Excel Help under ThemeColor property, and it says it is a Read/Write Variant.
I dimension SheetColor As Variant.
I then declare it (set its value?) using a string and the value of SheetNum (integer).
Then TypeName(SheetColor) shows String.
Then I get Type Mismatch error and the code fails.
So my desired result looks like this:
Sheet1.Tab.ThemeColor = xlThemeAccent1
Sheet2.Tab.ThemeColor = xlThemeAccent2
...
Then finally activate Sheet1 again.
I have code I wrote, which I can insert a MsgBox "OK" statement anywhere up to line ThemeColor = SheetColor.
I guess that is the line that has the problem, because SheetColor has type String, but ThemeColor requires a Variant argument.
If I use SheetColor.Value, I get requires an object (Value is a Property so doesn't fit the bill).
I found CStr which can convert anything to a string, but I didn't find an equivalent to help with converting to Variant (should be CVar).
Code:
Private Sub TabColor()
Application.ScreenUpdating = False
Dim SheetNum As Long
Dim SheetColor As Variant
Dim SheetName As String
Dim Sht As Sheets
For SheetNum = 1 To Sheets.Count Step 1
Worksheets(SheetNum).Activate
SheetColor = "xlThemeColorAccent" & SheetNum
With ActiveWorkbook.ActiveSheet.Tab
.ThemeColor = SheetColor
End With
SheetNum = SheetNum + 1
Next SheetNum
Sheet1.Activate
Application.ScreenUpdating = True
End Sub
Any help getting this to work?