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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

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
Top