INVOLVED - I Suck With User Forms...

[RayportingMonkey]

I have a dashboard sheet with a series of (camera) images that are a
thumbnail image of a chart or graph in my reports. I have written three
macros that change the size of said image to "Large", "Larger" or
"Minimized".

I need to create a "SizeChooser" macro that will link to the image and will
launch a User Form that simply has three choices, Large, Larger & Minimize,
which when clicked will link to the appropriate macro. This "Size Chooser"
macro will be associated with the image, so that when the image is clicked,
the user gets a popup user form that gives them the choice of what they want
to do to view the data contained in that image.

I need help setting up the User Form and writing the code that runs the
other three macros.

Here's the macro code I have for changing the image sizes:

Sub Larger()
'Select the image and format picture scale to 100%
ActiveSheet.Shapes("Picture 53").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 431.25
Selection.ShapeRange.Width = 356.25
Selection.ShapeRange.Rotation = 0#
Application.Goto Reference:="R1C1"
End Sub
Sub Large()
'Select the image and format picture scale to 65%
ActiveSheet.Shapes("Picture 53").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 279.75
Selection.ShapeRange.Width = 231#
Selection.ShapeRange.Rotation = 0#
Application.Goto Reference:="R1C1"
End Sub
Sub Minimize()
'Return the image to it's 20% minimized position
ActiveSheet.Shapes("Picture 53").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 86.25
Selection.ShapeRange.Width = 71.25
Selection.ShapeRange.Rotation = 0#
Application.Goto Reference:="R1C1"
End Sub

The other two related issues I need help with are:

1) The ability for the code to know which image was clicked and enter the
appropriate "Picture Name" as a variable into the ActiveSheet.Shapes("Picture
Name").Select line.

2) Is there a better way of de-selecting the image than grabbing a cell,
like I'm doing now? I can't leave the image selected...

As always, I really appreciate the help!

Thanks.
Ray

 

[RayportingMonkey]

OK - So maybe I don't suck as bad as I thought... I figured out the form and
can now change the image size to any of my predetermined sizes by clicking
the image and then making a choice on the user form.

What I do still need help with is the following:

1) The ability for the code to know which image was clicked and enter the
appropriate "Picture Name" as a variable into the ActiveSheet.Shapes("Picture
Name").Select line.

2) Is there a better way of de-selecting the image than grabbing a cell,
like I'm doing now? I can't leave the image selected...

Thanks - You all don't suck! Far from it in fact...

Later-
Ray

 

[JW]

Don't need to select the shape at all. Simply modify this to work
with your userform.
Sub Sizer(clickedShape As String, sz As String)
With ActiveSheet.Shapes(clickedShape)
MsgBox .Name
Select Case sz
Case "Larger"
.Height = 431.25
.Width = 356.25
Case "Large"
.Height = 279.75
.Width = 231#
Case "Minimize"
.Height = 86.25
.Width = 71.25
End Select
.LockAspectRatio = msoTrue
.Rotation = 0#
End With
End Sub

call like this:
Sub optionButton()
Sizer ActiveSheet.Shapes(Application.Caller).Name, "Minimize"
End Sub

 

[JW]

Don't need to select the shape at all. Simply modify this to work
with your userform.
Sub Sizer(clickedShape As String, sz As String)
With ActiveSheet.Shapes(clickedShape)
MsgBox .Name
Select Case sz
Case "Larger"
.Height = 431.25
.Width = 356.25
Case "Large"
.Height = 279.75
.Width = 231#
Case "Minimize"
.Height = 86.25
.Width = 71.25
End Select
.LockAspectRatio = msoTrue
.Rotation = 0#
End With
End Sub

call like this:
Sub optionButton()
Sizer ActiveSheet.Shapes(Application.Caller).Name, "Minimize"
End Sub

OK. Modified it for you


In the code that calls the UserForm to open when clicking the shape,
add this before your UserForm.Show line:
callerName=ActiveSheet.Shapes(Application.Caller).Name

In a separate standard module, place this variable and sub:
Public callerName As String

Sub Sizer(clickedShape As String, sz As String)
With ActiveSheet.Shapes(clickedShape)
MsgBox .Name
Select Case sz
Case "Larger"
.Height = 431.25
.Width = 356.25
Case "Large"
.Height = 279.75
.Width = 231#
Case "Minimize"
.Height = 86.25
.Width = 71.25
End Select
.LockAspectRatio = msoTrue
.Rotation = 0#
End With
End Sub

In your UserForm, place this (assuming that your option buttons are
called optLarge, optLarger, and optMinimize).

Private Sub optLarge_Change()
optHandler
End Sub

Private Sub optLarger_Change()
optHandler
End Sub

Private Sub optMinimize_Change()
optHandler
End Sub

Sub optHandler()
If optLarge=True Then
Sizer callerName, "Large"
ElseIf optLarger=True then
Sizer callerName, "Larger"
ElseIf optMinimize=True Then
Sizer callerName, "Minimize"
End If
End Sub

 

[RayportingMonkey]

Hey JW-

Actually the first post you offered worked well for me and is a bit simpler.
The only real difference I made was to exclude the msg.box, as the user won't
care what the name of the image is called - the variable just needs to be
passed.

This is sweet - I actually now have what I would call my first real
dashboard - I have tons of small charts and graphs with the ability to "zoom
in" on any metric the user wants to see!

Later-
Ray

 

[JW]

Glad to hear you got it sorted out Ray. Only reason I left the
message box in there was to give you a demonstration of what the
Application.Caller was doing.

Good luck in future projects.

 

[RayportingMonkey]

One other thing I had do to make this work effectively was to add the .ZOrder
msoBringToFront to the routine, because I may be "popping" images over other
images and I want the "magnified" image to be on top.

After playing with this a bit I have one minor and sporadic issue...

If I maximize an image and then minimize it, there are times that I am left
with what I would have to call a "ghost outline" of the larger image...
Scrolling the screen up and down or otherwise manipulating the cells seems to
"fix" this... It seems to be a screen refresh issue.

Is there a way to force a screen re-draw that might clear this up?