I have a worksheet with rows and columns already frozen, I need a large text
box to always remain on the screen when I scroll. It is too large to fit in
the frozen cells.

Thanks,
Paul

Hi Paul,

As far as I know that is really only possible using a
Worksheet_SelectionChange event procedure.

The following code places the Text Box named "Text Box 1" at the bottom
righthand corner of the range of selected cells. Just change the
line...

Set Box = Me.Shapes("Text Box 1")

to suit the name of your text box, which is visible in the name box on
the left of the Formula Bar when the Text Box is selected.

To get the code into place...

1. Copy the code

2. Right click the worksheet's sheet tab then select "View Code" from
the popup menu.

3. Paste the code into the worksheet's code module

4. Press Alt + F11 to return to the usual Excel interface

5. Save

6. For the code to work your Security level will need to be medium. If
it is higher than medium then go Tools|Macro|Security... then select
medium, then Close and reopen the workbook. When the workbook is open
you will need to click the "Enable Macros" button on the Security
Warning dialog that appears on all workbooks with macros and security
level on medium.

The Text Box doesn't actually move while you scroll. Instead, it snaps
into place as soon as the user selects a cell or range of cell in the
new visible range.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Box As Shape
Set Box = Me.Shapes("Text Box 1")
If Selection.Left + Selection.Width _
+ Box.Width Rows(1).Width Then
Box.Left = Selection.Left - Box.Width
Else: Box.Left = Selection.Left + Selection.Width
End If
If Selection.Top + Selection.Height _
+ Box.Height Columns(1).Height Then
Box.Top = Selection.Top - Box.Height
Else: Box.Top = Selection.Top + Selection.Height
End If
Box.ZOrder msoBringToFront
End Sub

Ken Johnson

Thanks Ken, that's pretty cool. (A little anoying though when I'm working in
the sheet)

"Ken Johnson" wrote:

Hi Paul,

As far as I know that is really only possible using a
Worksheet_SelectionChange event procedure.

The following code places the Text Box named "Text Box 1" at the bottom
righthand corner of the range of selected cells. Just change the
line...

Set Box = Me.Shapes("Text Box 1")

to suit the name of your text box, which is visible in the name box on
the left of the Formula Bar when the Text Box is selected.

To get the code into place...

1. Copy the code

2. Right click the worksheet's sheet tab then select "View Code" from
the popup menu.

3. Paste the code into the worksheet's code module

4. Press Alt + F11 to return to the usual Excel interface

5. Save

6. For the code to work your Security level will need to be medium. If
it is higher than medium then go Tools|Macro|Security... then select
medium, then Close and reopen the workbook. When the workbook is open
you will need to click the "Enable Macros" button on the Security
Warning dialog that appears on all workbooks with macros and security
level on medium.

The Text Box doesn't actually move while you scroll. Instead, it snaps
into place as soon as the user selects a cell or range of cell in the
new visible range.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Box As Shape
Set Box = Me.Shapes("Text Box 1")
If Selection.Left + Selection.Width _
+ Box.Width Rows(1).Width Then
Box.Left = Selection.Left - Box.Width
Else: Box.Left = Selection.Left + Selection.Width
End If
If Selection.Top + Selection.Height _
+ Box.Height Columns(1).Height Then
Box.Top = Selection.Top - Box.Height
Else: Box.Top = Selection.Top + Selection.Height
End If
Box.ZOrder msoBringToFront
End Sub

Ken Johnson

Paulm wrote:
Thanks Ken, that's pretty cool. (A little anoying though when I'm working in
the sheet)


Hi Paul,

I agree.

Try this possible improvement, where if you double click anywhere in
the sheet the Text Box will stop tagging your selection until the next
time you double click antwhere in the sheet.
I've used a second event procedure to toggle the Text Box's selection
following behaviour.

Option Explicit
Public blnTagSelection As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
blnTagSelection = Not blnTagSelection
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If blnTagSelection Then
Dim Box As Shape
Set Box = Me.Shapes("Text Box 1")
If Selection.Left + Selection.Width _
+ Box.Width Rows(1).Width Then
Box.Left = Selection.Left - Box.Width
Else: Box.Left = Selection.Left + Selection.Width
End If
If Selection.Top + Selection.Height _
+ Box.Height Columns(1).Height Then
Box.Top = Selection.Top - Box.Height
Else: Box.Top = Selection.Top + Selection.Height
End If
Box.ZOrder msoBringToFront
End If
End Sub

Just follow the same steps to replace the old code with above code.

Ken Johnson