-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Help with always visible table in excel
- Thread starter kt_mason
- Start date
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
For one possible approach to doing this, follow these steps:
Step 1
Select the range of data which represents the table you want to keep visible.
Step 2
With that range selected, press the Shift key, and while doing that, from the worksheet menu click Edit > Copy Picture.
Step 3
Select the option "As shown when printed" and click OK.
Step 4
Activate the sheet where you want this table to always be visible.
Select any cell and press Ctrl+V.
Step 5
At that point, the range is a picture object on your desired sheet, and that picture object is selected. Click inside the name box (just to the left of the formula bar) and enter "PictureX" (without the quotes) and then press Enter.
Step 6
Select any worksheet cell to deselect the PictureX object.
Step 7
Right click on that sheet tab, left click on View Code, and paste this into the large white area that is the worksheet module:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange
ActiveSheet.Shapes("PictureX").Top = .Top + 5
ActiveSheet.Shapes("PictureX").Left = .Left + .Width - ActiveSheet.Shapes("PictureX").Width - 45
ActiveSheet.Shapes("PictureX").Top = .Top + 35
ActiveSheet.Shapes("PictureX").Left = .Left + .Width - ActiveSheet.Shapes("PictureX").Width - 45
End With
End SubStep 8
Press Alt+Q to return to the worksheet.
Select any cell and scroll down or across and select any other cell, and as you do that, the picture object will always be in the upper right corner of the screen.
Note - -
This is triggered by the selection of cells.
It is possible with a timer to monitor the scroll bar but that is more involved (do-able, just too top-heavy and not necessary in this application), so see if the above steps get you what you want.
Upvote
0
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
Thanks for that nice message Stormseed...hopefully kt_mason might be able to use it, though I suspect there are other ways too, such as a modeless userform with a spreadsheet control, or floating an image control, or splitting the pane, depending on what works best for the application.
Upvote
0
BlueSocksMcGee
New Member
- Joined
- May 24, 2006
- Messages
- 39
Hi everyone, I am trying to do something similar in that I want to see the table always floating on the screen and to be movable + I want to link the values of the table to a range in a sheet. That way when I change the values in the floating table the range changes as well. I was considering using a textBox, and a regular Text Box and can link them to one cell but not to a whole 2 dimensional range. Any thoughts?
Upvote
0
Dear Tom
Is it possible to tweek this code in VBA so that I can use the mouse scroll to go down and up the page while still viewing the floating table. The code only seems to allow Page Up Page Down mechanism, but not the mouse scroll.
If so, can you please supply the amended code for me.
Thanks
Is it possible to tweek this code in VBA so that I can use the mouse scroll to go down and up the page while still viewing the floating table. The code only seems to allow Page Up Page Down mechanism, but not the mouse scroll.
If so, can you please supply the amended code for me.
Thanks
Upvote
0
Similar threads
- Solved
