Momentary Popup Table

[bullcreel]

Lets say I have a small table on Sheet3 cells B2 to J13. I am on Sheet1 and just want to hit a button and display (view only) that table (not the whole worksheet, but just cells range B2 to J13). Then I can hit the button again and it disappears. Is there a way to do this?

Thanks in Advance
Jim

 

[Yongle]

Yes - this is very simple to achieve with VBA

1. Insert a new module and paste this code into that module

Option Explicit
Sub ShowTable()
    DeletePicture
    Sheets("Sheet3").Range("B2:J13").Copy
    With ActiveSheet
        .Pictures.Paste
        .Shapes(.Shapes.Count).Name = "TablePicture"
        .Shapes("TablePicture").OnAction = "DeletePicture"
    End With
End Sub

Sub DeletePicture()
    On Error Resume Next
    ActiveSheet.Shapes("TablePicture").Delete
    On Error GoTo 0
End Sub

2. activate Sheet1 and hit keys {ALT } {F8} for list of macros and run ShowTable

3. Click on the table to make it dissappear

 

[bullcreel]

It is working, however, it wont delete. I get a "Ambiguous Name" when trying to delete. One other question. Can something like this be done if it wasnt a table but just a range of cells

 

[Yongle]

The current code is using a range of cells not the fact that it's a table.
- it makes no difference
I will update the code later to deal with that problem

 

[bullcreel]

Thank You so much sir. It works great though. One more question, what can I add into the code to ensure it shows up in the midde of the sheet i want it to show up in; for exampl R2?

 

[Yongle]

The user could be in any cell when the macro is run....
- would you prefer the image to be placed centrally in the visible area of the sheet?

 

[bullcreel]

Yes Sir, that would be great

 

[Yongle]

Hopefully ...
- the table is now placed in middle of the visible area of the sheet
- code to delete image amended to deal with any found duplicate shape names

Sub ShowTable()
'delete previous picture
    DeletePicture
'create new picture centrally placed
    Application.ScreenUpdating = False
    Sheets("Sheet3").Range("B2:J13").Copy
    With ActiveSheet
        .Pictures.Paste
        With .Shapes(.Shapes.Count)
            .Name = "TablePicture"
            .OnAction = "DeletePicture"
            .Left = (ActiveWindow.VisibleRange.Width - .Width) / 2
            .Top = (ActiveWindow.VisibleRange.Height - .Height) / 2
        End With
    End With
End Sub

Sub DeletePicture()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Name = "TablePicture" Then shp.Delete
    Next shp
End Sub

How do you trigger the code?
If this code is placed in sheet1 code window it will trigger when user double-clicks any cell
(right-click on sheet1 tab \ view code \ paste code into the open window)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    ShowTable
End Sub

 

[bullcreel]

It is working just Like I want except...Still getting Ambiguous Name when clicking on it for it to delete

 

[bullcreel]

Nevermind, I found the problem. While working on this myself and with you, I ended up having some macros with the same name. I got rid of everything except the code you provided here and all is working.

Thank You So Much.

Jim Creel