Excel Pop Out cell contents for entire worksheet

[QTQ28]

Hi Friends! I'm not a brainy code person, so I need help here fromt his amazing group of code wizards. I'm working on a spreadsheet where I have the text wrapped, but being that some cells have a lot of text, and I want to keep the cells small for visual appeal, I really want to find a way for the text of the cell to pop out for easy reading which i click on it. Can someone help please?

 

[QTQ28]

Add a real simple Macro like this in the VBA section of that workbook:

Sub Test()
    MsgBox "VBA is enabled!"
    MsgBox Application.EnableEvents
End Sub

And try running it and see if it returns any messages.
If it does, please tell me what the second message returns.

Hi Joe - is there a way to make it so the pop out box becomes editable? Like click on cell, box pops out, and then I can edit the text?

 

[Joe4]

Hi Joe - is there a way to make it so the pop out box becomes editable? Like click on cell, box pops out, and then I can edit the text? View attachment 118186

You would need to use an InputBox instead of a MsgBox, i.e.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ib As String
    
'   Only run if a single cell is selected
    If Target.CountLarge > 1 Then Exit Sub

'   Only run if a cell in column G is selected
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        ib = InputBox("Enter/edit text for this cell", "UPDATE CELL", Target.Value)
        Application.EnableEvents = False
        Target.Value = ib
        Application.EnableEvents = True
    End If
    
End Sub

 

[QTQ28]

You would need to use an InputBox instead of a MsgBox, i.e.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ib As String
   
'   Only run if a single cell is selected
    If Target.CountLarge > 1 Then Exit Sub

'   Only run if a cell in column G is selected
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        ib = InputBox("Enter/edit text for this cell", "UPDATE CELL", Target.Value)
        Application.EnableEvents = False
        Target.Value = ib
        Application.EnableEvents = True
    End If
   
End Sub

shoot - that was visually really unappealing and when you close the box it seems to have deleted the text. and then when I tried to undo it to get the text back, it bounced me to a different spreadsheet all together. really odd. I'll stick with the other pop out box you can't edit.

 

[Joe4]

Yes, a MsgBox can only return a message to the screen - there are no editing options on it.

We can update the code so that it does nothing if they hit the Cancel button.
Here is that update:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ib As String
    
'   Only run if a single cell is selected
    If Target.CountLarge > 1 Then Exit Sub

'   Only run if a cell in column G is selected
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        ib = InputBox("Enter/edit text for this cell", "UPDATE CELL", Target.Value)
'       Exit if user hits Cancel
        If StrPtr(ib) = 0 Then
            Exit Sub
'       Otherwise update cell
        Else
            Application.EnableEvents = False
            Target.Value = ib
            Application.EnableEvents = True
        End If
    End If
    
End Sub