Solved

To connect Case statement to load image

Posted on 2014-09-07
19
120 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 46

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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Closing Comment

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

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 46

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 46

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
 
LVL 46

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 46

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 46

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 46

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 46

Expert Comment

by:Martin Liss
ID: 40310353
Yes actually.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

823 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