 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007INVOLVED - I Suck With User Forms...
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 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 > 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 [quoted text clipped - 54 lines] > Thanks. > Ray 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 - 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 [quoted text clipped - 72 lines] > > Thanks. > > Ray > Don't need to select the shape at all. Simply modify this to work > with your userform. [quoted text clipped - 98 lines] > > > Thanks. > > > Ray 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 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 > > Don't need to select the shape at all. Simply modify this to work > > with your userform. [quoted text clipped - 151 lines] > End If > End Sub 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. > Hey JW- > [quoted text clipped - 165 lines] > > End If > > End Sub 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? > Don't need to select the shape at all. Simply modify this to work > with your userform. [quoted text clipped - 97 lines] > > > Thanks. > > > Ray |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.