Excel Spreadsheet Macro - VBA - Application definition or object definition error (Completed)

Asked

Viewed 58 times

-1

I am creating a macro by Activex Command Button, where it will first select specific cells, each column at a time(AT2:AT15, BI2:BI15, BX2:BX15) and sort them from A to Z.

After, in these same cells, in each column, in a row will paint the background of one color (white) and the bottom line of another color (gray), that in the 14 lines. So I’ve each got white and gray, always in that order.

However, the application definition or object definition error occurred.

I am using the following macro:

Private Sub ORDENAR_Click()
'
' Macro10 Macro
' as
'
' Atalho do teclado: Ctrl+Shift+P
'
    Range("BX2:BX15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "BX2:BX15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("BX2:BX15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("BI2:BI15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "BI2:BI15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("BI2:BI15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AT2:AT15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "AT2:AT15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("AT2:AT15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range( _
        "AT2,AT4,AT6,AT8,AT10,AT12,AT14,BI14,BI12,BI10,BI8,BI6,BI4,BI2,BX2,BX4,BX6,BX8,BX10,BX12,BX14" _
        ).Select
    Range("BX14").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range( _
        "BX15,BX13,BX11,BX9,BX7,BX5,BX3,BI3,BI5,BI7,BI9,BI11,BI13,BI15,AT15,AT13,AT11,AT9,AT7,AT5,AT3" _
        ).Select
    Range("AT3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99786370433668E-02
        .PatternTintAndShade = 0
    End With
End Sub

Debugging takes me to the next line:

.Pattern = xlSolid

The error only happens when I protect the spreadsheet, ie when the spreadsheet is free the code works properly.

Someone can help me?

vba excel-vba macro

  • Here it worked as expected. I did not evaluate if it is the best form of construction. See: How to create a Minimum, Complete and Verifiable example and How to ask a good question?.

  • Ok, I believe I forgot to comment on the question, when I protect the spreadsheet that the error occurs, when the spreadsheet is unprotected the macro works normally, if I have some way to make the macro work with the protected spreadsheet, could help me...

  • Edits and complements with the details of what actually occurs in the question. I believe you will have more success in the help you seek.

1 answer

0


The error occurs due to editing protection in the spreadsheet. Below two treatment options, among others possible depending on each case:

Include unlocking step in code

You can control the lock and unlock by using the commands:

Worksheets("Planilha1").Unprotect "AlterarParaaSuaSenhaAqui"
'[...código...]
Worksheets("Planilha1").Unprotect "AlterarParaaSuaSenhaAqui"

Note that the password will be saved and can be exposed whenever someone accesses the macro code editor. Therefore, it is recommended to protect the VBA project (Tools/Vbaproject Properties/Protection).

Example applied to question code:

Private Sub ORDENAR_Click()
'
' Macro10 Macro
' as
'
' Atalho do teclado: Ctrl+Shift+P
'

    Worksheets("Planilha1").Unprotect "AlterarParaaSuaSenhaAqui"

    Range("BX2:BX15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "BX2:BX15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("BX2:BX15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("BI2:BI15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "BI2:BI15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("BI2:BI15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AT2:AT15").Select
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
        "AT2:AT15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("AT2:AT15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range( _
        "AT2,AT4,AT6,AT8,AT10,AT12,AT14,BI14,BI12,BI10,BI8,BI6,BI4,BI2,BX2,BX4,BX6,BX8,BX10,BX12,BX14" _
        ).Select
    Range("BX14").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range( _
        "BX15,BX13,BX11,BX9,BX7,BX5,BX3,BI3,BI5,BI7,BI9,BI11,BI13,BI15,AT15,AT13,AT11,AT9,AT7,AT5,AT3" _
        ).Select
    Range("AT3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99786370433668E-02
        .PatternTintAndShade = 0
    End With
    
    Worksheets("Planilha1").Protect "AlterarParaaSuaSenhaAqui"
End Sub

Allow formatting when blocking

When leaving the cells free for formatting, even with the spreadsheet locked, the code will not have problems when running and the password does not need to be stored in it. However, cells are free to edit users as well.

  1. Whole spreadsheet - You can allow cell formatting while blocking, for example:
    inserir a descrição da imagem aqui
  1. Specific cells - You can set some cells/ranges to not lock, select and Ctrl+1, under Protection.

Anyway, there are different ways to solve the error that is occurring. It is necessary to assess what applies best in the specific case, considering the advantages and disadvantages of each of the possible solutions.

Browser other questions tagged vba excel-vba macro

You are not signed in. Login or sign up in order to post.