Insert Comment depending on cell value

[thedobbs]

I would like to insert a comment depending on the value in cell S6. I have a macro that works when the values in the worksheet change, However I can not use this as it conflicts with other macros.
Can anybody convert this to a simple macro where it adds the comment on command rather than automatically.

Please find the macro below:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("S6").Select
Selection.ClearComments
Dim cmt As Comment
Dim Ans As String
If Not Intersect(Target, Range("S6")) Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
Select Case UCase(.Value)
Case "CC4XN3": Ans = "Cable - XLPE 400kV"
Case "CC2XN3": Ans = "Cable - XLPE 275kV"
Case "CC1XN1": Ans = "Cable - XLPE - 1x1600 - (km) 132kV "
Case "CC2XN4": Ans = "Cable - XLPE - 1x2000 - (km) 275kV "
Case "CC4OR1": Ans = "Cable - Oil 400kV"
Case "CC4OR3": Ans = "Cable - Oil - 1x2500 - (km) - 400kV"
Case "CC2OR3": Ans = "Cable - Oil 275kV"
Case "CC4XT1": Ans = "SGT Cable 240MVA - (km) - 400kV"
Case "CC4XT2": Ans = "SGT Cable - 400kV"
Case "CC2XT2": Ans = "SGT Cable - 275kV"
Case "CC6XT1": Ans = "SGT Cable - 66kV"
Case "CC3XT1": Ans = "SGT Cable - 33kV"
Case "CC2XT1": Ans = "SGT Cable 240MVA - (km) - 275kV"
Case "CC1XT1": Ans = "SGT Cable 240MVA - (km) - 132kV"
Case "CC6XN1": Ans = "Railway Feeder cable 80MVA - (km) - 72kV"
Case "CC6XN1": Ans = "Railway Feeder cable 40MVA - (km) - 72kV"
Case "CC4XA1": Ans = "Aluminium cable"
Case "CA4XC1": Ans = "Cable Sealing Ends (Set) - XLPE 400kV"
Case "CA2XC1": Ans = "Cable Sealing Ends (Set) - XLPE - 275kV"
Case "CA1XC1": Ans = "Cable Sealings Ends (Set) - XLPE -132kV"
Case "CA4OC1": Ans = "Cable Sealing Ends (Set) - Oil - 400kV"
Case "CA2OC1": Ans = "Cable Sealing Ends (Set) - Oil - 275kV"
Case "CA1OC1": Ans = "Cable Sealing Ends (Set) - Oil - 132kV"
Case "CA4XT1": Ans = "Cable Straight Joint - XLPE - 400kV"
Case "CA2XT1": Ans = "Cable Straight Joint - XLPE - 275kV"
Case "CA1XT1": Ans = "Cable Straight Joint - XLPE - 132kV"
Case "CA4OT1": Ans = "Cable Straight Joint - Oil - 400kV"
Case "CA2OT1": Ans = "Cable Straight Joint - Oil - 275kV"
Case "CA4OS1": Ans = "Cable Stop Joint + Bay - 400kV"
Case "CA2OS1": Ans = "Cable Stop Joint + Bay - 275kV"
Case "CA1XO1": Ans = "Transition joint (Oil:XLPE) - 132kV"
Case "CA2XO1": Ans = "Transition joint (Oil:XLPE) - 275kV"
Case "CA4XO1": Ans = "Transition joint (Oil:XLPE) - 400kV"
Case "BC0IB2": Ans = "Cable Installation - Direct Bury - Medium per km"
Case "BC0IR2": Ans = "Cable Installation - Troughed - Medium per km"
Case "BC0IT1": Ans = "Cable Installation - Tunnel - per km"
Case "BC0ID1": Ans = "Cable Installation - Ducted - per km"
Case "BS0CT1": Ans = "Cable Troughing per 100m"
Case "BC0CB2": Ans = "Containment - Direct Bury - Medium per km"
Case "BC0CR2": Ans = "Containment - Troughed - Medium per km"
Case "BC0CT1": Ans = "Containment - Tunnel - per km"
Case "BC0CD1": Ans = "Containment - Ducted - per km"
Case "BC0DD1": Ans = "Containment - Directional Drill"
Case "CA0OJ2": Ans = "Joint Bay - New"
Case "CA4OJ1": Ans = "Cable Joint Bay Refurb - 400kV"
Case "CA2OJ1": Ans = "Cable Joint Bay Refurb - 275kV"
Case "CA1OJ1": Ans = "Cable Joint Bay Refurb - 132kV"
Case "BS0CS1": Ans = "CSE Compound"
Case "BT0BO1": Ans = "Cable Tunnel - Bore - (km) - < 5km"
Case "BT0BO2": Ans = "Cable Tunnel - Bore - (km) - > 5km"
Case "CA4OL1": Ans = "Cable Link Box Replace - 400Kv"
Case "CA2OL1": Ans = "Cable Link Box Replace - 275Kv"
Case "CA4OO1": Ans = "Cable Oil Tank Replace + works - 400kV"
Case "CA2OO1": Ans = "Cable Oil Tank Replace + works - 275kV"
Case "CA1OO1": Ans = "Cable Oil Tank Replace + works - 132kV"
Case "CC4GN1": Ans = "Gas Insulated Line - (km) - 400kV"
Case "CC2GN1": Ans = "Gas Insulated Line - (km) - 275kV"
Case "BB0BL1": Ans = "Plant Building (all voltage levels)"
Case "BB0CV1": Ans = "Non-specific civils - £100k units"
Case "BS0PA1": Ans = "Permanent Access Civils Works - £100k units"
Case "MA0DD1": Ans = "Design and Development - £100k units"
Case "MA0PC1": Ans = "Past Contamination Cleanup - £100k units"
Case "MA0SE1": Ans = "Extra Site Establshment - £100k units"
Case "MA0SP1": Ans = "Professional Services - £100k units"
Case "MA0SS1": Ans = "Site Surveys - £100k units"
Case "MA0TS1": Ans = "Extra Site Security (Temp) - £100k units"
Case "": Ans = ""
End Select
Set cmt = .Comment
Call FormatAllComments
If cmt Is Nothing Then
Set cmt = .AddComment
cmt.Text Text:=Ans

End If
End With
End If
Call FormatAllComments
End Sub

Thanks to anybody who can help!!

 

[Jerry Sullivan]

Can anybody convert this to a simple macro where it adds the comment on command rather than automatically.

The code below might get you started. It shows how to make a comment in your Active Cell that has the value of Cell S6.

Sub MakeComment()
    Dim sValue As String
    sValue = ActiveSheet.Range("S6").Value
    With ActiveCell
        If .Comment Is Nothing Then .AddComment
        If sValue = vbNullString Then
            .Comment.Delete
        Else
            .Comment.Visible = False
            .Comment.Text Text:=sValue
        End If
    End With
End Sub

I'd suggest you avoid hard-coding your lookup table into the VBA code. Instead you might consider having a drop down validation list in S6 with your codes. Then based on the value selected go to a hidden lookup range to get the comment string.