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

[d0rian]

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)

 

[d0rian]

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]

Consider using camera tool - see https://trumpexcel.com/excel-camera-tool/

Ater getting the image into sheet 1, use VBA to keep the image in view

 

[Yongle]

ahh - you already spotted camera tool

 

[Yongle]

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

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

 

[d0rian]

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.

 

[Yongle]

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

 

[shg]

Why not just add a second window showing the range of interest?

 

[d0rian]

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]

How about placing the range pic on a modeless userform - That perhaps could solve the slowing down issue.