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. — , Dec 28 '15 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](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. — , Dec 28 '15 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"). — , Dec 28 '15 at 16:51
@Ta10n This is normal. Apply Jeeped's advice to remove the error. — A.S.H, Dec 28 '15 at 16:53
Perhaps you could add some narrative describing what you want to do and when you want to do it. — , Dec 28 '15 at 17:07
I'm not sure really. I did not write the code and I do not have much VBA knowledge. I was handed this ticket and asked to find a solution. I know in the document there is a blank dropdown that says, "If this SIG will be a master SIG to be used with the SMT, select Master below. If this SIG will be distributed leave blank." If you choose Master it starts to give these errors. — Ta10n, Dec 28 '15 at 17:17
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. — tigeravatar, Dec 28 '15 at 17:26

score 1 · Accepted Answer · edited May 23 '17 at 12:15

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.}

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. — tigeravatar, Dec 28 '15 at 17:26
@Jeeped That got rid of the error. Will removing that line negatively impact the functionality? — Ta10n, Dec 28 '15 at 19:36

Errors from Excel Macro coding

1 Answers1