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
 
If you fancy trying suggestion by @Jaafar Tribak, here is some code to help
Test on a COPY of your workbook! - ensure that there are no shapes or images etc in sheet2 before running the code
(the workbook must have been saved before running the VBA - it is using the workbook path to save the gif)

The code creates the camera image, creates a chart , places image in chart, exports chart as gif, loads it automatically into the userform when activated, deletes all images created

To test
1. Create a UserForm containing an image frame named Image1
2. Place VBA into that userform's code window
3. Show the userform

Code:
Private Sub UserForm_Activate()
    Call LoadImage
End Sub

Private Sub LoadImage()
    Dim C As Chart, R As Range, FName As String, W As Worksheet
    Set W = Sheets("Sheet2")
    Set R = W.Range("A1:C21")
'add temp chart
    W.Shapes.AddChart2(262, xl3DPie).Select
    Set C = W.ChartObjects(1).Chart
'Resize chartobject
    With C.Parent
        .Width = R.Width
        .Height = R.Height
    End With
'Copy the cells as bitmap and paste into the chart
    R.CopyPicture xlScreen, xlBitmap
    C.Paste
'Export
    FName = ThisWorkbook.Path & "\temp.gif"
    C.Export FName, "GIF", False
'Load into the userform
    UserForm1.Image1.Picture = LoadPicture(FName, 0, 0)
'delete temp image and chart in sheet
    Do While W.Shapes.Count > 0
        W.Shapes(1).Delete
    Loop
End Sub
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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