Right Click Cell to Show Text

[Sharonca]

Hello,
I have a protected sheet that has all formula columns set to locked & hidden. I would like to be able to show the text of the cells as the sheet is large and not all the text shows. I inserted an activeX text box and have the following code in the sheet. When I right click on a cell within the range designated the text box does not show. What am I doing wrong?

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H:FC")) Is Nothing Then
Cancel = True
With TextBox1
.Visible = True
.Text = Target.Text
.Left = Target.Offset(, 1).Left
.Top = Target.Top
End With
Else
TextBox1.Visible = False
End If
End Sub

Thank you for your time and attention.

 

[6StringJazzer]

It is a little confusing as to what cells you want to be able to click on. You start by saying that some columns are locked and hidden but it seems that your double click has nothing to do with that, since you can't double click a hidden cell. But if the cell is not hidden, then the user can see the content of the cell, so it's not clear why you need a textbox to show it.

At any rate, are the cells in H:FC locked? Does your protection mode specify that you can select unlocked cells and locked cells?

If your protection mode does not allow selection of these cells then you can't double click on them.

Here is a sample file based on what you described. The cells are locked but Select Unlocked Cells is selected for protection. A right-click will show the content in the textbox.

Sharonca=double click.xlsm

Shared with Dropbox

www.dropbox.com