Solved

To connect Case statement to load image

Posted on 2014-09-07
19
113 Views
Last Modified: 2014-09-08
Folks,
I've been trying to simplify a series of procedures in one module to a single procedure using Case in the  module. The module is named modInsertImage and within the module are numerous like procedures. The module is accessed in the Worksheet_SelectionChange event.
I can't get there?
Dashboard-rev.xlsm
0
Comment
Question by:Frank Freese
  • 10
  • 9
19 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40309057
Let's assume that the topics were not broken down by category and so all the topics were in one list and you wanted to copy the image for "Sparklines in Tables". I'm sure you can see how in that situation that you'd only need one InsertImage routine and there would be one "Case" for each topic. If you clicked the first topic intRow would be 1 and if you clicked "Sparklines in Tables" intRow would be 23 (I counted), so that Case would be 23. In our situation with separate topic lists, clicking the first entry in group always winds up with intRow being 1 and so clicking "Sparklines in Tables" makes intRow = 5. So in order to create a value that we can use as our index to InsertImages that performs in the same way it would if we had an all-in-one list we need to add 18 to intRow, and guess what, intCatFactor is 18 since it's a count of the topics in all previous categories! So now all you need to do to copy the "Sparklines in Tables" image is do

InsertImage intRow + intCatFactor

If that doesn't help please tell me which parts you don't understand
0
 

Author Comment

by:Frank Freese
ID: 40309079
Sometimes I think there is more live intelligence on the other side of the keyboard than the human side. Stupid me did not align my case statements correctly in the InserImage module. Once I did that (after reading and re-reading your comments) a light came on. It looks like I made something easy so hard.
Thank you sir, I can sleep tonight.
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

Case 4
            Cells(3, 7).Value = Sheets("Topics").Range("AppropriateCharts").Cells(intRow, 1).Offset(0, 1)
               InsertImage intRow + intCatFactor
               Cells(Target.Row, "D").Select

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 40309080
Forget the code - I forgot to remove it (surprised?)
0
 

Author Closing Comment

by:Frank Freese
ID: 40309081
Fantastic help and patience and I greatly appreciate it!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40309087
Here's a picture of what I'm talking about.
intCatFactor
So in the Worksheet_Selection change you would simplfy your code like this. Note that the first two lines are not repeated.

    InsertImage intRow + intCatFactor
    Cells(Target.Row, "D").Select

    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)
        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)
       Case 11
            Cells(3, 7).Value = Sheets("Topics").Range("NonContRange").Cells(intRow, 1).Offset(0, 1)
       Case 12
            Cells(3, 7).Value = Sheets("Topics").Range("EmbedObjects").Cells(intRow, 1).Offset(0, 1)
       Case 13
            Cells(3, 7).Value = Sheets("Topics").Range("InCell").Cells(intRow, 1).Offset(0, 1)
       Case 14
            Cells(3, 7).Value = Sheets("Topics").Range("Panel").Cells(intRow, 1).Offset(0, 1)
       Case 15
            Cells(3, 7).Value = Sheets("Topics").Range("StepCharts").Cells(intRow, 1).Offset(0, 1)
    End Select
End If

Open in new window


And here is InsertImage
Public Sub InsertImage(intRow As Integer)
' InsertImage Macro

    Application.ScreenUpdating = False
    
    Sheets("Images").Activate
    
    Select Case intRow
        Case 1
            Sheets("Images").Shapes("Picture 1").Select
        Case 2
            Sheets("Images").Shapes("Picture 2").Select
        Case 3
            Sheets("Images").Shapes("Picture 3").Select
        Case 4
            Sheets("Images").Shapes("Picture 4").Select
        Case 5
            Sheets("Images").Shapes("Picture 5").Select
        Case 6
            Sheets("Images").Shapes("Picture 6").Select
        Case 7
            Sheets("Images").Shapes("Picture 7").Select
        Case 8
            Sheets("Images").Shapes("Picture 8").Select
        Case 9
            Sheets("Images").Shapes("Picture 9").Select
        Case 10
            Sheets("Images").Shapes("Picture 10").Select
        Case 11
            Sheets("Images").Shapes("Picture 11").Select
        Case 12
            Sheets("Images").Shapes("Picture 12").Select
        Case 13
            Sheets("Images").Shapes("Picture 13").Select
        Case 14
            Sheets("Images").Shapes("Picture 14").Select
        Case 15 To 22 ' Break this up into individual cases
            '
        Case 23
            Sheets("Images").Shapes("Picture 16").Select
        ' Add other cases here
        Case Else
            Sheets("Menu").Select
            Exit Sub
    End Select

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40309090
Once I did that (after reading and re-reading your comments) a light came on. It looks like I made something easy so hard.
Sorry but that's still not correct. See my previous post.
0
 

Author Comment

by:Frank Freese
ID: 40309103
We're not exactly on the same page yet - for the InsertImage my Case did not start at Case 1 but Case 4 (since I had no images for the first Category that contained 3 rows). Therefore, in InsertImage it looks like this to start
Select Case intRow 
        Case 4
            Sheets("Images").Shapes("Picture 1").Select

Open in new window

I went further by testing this for "Sparklines" since that Category and multiple Topics.
   Case 7
            Cells(3, 7).Value = Sheets("Topics").Range("Sparklines").Cells(intRow, 1).Offset(0, 1)
               ImageSparklines intRow
               Cells(Target.Row, "D").Select

Open in new window

and everything seems to be OK. Have I done something wrong?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40309106
Not wrong but you still have multiple InsertImage clones and you don't need them. Let me modify the workbook you posted in this thread.
0
 

Author Comment

by:Frank Freese
ID: 40309116
Thanks
BTW there are two things I don't know why (just 2 you say?
1. Look at the Images tab that is very disorganized and the Range A3:A23. Is that something you did? I'm not sure why it's there but I haven't done anything with it.
2. You suggest I replaced this  
 If Shapes.Count > 3 Then
        ActiveSheet.Shapes(Shapes.Count).Cut
     End If

Open in new window

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

Open in new window

in thread ID: 40304091.
I did not do that but I can't recall why not? It's in the Wroksheet_SelectChange event
Dashboard-rev.xlsm
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 40309118
Look at the Images tab that is very disorganized and the Range A3:A23. Is that something you did?
Not knowingly but sometimes strange things happen while testing.
0
 

Author Comment

by:Frank Freese
ID: 40309121
I'll leave it for now - no problem
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40309171
Here's an updated workbook based on the one you posted in Post ID 40309116. All the changes are marked with "Q_28513421". I earned my "pay" here:) Please look at all the changes and if you don't understand something please ask. You should also delete the Imagexxxxxxx subs that I commented out so that all that dead code isn't hanging around.
Q-28513421.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40310043
Appreciate it Marty - give yourself a raise (wish I could give more points)
0
 

Author Comment

by:Frank Freese
ID: 40310087
I made the changes to your workbook. I would have never thought to do what you did. Although what I had worked it made Excel work harder.
Everything is dandy - thank you so very much. It look great!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40310145
Do you understand everything I did?

Did you see the comment about the wrong image in InsertImage?
0
 

Author Comment

by:Frank Freese
ID: 40310176
The wrong image is actually ok - it might look out of place but it is used for two different types of problems
I go sucked into tunnel vision with the way things were done and missed the obvious. Sometimes you can take a mule to water yet he's looking for wine. I was that mule. What you did make a lot of sense, now that I can see it.
I promise you I do not stay up late at night and think of these things simply to challenge EE. A year ago I was just getting into VBA (though I'd rather build in Access or Visual Basic) and today I feel so much further along. But I'll spend hours reading and studying just to understand many of the principles. You've been a big help and I appreciate it. Hopefully, you got yourself educated also.
Thank you again.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40310240
You may have noticed that when you select a new Category that there's a slight screen flicker. That's happening because InsertImage is being done twice in that situation. To stop that from happening go to Menu's Worksheet_SelectonChange code and change

    Range("D3").Select
    Set Target = ActiveCell

Open in new window

to
    Application.EnableEvents = False
    Range("D3").Select
    Set Target = ActiveCell
    Application.EnableEvents = True

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 40310338
worked perfectly - thank you
having fun?  :)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40310353
Yes actually.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

744 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

13 Experts available now in Live!

Get 1:1 Help Now