Is it possible to have a table appear as a floating "object" on a sheet rather than in cells?

d0rian

Board Regular
Joined
May 30, 2015
Messages
245
I have a table (shown in image below) in A1:C21 on Sheet 2 that I want to be constantly visible when I'm working on Sheet 1. HOWEVER, I don't want it to actually use up any CELLS on Sheet 1 (since I don't really have cells to spare there.)


Idea popped into my head today: can I instead create an 'object' or something (in the same way that you can create a chart or place a Shape on a sheet, and it lives in a bounded window that you can drag around) that would show the table that I want always-visible when I'm on Sheet 1?

(In case it's not clear, this can't be a static image, since the Sheet2!A1:C21 values are formulas that derive from changes I make on Sheet 1...so the object has to be something whose values can update as I work)

 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

d0rian

Board Regular
Joined
May 30, 2015
Messages
245
Think I solved my own problem - seems like the not-too-well-known-but-secretly-awesome "Camera" tool does exactly what I need. Wow, can't believe I didn't know about this.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,659
Office Version
365
Platform
Windows
ahh - you already spotted camera tool :)
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,659
Office Version
365
Platform
Windows
A simple way to take the picture wherever you go on the sheet amend to suit your needs)
Click on a cell and the image moves to the adjacent cell

Rename the image CamPic and place VBA below in the sheet module for sheet1
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Shapes("CamPic").Left = Target.Offset(, 1).Left + 15
    Me.Shapes("CamPic").Top = Target.Top
End Sub
 
Last edited:

d0rian

Board Regular
Joined
May 30, 2015
Messages
245
Thanks for replies. Re: the camera tool, looks like it might have been too good to be true. Despite doing exactly what I want, it seems to absolutely kill my workbook performance. Specifically, VBA code that I need to run frequently (which just does a bunch of bunch of copy/paste/replace stuff), which usually runs in about 1-2 seconds now makes Excel hang for 60-90 seconds before completing. What makes no sense about this is that the code doesn't have any relationship to the small range of cells that the camera picture is linked to...so it's not like the pic is somehow struggling to keep up with the changes that my copy/paste/replace VBA code is making -- it's a completely separate range. Not only that, but I tried all of the below:
- I hardcoded the source range for the camera pic
- I even moved the pic itself to a different sheet (which would defeat purpose of what I'm trying to do, but wanted to test if it was some sort of rendering thing whereby the pic just uses a lot of resources or something if it's on screen)

Neither of these changed the fact that my code now made Excel hang for 60+ seconds before completing. I'm sure it's the pic causing it because as soon as I delete it, performance returns to normal. It seems like merely having a camera-tool pic anywhere in my workbook (even on a sheet in the background, and linked to hardcoded values, really kills my file...any reason why this would be?

EDIT:
Sigh, seems like this is a known issue. What still doesn't quite make sense to me is that the few posts I've read about it seem to explain that the slowdown is indeed -- as i suspected above -- due to the picture "keeping up" / updating-to-reflect the values in the source range. But I find that the slowdown happens even if the values in the source range are hardcoded...so theoretically there shouldn't be any need to incorporate updated values into the pic at all...regardless, the only solutions available seem to be hacked-together stuff like having your macro delete all camera-pics in the file and then recreate them at the end of your code. Pretty crude, surprised this is so problematic.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,659
Office Version
365
Platform
Windows
You could try this. Test first and then could be automated if not too problematic

1 Copy the range from sheet2 and paste into Notepad
2 Copy from Notepad and paste into a TextBox in sheet1
3 Move that around the sheet instead
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Why not just add a second window showing the range of interest?
 

d0rian

Board Regular
Joined
May 30, 2015
Messages
245
Why not just add a second window showing the range of interest?
Because I need Sheet 1 (my main working sheet) maximized / talking up my entire screen real-estate. I've squeezed every possible pixel out of my column widths and don't have any cells to spare...hence why I was even thinking about putting the table in a floating object / camera-pic in the first place.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,771
Office Version
2016
Platform
Windows
How about placing the range pic on a modeless userform - That perhaps could solve the slowing down issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,840
Messages
5,446,817
Members
405,416
Latest member
galoli

This Week's Hot Topics

Top