Show/hide text box on cell select/deselect

b0unce

Board Regular
Joined
Apr 22, 2009
Messages
73
Hello,

maybe anyone of you know the solution to my problem? I need this:
1. When you select a certain cell (let's say B2), text box pops up; which allows you to insert/edit comment.
2. When you lose focus of that certain cell (select from B2 to let's say A1), the text box hides.

I know this can be done using macros with certain events, but maybe it is possible to have this done without macros? As the users will be adding more of these text and I do not want to code each of these textboxes individually. Maybe something can be done using simple linking or throught object settings?

Please let me know if anyone knows how to solve this problem.
Thank you!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Don't think there's a non-VBA solution to this. If you do want a VBA solution then try below.

Place this code in the code module corresponding to the worksheet:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim old_txt As String
    Dim new_txt As Variant
    
    With Target
        If .Address <> "$B$2" Then Exit Sub
        On Error Resume Next
        old_txt = .Comment.Text
        On Error GoTo 0
    End With
    
    new_txt = Application.InputBox( _
        Prompt:="Add comment:", _
        Default:=old_txt, _
        Type:=2)
        
    If new_txt = False Then Exit Sub
    
    With Target
        On Error Resume Next
        .ClearComments
        On Error GoTo 0
        .AddComment
        .Comment.Text Text:=new_txt
    End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,149
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top