Macro to display Comments

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have code below that where D19 on sheet "Depreciation" has a value that is not zero, then a comment must be inserted/displayed in cell D22 on sheet "Recon"

I get a run time error "end if without block if"

It would be appreciated if someone could amend my code



Code:
 Sub Comments()
With Sheets("Depreciation")
If .Range("D19").Value <> 0 Then
  Sheets("Imported Data").Range("D22").ClearComments
    Exit Sub
Else:
With Sheets("Recon")
.Range("D22").ClearComments
.Range("D22").AddComment.Comment.Visible = True
    
  .Comment.Text Text:="Depreciation not yet processed"""
  .Comment.Shape.Select True
   Selection.ShapeRange.IncrementLeft -65#
   Selection.ShapeRange.IncrementTop 40#
 
   Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
      
   Selection.ShapeRange.ScaleWidth 0.95, msoFalse, msoScaleFromBottomRight
      End If
End With
End With
end Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi howard

Good example why indenting code is useful. Try this:

VBA Code:
Option Explicit
Sub Comments()
    With Sheets("Depreciation")
        If .Range("D19").Value <> 0 Then
            Sheets("Imported Data").Range("D22").ClearComments
            Exit Sub
        Else
            With Sheets("Recon")
                .Range("D22").ClearComments
                .Range("D22").AddComment.Comment.Visible = True                   
                .Comment.Text Text:="Depreciation not yet processed"""
                .Comment.Shape.Select True
                Selection.ShapeRange.IncrementLeft -65#
                Selection.ShapeRange.IncrementTop 40#
                Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
                Selection.ShapeRange.ScaleWidth 0.95, msoFalse, msoScaleFromBottomRight
            End With
        End If
    End With
End Sub

Robert
 
Upvote 0
Thanks for your advise and help

I now get a run time error "object doesn't support this property or method" and the code below is highlighted



Code:
 .Range("D22").AddComment.Comment.Visible = True
 
Upvote 0
Try this:

VBA Code:
Option Explicit
Sub Comments()
    With Sheets("Depreciation")
        If .Range("D19").Value <> 0 Then
            Sheets("Imported Data").Range("D22").ClearComments
            Exit Sub
        Else
            With Sheets("Recon").Range("D22")
                .ClearComments
                .AddComment
                .Comment.Visible = True
                .Comment.Text Text:="Depreciation not yet processed"
                .Comment.Shape.Select True
                With Selection
                    .ShapeRange.IncrementLeft -65#
                    .ShapeRange.IncrementTop 40#
                    .ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
                    .ShapeRange.ScaleWidth 0.95, msoFalse, msoScaleFromBottomRight
                End With
            End With
        End If
    End With
End Sub
 
Upvote 0
Thanks for the help Robert. Code works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top