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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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)

E9yhxEj.jpg
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
ahh - you already spotted camera tool :)
 
Last edited:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
Why not just add a second window showing the range of interest?
 
Upvote 0
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.
 
Upvote 0
How about placing the range pic on a modeless userform - That perhaps could solve the slowing down issue.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top