Changing from message box to insertion of text into cells

[ardykav]

Hi,

Thanks to PGC01 for the below code to show where page breaks are on a sheet. I was just looking to edit the code so that rather than the message box appearing it actually puts the text in the row itself. Something like "There is a page break on this row" in column A would do the trick I think.

Sub pagebreaklook()
Dim hpbs As HPageBreaks
Dim j As Long

Set hpbs = ActiveSheet.HPageBreaks
If hpbs.Count = 0 Then
MsgBox "No horizontal page breaks in active sheet"
Else
For j = 1 To hpbs.Count
MsgBox "Horizontal page break " & j & " at row: " & hpbs(j).Location.Row
Next j
End If
End Sub

 

[DanteAmor]

How about

Sub PageBreakText()
  Dim h As HPageBreak
  For Each h In ActiveSheet.HPageBreaks
    Range("A" & h.Location.Row).Value = "There is a page break on this row"
  Next
End Sub

 

[ardykav]

Absolutely perfect, thanks so much

How about

Sub pagebreaklook()
  Dim hpbs As HPageBreaks
  Dim j As Long
 
  Set hpbs = ActiveSheet.HPageBreaks
  If hpbs.Count = 0 Then
    MsgBox "No horizontal page breaks in active sheet"
  Else
    For j = 1 To hpbs.Count
      Range("A" & hpbs(j).Location.Row).Value = "There is a page break on this row"
    Next j
  End If
End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.