Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to insert floating table or textbox in Excel worksheet?

If you have a table or a textbox with some important data which you want to always be visible on the worksheet screen even though when you scroll up or down the worksheet. This may be an interesting task, this article, I will introduce a quick trick to solve it.

Insert floating table in Excel with VBA code

Insert floating textbox in Excel with VBA code


Maximize or minimize worksheet window:

To maximize the worksheet window can release more space for users, in Excel, you can maximin the worksheet window by hiding ribbon, formula bar or status bar. Kutools for Excel's Work Area can help you to hide the ribbon with only one click. Download and free trial Kutools for Excel now!

doc insert floating table 6


arrow blue right bubble Insert floating table in Excel with VBA code


There is no direct way for you to insert the floating table, so you need to convert the table data to an image, and then apply a code to finish it, please do as this:

1. Select the data table that you want to keep visible, then click Copy > Copy as Picture under the Home tab, see screenshot:

doc insert floating table 1

2. In the popped out Copy Picture dialog box, select As shown on screen from the Appearance section, and choose Picture under the Format section, click OK to close the dialog, and then click a cell to output the picture, at last, press Ctrl + V to paste this picture, see screenshot:

doc insert floating table 2

3. After converting the table to picture, then right click the sheet tab which contains the table picture that you want to keep visible always, and choose View Code from the context menu, and in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:

VBA code: Insert floating table in excel:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161116
    Application.ScreenUpdating = False
    If Target.Cells.Count > 1 Then Exit Sub
        With ActiveSheet.Shapes("Picture1")
            .Top = ActiveWindow.VisibleRange.Top + 5
            .Left = ActiveWindow.VisibleRange.Left + ActiveWindow.VisibleRange.Width - .Width - 45
        End With
    Application.ScreenUpdating = False
End Sub

Note: In this code, the Picure1 is the image name you have created, please change it to your need.

doc insert floating table 3

4. Then save and close the code window, now, when you scroll the worksheet and click a cell, the table picture is always kept on the top right corner of the worksheet.


arrow blue right bubble Insert floating textbox in Excel with VBA code

To insert a floating textbox, here is also a VBA code to solve this task.

1. First, you should insert a textbox from the Developer tab, click Developer > Insert > Text Box (ActiveX control), and then draw the textbox, and enter the information into the textbox as you need. See screenshot:

doc insert floating table 4

2. Then right click the sheet tab which contains the textbox that you want to keep visible always, and choose View Code from the context menu, and in the opened Microsoft Visual Basic for Applications window, copy and paste the following code into the blank Module:

VBA code: Insert floating textbox in Excel:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161116
    Application.ScreenUpdating = False
    With ActiveWindow.VisibleRange
        TextBox1.Top = .Top + 5
        TextBox1.Left = .Left + .Width - TextBox1.Width - 45
    End With
    Application.ScreenUpdating = True
End Sub

Note: In this code, the TextBox1 is the textbox name you have inserted, please change it to your need.

doc insert floating table 5

3. Then save and close the code window, now, when you scroll the worksheet to anywhere, the inserted textbox is always kept on the top right corner of the worksheet.


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Guest · 2 months ago
    If I re-size the window, the picture floats too far into the middle. How do I make the picture float to the same margin from the screen no matter what size window I have?
  • To post as a guest, your comment is unpublished.
    Rachelle Reamer · 1 years ago
    Thank you-this solved my problem very easily!