Recording macro gives Run-time error '9' Subscript out of range when it is runned (bug in recording macro)

I want to copy an existing sheet, rename it, and make the tab color of the new sheet "No Color".  I've recorded a macro to do this, but when I run it, it errors.  Run-time error '9'  Subscript out of range.  Debug highlights this line:      .ColorIndex = xlAutomatic

The macro runs fine when this line is changed manually to:
 .ColorIndex = xlColorIndexNone

***Post moved by the moderator to the appropriate forum category.*** 

I suggest that you have identified a bug but if you want to use xlAutomatic then use .Color in lieu of ColorIndex like the following.

    Sheets("Sheet1").Copy Before:=Sheets(1)
    ActiveSheet.Name = "Copy of Sheet1"
    With ActiveSheet.Tab
        .Color = xlAutomatic    'Use in lieu of next line
        '.ColorIndex = xlAutomatic  'Does not work
        .TintAndShade = 0
    End With

Regards,

OssieMac

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Yes, it is a bug in recording a macro. This bug exists in all Excel versions. Can someone pass it to the relevant engineer to fix this.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

take a look here...

https://msdn.microsoft.com/VBA/Excel-VBA/articles/worksheet-tab-property-excel


also...

Sub format_tab()
'Tab.Color / Tab.ColorIndex / Tab.ThemeColor property //  Tab.Name property
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
.Tab.ColorIndex = xlNone
MsgBox "no color"
.Tab.Color = vbRed
MsgBox "red color"
.Tab.ThemeColor = xlThemeColorAccent1
MsgBox "theme color Accent1"
.Tab.ColorIndex = xlNone
MsgBox "no color"
End With
End Sub

note

 in tab property

you can't use

1) underline property

2) bold property


-----------------------------
Office 365 on Windows 10

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

In the example on the MSDN website xlColorIndexNone is used. VBA does not record it correctly, when it uses xlAutomatic

Running the macro with xlAutomatic gives an error. When one changes xlAutomatic into xlColorIndexNone it runs fine. It is clearly a bug in VBA recording.

Sub Macro1()
    Sheets("Sheet1").Select
    With ActiveWorkbook.Sheets("Sheet1").Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End Sub

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

right click on sheet tab

you can't have the xlAutomatic property

(but the 'no color' option  - xlNone / xlColorIndexNone)

so,

for what reason this is a bug ?

-----------------------------
Office 365 on Windows 10

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

right click on sheet tab

you can't have the xlAutomatic property

(but the 'no color' option  - xlNone / xlColorIndexNone)

so,

for what reason this is a bug ?

Try recording the macro as per the OP's original post and you will see that it records it as .ColorIndex = xlAutomatic as per the example below.

If it cannot record the code correctly so that the recorded code can be run without error then it is obviously a bug in macro recording.

I replied previously that xlAutomatic can be used with Color instead of ColorIndex and the code will run but having to edit the code because it did not record correctly is still a bug in the recording process. 

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet1").Select
    With ActiveWorkbook.Sheets("Sheet1").Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End Sub

Regards,

OssieMac

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Okay OssieMac, thank you.

-----------------------------
Office 365 on Windows 10

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

OssieMac,

Thanks for confirming the bug.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated January 8, 2019 Views 353 Applies to: