Solved

Connecting an image to location

Posted on 2014-09-03
28
117 Views
Last Modified: 2014-09-05
Folks,
In the attached workbook on the Menu tab in the Description section is a rectangle shape. I have a tab next to the Menu tab where my images will be located. Right now I have only 1 image. This image is associated under the Category "Appropriate Charts" under the Topic "Line Charts".
I have a macros called InsertImage that I've started. I'm trying to use the Case Select in that macro.
When a user clicks on a Topic, in this case it would be "Line Charts" a descriptions appears. Below the description is where I would like to place a the image that is associated with Topic.
Excel-Dashboards-Rev-Test.xlsm
0
Comment
Question by:Frank Freese
  • 14
  • 14
28 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302298
Working on it.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302314
I think having the rectangle within the Description area will cause a problem with long descriptions such as the one for "Area Charts". I don't know if you can get the description to flow around the rectangle.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302332
I don't think this will be easy.

In your new macro you refer to ActiveSheet but I assume that you want the image to show up on Main when one of the Topics is single-clicked so "Main" is the ActiveSheet.  That's one problem, but the second is that the values in the Topics cells are generated via formulas so a given topic may be in a different row depending on the scrollbar, but let's see what I can do.
0
 

Author Comment

by:Frank Freese
ID: 40302391
The length of the description will be limited - no wrap around.
When I rem out the first two statements of the macro as well as the End Select statement it worked but only, only for that one image.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302400
Sorry but I don't know how to do what you want. However if you use an ActiveX Image control rather than a shape (which I think would be OK in this case), and if you stored individual pictures on your PC someplace then you could do this.
    Select Case intCategoryRow
        Case 3
            Cells(3, 7).Value = Sheets("Topics").Range("Introduction").Cells(intRow, 1).Offset(0, 1)
        Case 4
            Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
           Image1.Picture = LoadPicture("C:<Path to picture>\<picture name>")
        Case 5
            Cells(3, 7).Value = Sheets("Topics").Range("SecondaryAxis").Cells(intRow, 1).Offset(0, 1)
        Case 6
           Cells(3, 7).Value = Sheets("Topics").Range("SmoothingData").Cells(intRow, 1).Offset(0, 1)
        Case 7
            Cells(3, 7).Value = Sheets("Topics").Range("Sparklines").Cells(intRow, 1).Offset(0, 1)
        Case 8
            Cells(3, 7).Value = Sheets("Topics").Range("FormattingTricks").Cells(intRow, 1).Offset(0, 1)
        Case 9
            Cells(3, 7).Value = Sheets("Topics").Range("WinLossDrawCondFormatting").Cells(intRow, 1).Offset(0, 1)
       Case 10
            Cells(3, 7).Value = Sheets("Topics").Range("DynamicLabels").Cells(intRow, 1).Offset(0, 1)
    End Select

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302403
When I rem out ....
You're showing your age sir.

Let me see if I can generalize that.
0
 

Author Comment

by:Frank Freese
ID: 40302474
Showing my age? At least I wasn't around when they found the original "bug" in the computer :)
Let me ask this. In this code:
Sub InsertImage()

' InsertImage Macro

  Select Case Application.ActiveSheet.Name
        Case "LineCharts"
            ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
            Range("F3:F23").Select
            Sheets("Images").Select
            Selection.Copy
            Sheets("Menu").Select
            Range("F3:F23").Select
            ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
            ActiveSheet.Paste
            Range("C3").Select
  End Select
End Sub

Open in new window

If I insert this macro when the user clicks on the Topic selected (I'm not sure where to place it) and there was a way to get the name of the sheet the Topic references to (which I don't know how to do) then each argument under the Case statement would be modified to get the right picture. Does that make sense?
I believe in you!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302475
Okay got it!

Remove your "Picture 1" shape from the Menu sheet.

Then in Menu's Selection change do this
    
    ' Remove the old picture
    ActiveSheet.Shapes(Shapes.Count).Cut

    Select Case intCategoryRow
        Case 3
            Cells(3, 7).Value = Sheets("Topics").Range("Introduction").Cells(intRow, 1).Offset(0, 1)
        Case 4
            Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
           InsertImage intRow
        Case 5
            Cells(3, 7).Value = Sheets("Topics").Range("SecondaryAxis").Cells(intRow, 1).Offset(0, 1)
        Case 6
           Cells(3, 7).Value = Sheets("Topics").Range("SmoothingData").Cells(intRow, 1).Offset(0, 1)
        Case 7
            Cells(3, 7).Value = Sheets("Topics").Range("Sparklines").Cells(intRow, 1).Offset(0, 1)
        Case 8
            Cells(3, 7).Value = Sheets("Topics").Range("FormattingTricks").Cells(intRow, 1).Offset(0, 1)
        Case 9
            Cells(3, 7).Value = Sheets("Topics").Range("WinLossDrawCondFormatting").Cells(intRow, 1).Offset(0, 1)
       Case 10
            Cells(3, 7).Value = Sheets("Topics").Range("DynamicLabels").Cells(intRow, 1).Offset(0, 1)
    End Select

Open in new window


Finally replace your InsertImage with this

Sub InsertImage(intRow As Integer)

' InsertImage Macro

    Application.ScreenUpdating = False
    
    Sheets("Images").Activate
    
    Select Case intRow
        Case 4
            Sheets("Images").Shapes("Picture 1").Select
        Case Else
            Sheets("Menu").Select
            Exit Sub
    End Select
    
    Selection.Copy
    Sheets("Menu").Select
    ActiveSheet.Paste
    
    ' Position the copied picture
    Selection.ShapeRange.Left = Columns("G").Left + 50
    Selection.ShapeRange.Top = Rows(13).Top
    
    Range("C3").Select
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302485
Showing my age? At least I wasn't around when they found the original "bug" in the computer :)
Sadly, I was since while I don't know when Ada Lovelace found her bug, she got her PhD in 1937 when I was 4.
0
 

Author Comment

by:Frank Freese
ID: 40302499
That's funny - few people would know that story....
Do you have a copy of the workbook that works? The reason I ask is that in the InsertImage module the code skipped over line 11 in the above code.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40302508
I don't know where I got the idea but 4 relates to the "Horizontal Bar Charts..." topic, so change it to whichever one is correct.
0
 

Author Comment

by:Frank Freese
ID: 40302511
Augusta Ada King, Countess of Lovelace (10 December 1815 – 27 November 1852), born Augusta Ada Byron and now commonly known as Ada Lovelace, was an English mathematician and writer chiefly known for her work on Charles Babbage's early mechanical general-purpose computer, the Analytical Engine. Her notes on the engine include what is recognized as the first algorithm intended to be carried out by a machine. Because of this, she is often described as the world's first computer programmer.

I think you meant:
September 9, 1947

First Instance of Actual Computer Bug Being Found

At 3:45 p.m., Grace Murray Hopper records the first computer bug in her log book as she worked on the Harvard Mark II. The problem was traced to a moth stuck between a relay in the machine, which Hopper duly taped into the Mark II's log book with the explanation: “First actual case of bug being found.”
0
 

Author Comment

by:Frank Freese
ID: 40302523
I'm asking a lot on this and errors seems to be popping up at loading the attached workbook. You're welcomed to move forward or I can accept your solution at 40302400.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40302558
My fault. Line 3 in the Selection Change event needed to be embellished a bit. Here's a working wb. Remember that it shows the picture only if you click on "Horizontal Bar Charts..."
Q-28511134.xlsm
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

by:Frank Freese
ID: 40303681
Thank you kindly sir. See, when you believe it happens. I believe in you. They don't make them any better than you!
Job well done
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40303888
Always happy to help.
0
 

Author Comment

by:Frank Freese
ID: 40303978
Note:
Had to add this bit of code to clear the shape when a different Category was selected. Just an FYI

     If Shapes.Count > 3 Then
        ActiveSheet.Shapes(Shapes.Count).Cut
     End If
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40304091
Ok that makes sense.

I think however you should make a change everyplace that code now occurs. Let me explain why.

My If Shapes.Count > 3 Then line works now because not including the copied image there are 3 shapes on the sheet (cmdExit and the two scrollbars) and so Shapes(4) is the image copied from the "Images" sheet. But if in the future any other permanent shape is added, it would be Shape(4) and would be deleted by that code. Also if say cmdExit were removed, the copied image wouldn't be deleted because there would only be 3 shapes including the copied image. So here's what you should do.

In the InsertImage sub add line 20.
Public Sub InsertImage(intRow As Integer)
' InsertImage Macro

    Application.ScreenUpdating = False
    
    Sheets("Images").Activate
    
    Select Case intRow
        Case 4
            Sheets("Images").Shapes("Picture 1").Select
        Case Else
            Sheets("Menu").Select
            Exit Sub
    End Select
    
    Selection.Copy
    Sheets("Menu").Select
    ActiveSheet.Paste
    
    Selection.Name = "CopiedPicture"
    
    ' Position the copied picture
    Selection.ShapeRange.Left = Columns("G").Left + 50
    Selection.ShapeRange.Top = Rows(13).Top
    
    Range("C3").Select
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window


And change this code
    If Shapes.Count > 3 Then
        ActiveSheet.Shapes(Shapes.Count).Cut
     End If

Open in new window


to this.
    On Error Resume Next
    ActiveSheet.Shapes("CopiedPicture").Cut
    On Error GoTo 0

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 40304163
Makes sense
Changes made - this is neat stuff here.
Hopefully, I bring something's that are interesting and challenging.
Thank you
0
 

Author Comment

by:Frank Freese
ID: 40304223
Well....please look at the attached w/b with changes. I probably did something wrong, again. If you click on the Category - Appropriate Charts - and then under Topics - Line Charts and double click nothing happens.
DashboardTest.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40304969
Martin,
I found the problem and will post another question since I'm unsure how to resolve the issue.
0
 

Author Comment

by:Frank Freese
ID: 40305012
Martin,
I solved the problem I was going to ask about with the code below:  

Case 4
            Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
               InsertImage intRow
              Cells(Target.Row, gstrTopicCol).Select
I wanted to move the focus off the image and it took a little digging. If correct (I've not had any problems yet) then I'm starting to get my arms around your code.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40305017
An easier solution is just to remove the  Range("C3").Select line in InsertImage.
0
 

Author Comment

by:Frank Freese
ID: 40305031
I tried that but it had no effect in that the image copied was still selected.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40305041
All I can say is that it works for me.
0
 

Author Comment

by:Frank Freese
ID: 40305964
I went back to your comment again "An easier solution is just to remove the  Range("C3").Select line in InsertImage " just to make sure.
 
 Range("C3").Select

Open in new window

That did not work for me. I'm stuck in a loop.
What I added is not working
  Cells(Target.Row, gstrTopicCol).Select

Open in new window

but I know why because
gstrTopicCol

Open in new window

is going to the Topics tab and picking up column C, where I need column D from the menu tab.
So what I did was
Case 4
            Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
               InsertImage intRow
               gstrTopicCol = "D"
              Cells(Target.Row, gstrTopicCol).Select

Open in new window

and that seems to work.
Comments?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40306053
You shouldn't do that. gstrTopicCol is meant to be the column in the Topics sheet where the data for the Double-Click Topic column comes from and it's derived from the formula in the cell of the Double-Click Topic that the user clicks.

If you wanted to this instead you could because the effect is exactly the same
 Case 4
             Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
               InsertImage intRow
              Cells(Target.Row, "D").Select

Open in new window


but consider what's going on. I assume that in InsertImage the next last line is still Range("C3").Select which selects C3 on the Menu sheet, and in the code above you change it right back to D3. So instead of selecting that cell the way you are trying to do it, why not change the line in InsertIMage to be Range("D3").Select, or eliminate it completely (as I suggested earlier) because in other places we've been selecting cells like "A1" so that it looks better, but it this case the user starts the image insertion process by clicking a cell in D3:D23 and so a cell in column D is already selected. In fact, selecting D3 is wrong because doing that incorrectly selects that cell every time the user clicks any cell in D3:D23, and it would be better to leave the user's selection unchanged.
0
 

Author Comment

by:Frank Freese
ID: 40306082
I did try this once but I think back now and I believe I had it associate with Range and got all types of problems.
 Case 4
             Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
               InsertImage intRow
              Cells(Target.Row, "D").Select

Open in new window

Changing as you suggested are implemented and this works fine. I did eliminate the Range(:D3").Select in the InsertImage module.
Things are suddenly becoming much clearer. Thanks for your feedback.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now