Enlarge pics on worksheet...can this be done??

[brad2157]

I am working on a workbook that has several sheets within it with various information. Each spreadsheet has several pictures on it as well. I would like to have it so that when the small picture is clicked on, then it will pop up a box with a larger version of that particular picture. This would allow small details to be seen, without having to manually drag to resize each picture you wanted to view. I am not even sure this is possible what I am wanting to do.

 

[erik.van.geit]

Hello, brad2157
Welcome to the Board !!!!!

this is not exactly how you asked it, but let's see if it's OK for you ...
1. paste the code in a "normal" macro-module
2. assign the same macro to all your pictures using the code "assign_macro_to_all_pics"
3. whenever you click a picture it will enlarge
4a. clicking again the same picture, it will resize back to it's original state
4b. clicking another picutre will perform 4a and enlarge the one you just clicked

I assume this is userfriendly

Public PW As Integer    'picture width
Public PH As Integer    'picture height
Public PIC As Object
Dim RF As Single        'resize factor
Public LAR As Boolean
Public CloseFlag As Boolean

Sub zoom_pics()
'Erik Van Geit
'051124
'click a pick to enlarge

    If Not PIC Is Nothing Then
        If RF <> 0 Then
            With PIC
            .Width = PW
            .Height = PH
            .LockAspectRatio = LAR
            End With
        RF = 0
        If CloseFlag Then Exit Sub
        If ActiveSheet.Shapes(Application.Caller).Name = PIC.Name Then Exit Sub
        Set PIC = Nothing
        End If
    End If

If CloseFlag Then Exit Sub
Set PIC = ActiveSheet.Shapes(Application.Caller)
Dim AW As Integer
Dim AH As Integer

    With ActiveWindow.VisibleRange
    AW = .Width
    AH = .Height
    End With

    With PIC
    LAR = .LockAspectRatio
    .LockAspectRatio = msoTrue
    PW = .Width
    PH = .Height
    RF = Application.Min(AW / PW, AH / PH) * 0.8
    .Width = .Width * RF
    End With

End Sub

Sub assign_macro_to_all_pics()
Const MacroName = "zoom_pics"
Const msg = "This procedure will assign the macro " & """" & MacroName & """" & _
" to all your pictures on this sheet." & vbLf & "Do you want to proceed?"

If MsgBox(msg, 292, "ASSIGN MACRO") = vbNo Then Exit Sub

    On Error Resume Next
        For Each PIC In ActiveSheet.Pictures
        PIC.OnAction = MacroName
        Next PIC
    On Error GoTo 0
End Sub

Wait a moment ...
still one step to go
when the workbook is closed you need to restore the last picture if you forgot to do so
therefore paste this code into the workbookmodule

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CloseFlag = True
zoom_pics
End Sub

it was a JOY to create this (although it could already exist somewhere...)
feel free to post comments

kind regards,
Erik

EDIT: just added one line three minutes after posting
If CloseFlag Then Exit Sub (second appearance)

 

[brad2157]

Hey Erik, I cant thank you enough. Even not having alot of knowledge of Excel, I was still able to get this to work just as I wanted. I REALLY do appreciate your help and the code you wrote up. That was a lifesaver. This works PERFECT for what I needed it for. THANK YOU AGAIN!!!!!!
-BRAD

 

[brad2157]

It worked fine till I was testing out my whole spreadsheet and found out that since I have started using these macros, my buttons at the top of my spreadsheets will no longer work. If I open the workbook and enable macros, then I experience the problem, if I open the workbook and dis-able the macros, then the buttons work like they should. The two buttons I refer to, are simply just hyperlinked to another spreadsheet within the same workbook. I am not too experienced with excel, but I think it has something to do with the macros referring to "object". I noticed when I right click on the buttons, it refers to them as objects also.

The coding for both macros is listed in the previous post for you guys to look at.

I hate to lose the macros, because they enlarge the pics just like I needed them to and I would really love to be able to use the hyperlinked buttons on the same spreadsheets as well.

Thank you guys for all your help!!!!

 

[erik.van.geit]

if I open the workbook and dis-able the macros, then the buttons work like they should.

this is a strange statement, since buttons normally use macros and when they are disabled the buttons can't do anything

I just tested again: the buttons are not getting assigned to the "enlarge" macro when running "assign_macro_to_all_pics", so that can't be the problem, unless ... I wouldn't know

can you clarify what you mean by this

The two buttons I refer to, are simply just hyperlinked to another spreadsheet within the same workbook

how did you assign hyperlinks to buttons ?

we'll get out of the problem, perhaps will need some time ... ?

best regards,
Erik

 

[brad2157]

The way I assigned a hyperlink to the buttons was to click the button I wanted to hyperlink, then I went to INSERT>HYPERLINK from the top menu choices. I then selected to another page within the workbook.

Its weird...if I enable macros, then it still locks up excel if I try to right click the buttons. I had thought at first it had something to do with the way the pictures were addressed in the macro. I just tried to test out another theory, and even if I select enable macros and go to a page that has only the two buttons and no pictures at all....it still freezes up excel. This is surely a strange problem. I am going to play around with it some more and see if I can come up with anything else.

If I enable macros when opening, and click (either left or right) on a button, it greys out all the edit buttons on the toolbar across the top. It does this until I click a cell in the spreadsheet.

I think if I create a macro for each of the two buttons and assign them, then that may clear the trouble. I am going to see if I can find some code to do so on the message forum here because I am not too handy with writing macros or scripts.

I also found out that with macros enabled, after I click a button and excel freezes up, I can click a cell on the spreadsheet (to unfreeze) and then if I go to "control toolbox" toolbar and add a new button from there, delete the button...then my orginal two buttons will work and direct me to another spreadsheet...then after that...its back to same again...freezes up again.

 

[erik.van.geit]

your method of creating hyperlinks with buttons is not really an "inbuilt-function"
you can assign hyperlinks to cells: wouldn't that be easier ?
do you have a specific reason to use buttons ?

best regards,
Erik

 

[brad2157]

I see what you mean. I noticed it doesnt give you a option to assign a hyperlink to the button, when you right click on it. I am mainly wanting to use the buttons, because its a more prefessional look to the workbook. It stands out when viewing and I want to make this real user friendly as I will be distrubuting it out to a whole lot of people once finished.

Is there another method I can use that the hyperlink method. To still save the buttons. I have one button that will be going to a website address, and the other button is just going to the main spreadsheet within the workbook.
I didnt know if there was some way I can assign a macro to the button and make it work, or if I will still have the same trouble.

THANKS alot for all your help...its really appreciated!!!!!!!!

 

[erik.van.geit]

I have one button that will be going to a website address

Option Explicit

Sub goto_webpage()
Dim WSh As Object

Set WSh = CreateObject("WScript.Shell")
WSh.Run "www.yourwebsitename.be"

End Sub

and the other button is just going to the main spreadsheet within the workbook.

some possibilities
named range
Application.Goto Reference:="data"
just "hardcoded ranges"
Application.Goto Reference:=Range("sheet1!A1")
or
Application.Goto Reference:=Sheets("sheet1").Range("A1")
assign a macro to it your buttons
If your button is made with the Forms Toolbar

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the following code:

Sub a()
MsgBox "Hello"
End Sub

right click on the button
choose "assign macro"
choose macro a

clicking on the button you will get "Hello"

if you're button is created with the Controls Toolbar
choose "show programm code" from the rightclick menu

you will get this

Private Sub CommandButton1_Click()

End Sub

where you can fill in your macro

Private Sub CommandButton1_Click()
MsgBox "Welcome to the Board",48,"HELLO"
End Sub

kind regards,
Erik

 

[brad2157]

I deleted my buttons and went back and added them as you instructed and this DID fix my trouble I had. I was adding them under a different toolbar and not under the FORMS toolbar.

I have one more quick question for you. What code do I need to use if I just want to use a macro to go to a file location on my hard drive of my PC? I tried to use the webpage one, and replace the "www.abc123.com" with my file location and it doesnt seem to like it. I also need this to be in a macro for compatablity issues. THANKS A BUNCH Erik!!!