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
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:
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:
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
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.
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.
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:
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
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.
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.
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!
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
