unprotect macro won't run!

WayeB

New Member
Joined
Feb 9, 2018
Messages
5
Hi All,

First of all thank you for you're help, your about to cure my head ache

I have this running (see below) and I am trying to run the macro but it's telling me I need to unprotect the sheet, but it's not protected, im not an expert when it comes to all this but it has been working fine.

can some one help me with this pleeease

I can't add the screen shot for some reason


Dim thisfile As Workbook
Set thisfile = ActiveWorkbook
Dim MyWorkbook As Workbook
Dim MySheet As Worksheet
Dim MyComment As Comment
Dim CommentCount As Long
Dim lArea As Long
Dim fixed As Boolean
fixed = False
On Error GoTo NeedToUnprotect
For Each MyWorkbook In Workbooks
MyWorkbook.Activate
For Each MySheet In MyWorkbook.Sheets
MySheet.Activate
CommentCount = 0
For Each MyComment In MySheet.Comments
With MyComment.Shape
.Placement = xlMoveAndSize
.Top = MyComment.Parent.Top + 5
.Left = MyComment.Parent.Offset(0, 1).Left + 5
.TextFrame.Characters.Font.Name = "Tahoma"
.TextFrame.Characters.Font.Size = 8
.TextFrame.AutoSize = True
CommentCount = CommentCount + 1
End With
If MyComment.Shape.Width > 300 Then
lArea = MyComment.Shape.Width * MyComment.Shape.Height
MyComment.Shape.Width = 200
MyComment.Shape.Height = (lArea / 200) * 1.1
End If
Next MyComment
If CommentCount > 0 Then
MsgBox ("A total of " & CommentCount & " comments in worksheet '" & MySheet.Name & "' of workbook '" & MyWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")
fixed = True
End If
Next MySheet
Next MyWorkbook
thisfile.Activate
If fixed = False Then
MsgBox ("No comments were detected.")
End If
On Error GoTo 0
Exit Sub

NeedToUnprotect:
MsgBox ("You must unprotect all worksheets before running the macro.")
thisfile.Activate
Exit Sub

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
It ran for me with and without comments.

If it's using YOUR msgbox to tell you that you need to unprotect the sheet, all it means is it encountered some error, not necessarily a protected sheet.

Create a new workbook and try it and see what happens.
 
Upvote 0
also when I move comments or change font color, it ALWAYS goes back to back and not the specified color I wanted
 
Upvote 0
Remove the error checking line (the line making the code go to the end and give you that message box)

Like jproffer said, all we know is your code is running into an error, and that error makes it go to the end and give you the message box.
But we don't know what the error actually is, or which line is causing it.
There could be infinite other reasons for an error, not related to a sheet being protected

Remove this line (as a troubleshooting step, not as a solution)
On Error GoTo NeedToUnprotect

Then run the code again and report back with the actual error, and which line caused it.
 
Upvote 0
I really appreciate this guys

TextFrame.Characters.Font.Name = "Tahoma" was highlighted I yellow
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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