display long cell text in text box

[cricked]

Hi all,

I have a special case where I want to make an easy-to-read table overview on trainings, however one of the columns contains the content of the training and therefore needs to be able to display a lot of text.
I do not want to adjust the height of the rows but rather looking for an alternative like a pop up window or something similar displaying the full text.

I already tried with VBA a bit but as I do not have experience with VBA i could not solve the runtime error issue.

Any help and recommendations on how display the text in the best way and/or adjust the VBA would be appreciated!

Thanks and BR
cricked

[protonLeah]

So! You posted a workbook with empty cells and an empty textbox

[cricked]

it actually is not empty.. if you enable macros and click on a cell in the content column (F) a pop up window with its content will appear (which is done with VBA) if you then click on another cell (not in column F) the window disappears again. However if you then click again on another cell (not in column F) it shows a runtime error.
I suppose this is due to the fact that there is no text box to delete..

This is the code used:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Static oshp As Shape
Set ws = ActiveSheet
If Not oshp Is Nothing Then oshp.Delete
If Target.Column = 6 Then
Set oshp = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 700, 100, 250, 400)
oshp.TextFrame2.TextRange.Characters.Text = Target.Value
End If
End Sub

[pipoliveira]

Hi There,
A simple line on your code will fix the issue as following (oulined in red):

Please Login or Register  to view this content.

Let me know if that worked.
Cheers,
Filipe

[Andy Pope]

If you delete the shape you will also need to set the object reference to nothing.

Please Login or Register  to view this content.

[cricked]

thanks to you both! Andy popes code works perfectly fine
pipoliveira - your code worked however the pop up window did not disappear after clicking in another cell then column 6

[pipoliveira]

Hi there,
Strange, I have tested on my laptop and the pop up only appears when I click on column 6.
Better use Andy code then.
As it seems that you have a solution, you can now set this thread as "SOLVED" under "Thread Tools" and please add reputation to Andy
Good Luck!
Filipe Oliveira