Looping through sheets to change sheet not working

[mslosar]

Can anyone tell me why this rewrites the values on page 1 twice, moves to page 2, but does nothing?

'Set new Drawing Number
    Dim oMTRNum As String
    Dim oMTRInt As Integer
    
    For Each oSheet In oDoc.Sheets
       oSheet.Activate
       
        For Each oTextBox In oTB.Definition.Sketch.TextBoxes
            If GetPromptField(oTextBox.FormattedText) = "DRAWING_NO" Then
                    MsgBox "title written"
                    Call oTB.SetPromptResultText(oTextBox, strDN)
                Exit For
            End If
        Next
        
        'reset revision to 0
        For Each oTextBox In oTB.Definition.Sketch.TextBoxes
            If GetPromptField(oTextBox.FormattedText) = "REV_NO" Then
            MsgBox "Rev written"
                    Call oTB.SetPromptResultText(oTextBox, "1")
                Exit For
            End If
        Next
    next

 I have a feeling it has to do with referring to activesheet. But i'm unsure how to reference the oTB.Definition.Sketch.TextBoxes portion for current sheet.  I know that oDoc.Sheets(1).TitleBlock.Definition.Sketch.TextBoxes address just sheet 1...just not sure how to make it deal with the current sheet.

Thanks

[VdVeek]

In your code snippet i'm missing the declaration of oTB for the active sheet.

Something like this:

....... 
    For Each oSheet In oDoc.Sheets
       oSheet.Activate
       oTB = oSheet.TitleBlock 
        For Each oTextBox In oTB.Definition.Sketch.TextBoxes
.......

Sorry but i can't test this at the moment, but i think it has to be something like this.

Rob.

[mslosar]

Me too 🙂

oTB is (IIRC) oDoc.ActiveSheet - which is part of the problem that it keeps using active sheet instea of the current sheet.

I believe it worked it out with .sheet.titleblock.definition.sketch.textboxes

I defined a new sheet collection as well. The combination seems to have sorted it.