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

##### New Member
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### erik.van.geit

##### MrExcel MVP
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

Code:
``````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
Code:
``````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...)

kind regards,
Erik

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

##### New Member
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!!!!!!

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
I have one button that will be going to a website address
Code:
``````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

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:
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
Code:
``````Private Sub CommandButton1_Click()

End Sub``````
where you can fill in your macro

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

kind regards,
Erik

##### New Member
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!!!

Replies
0
Views
210
Replies
0
Views
109
Replies
0
Views
350
Replies
0
Views
199
Replies
1
Views
174

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

### 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.

### Which adblocker are you using?

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

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