Errors from Excel Macro coding

Question

I have inherited a help ticket to solve error issues within an Excel Macro document. I receive the error "Runtime Error 438: Object doesn't support this property or method", when the first code group listed below is run. After making a small syntax change I receive the error "Compile error: Next without For", when the second code group listed below is run. I have tried a few possibly fixes but I cannot shake these errors. Any help is appreciated.

Private Sub Worksheet_Change(ByVal target As Range)
Dim TabNum As Long

' Check all automation boxes on all of the tabs when Master is selected
If Range("Master") = "Master" Then
    For TabNum = 7 To 23 Step 1 'thisworkbook.Sheets.Count is the number of tabs in the open master SIG
        ThisWorkbook.Worksheets(TabNum).Select
        If ThisWorkbook.Worksheets(TabNum).CheckBox1.Value = False Then ThisWorkbook.Worksheets(TabNum).CheckBox1.Value = True
    Next TabNum
End If

' move back to the formula notes worksheet
ThisWorkbook.Worksheets(27).Select

End Sub

Error 438

Private Sub Worksheet_Change(ByVal target As Range)
Dim TabNum As Long

' Check all automation boxes on all of the tabs when Master is selected
If Range("Master") = "Master" Then
    For TabNum = 7 To 23 Step 1 'thisworkbook.Sheets.Count is the number of tabs in the open master SIG
        ThisWorkbook.Worksheets(TabNum).Select
        If ThisWorkbook.Worksheets(TabNum).CheckBox1.Value = False Then
        ThisWorkbook.Worksheets(TabNum).CheckBox1.Value = True
    Next TabNum
End If

' move back to the formula notes worksheet
ThisWorkbook.Worksheets(27).Select

End Sub

Error Next without For

Why bother even checking to see what state the checkbox is in? Just set it to True. — user4039065
– user4039065, Commented Dec 28, 2015 at 16:46
You shouldn't be selecting worksheets from within a Worksheet_Change event macro. See How to avoid using Select in Excel VBA macros for methods on getting away from relying on select and activate to accomplish your goals.</sub> — user4039065
– user4039065, Commented Dec 28, 2015 at 16:48
The phrase '... when Master is selected' seems to indicate to me that this should be a Worksheet_SelectionChange, not a Worksheet_Change and you should be comparing Target, not Range("Master"). — user4039065
– user4039065, Commented Dec 28, 2015 at 16:51

Community · Accepted Answer · 2017-05-23 12:15:21Z

1

From your brief description, it seems that you want to cycle through the worksheets in the queue (from position 7 to 23, inclusive) and set Checkbox1 to True on each of these worksheets. You want this triggered '... when Master is selected' (not changed) so a Worksheet_SelectionChange event macro is more appropriate than a Worksheet_Change.

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Dim tabNum As Long

    ' Check all automation boxes on all of the tabs when Master is selected
    If LCase(target(1).Value2) = "master" Then
        For TabNum = 7 To 23 Step 1 'thisworkbook.Sheets.Count is the number of tabs in the open master SIG
            ThisWorkbook.Worksheets(tabNum).CheckBox1.Value = True
        Next tabNum
    End If

End Sub

Note that there is much more code removed¹ than was added or edited.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 28, 2015 at 17:19

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

tigeravatar Over a year ago

The 438 error happens specifically when the control the code is looking for doesn't exist. In this case, it could be that one (or more) of those worksheets doesn't have an ActiveX Checkbox control named CheckBox1. Perhaps putting in a check to see if that control exists would eliminate the error.

Ta10n Over a year ago

@Jeeped That got rid of the error. Will removing that line negatively impact the functionality?

user4039065 Over a year ago

@Ta10n - No, it will not.

Collectives™ on Stack Overflow

Errors from Excel Macro coding

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related