One VBA disables another, how to correct

[cmm0812]

I have run the macros (copied from the kbase):

1) Comments - to enter a comment into any cell by double-clicking
2) Protect entire worksheet at once with a password

The problem that I am having is that since having run the workbook protection macro, the comment macro has been disabled. Upon double-clicking on any cell, a dialogue box comes up that will allow for a comment to be entered, but once typed into the dialogue box and enter is pressed, the comment does not display in the cell.

Is there an easy way to correct this problem?

[Bob Phillips]

Show us your code and we might be able to tell.

[cmm0812]

Comments

'Code to be placed in the worksheet(s) you want to use this in
Option Explicit
Public oldRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With

Set oldRange = Target(1, 1)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
End If

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub


Password protect

Option Explicit

Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd
Next wSheet

End Sub

Sub UnProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
If Err <> 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error Goto 0

End Sub

[malik641]

This sounds like my KB entry (http://vbaexpress.com/kb/getarticle.php?kb_id=893). You have to modify the Comment Code to check for worksheet protection BEFORE it runs, then have it behave accordingly.

[cmm0812]

I have tried correcting the code but need some help, as I still cannot get it to show comments once they have been typed (I am new at writing VBAs, as you can tell). I also think I have the user interface bit incorrect. I do not want to have the protection disabled while imputting a comment in. I want to have the protection running continuously through the comment adding process, and just allowing for comments as an option, if possible.


Option Explicit
Public oldRange As Range

Private Function wbookSheetProtection(WorkbookTarget As Workbook) As Boolean
Dim wsheet As Worksheet
For Each ws In wbookTarget.Worksheets
If wsheet.ProtectContents = True Then
.Comment.Visible = True
.Protect Password:="YourPassword", UserInterFaceOnly:=True
Else
.Comment.Visible = True
Exit Function
End If
Next ws

End Function
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)
'I wasn't sure if I should add a command in here to have this code recognize the previous function
oldRange.Comment.Visible = False
With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With
Set oldRange = Target(1, 1)
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String
If Target.Comment Is Nothing Then
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True
End If
Cancel = True
End Sub