Macro/VBA to insert a comment mandatory when selecting a dropdown option

ussy1995

New Member
Joined
Sep 10, 2019
Messages
2
Is there a macro or vba code i can use so that when cell C6 dropdown is selected, a message box appears for the user to insert a comment mandatory?

I would like to use this for multiple cells i.e. cell C6 dropdown 'Resource' is selected, message box appears saying 'Insert Comment'...user inserts comment and clicks OK...the cell now has a comment attached to it. Doing the same for Cell C7, Cell D14 etc.

thanks
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,869
Office Version
365
Platform
Windows
Code below prevents user modifying cell until a comment has been added
- cell A1 is activated if comment left blank

Put code in SHEET module (does not work in standard module)
- rightClick sheetTab \ viewCode \ paste code into the window which opens \ go back to Excel with {ALT}{F11}
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim msg As String, T As String
    T = Target.Address(0, 0)
    Select Case T
        Case "C6", "C7", "D14"
            msg = InputBox("enter comment for " & T)
            If Len(msg) > 0 Then
                On Error Resume Next
                Target.Comment.Delete
                On Error GoTo 0
                Target.AddComment msg
            Else
                Range("A1").Select
            End If
        End Select
End Sub
 
Last edited:

Forum statistics

Threads
1,084,796
Messages
5,379,933
Members
401,633
Latest member
DinnerB0ne

Some videos you may like

This Week's Hot Topics

Top