VBA Lightbox? Code and Examples

[sjc4]

I have several cells filled with text. Is it possible to assign a Lightbox Macro to each of these cells so that when I double click them the text expands and comes forward (per se, the text "Lightboxes")?

here is an example of Lightbox if you don't know what it is:

http://www.blog.methodsinexcel.co.uk/2009/07/31/excel-lightbox-viewer/
http://www.juiceanalytics.com/writing/lightboxing-images-in-excel/

 

[baitmaster]

You could use the double-click event to do the following:

• Show a text box which has been hidden on your sheet
• Assign the cell text to the text box
• reposition the text box in line with the cell that you double-clicked

You would need another event to hide the text box again when you deselect it

I'll give it a bit of thought and see if I can write something...

 

[baitmaster]

I added an ActiveX label to my worksheet, and the following code to the worksheet module. Its basic, but does sort of what you want. You may need to resize it etc, but this should show you the basic theory

I set the Label to have a large font size, by adjusting the font in the properties window of the VBE

I needed the On Error for those strange occasions where Excel selection jumps around randomly, I don't know if anyone else gets this or if it's just me...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
On Error Resume Next
With Label1
    .Caption = Target.Text
    .Top = Target.Top
    .Left = Target.Left
    .Visible = True
End With
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Label1.Visible = False
End Sub

 

[sjc4]

I am going to try it out now. I'll post back to the boards

 

[Norie]

Don't see why it couldn't be done with cells.

Though if you were to do it using the techniques mentioned your links you might need code to save the cell(s) as images.

Then you would have more code to display those images.

Actually, this seems to do something similar but very basic without saving the image.

This goes in the worksheeet you want the code to work on.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pic As Shape
Dim I As Long

    Target.CopyPicture Appearance:=xlScreen, Format:=xlPicture
 
    Me.Paste
 
    Set pic = Me.Shapes(Me.Shapes.Count)
 
    pic.Left = Target.Left
    pic.Top = Target.Top

    For I = 10 To 200
        pic.Width = I
        pic.Height = I
    Next I
 
    pic.OnAction = "DeletePicture"

    Cancel = True
 
    Application.Goto Target
 
End Sub

This goes in a standard module.

Sub DeletePicture()
    ActiveSheet.Shapes(Application.Caller).Delete
End Sub

It can definitely be improved on to get a 'real' lightbox effect, eg putting rest of sheet in 'shadow'.

Oh, and it only works on one cell but again I'm sure that can be improved on.

 

[baitmaster]

good thinking Norie, didn't think of a camera shot, nice angle

 

[sjc4]

Norie that is surely a start. Well done too! However, It definitley needs some modifcations. For instance:

-display all text in a cell
-consistent box and font size
-and, like you said, the shadow effect of a true lightbox


p.s.

this has the potential to be a pretty powerful/effective Excel tool.

 

[Norie]

Well I understand some of that but not sure about some of it too.

When you copy as a picture the whole selection is copied as it is.

Also because it copies a picture it copies the format, eg font size

The 'box' in the code I posted is a consistent size 200 by 200.

Like I said very simple, and it isn't really an image we are showing - it's the cell copied as an image.

 

[sjc4]

correct me if i'm wrong: the amount of text shown in the lightbox cannot be modified because it is a picture of the cell?

Well I understand some of that but not sure about some of it too.

When you copy as a picture the whole selection is copied as it is.

Also because it copies a picture it copies the format, eg font size

The 'box' in the code I posted is a consistent size 200 by 200.

Like I said very simple, and it isn't really an image we are showing - it's the cell copied as an image.

 

[Norie]

That's right.

Perhaps displaying a textbox is a better idea, as far as I know that's the way it's done on web pages when dealing with text.