To connect Case statement to load image

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
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
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
Frank FreeseAuthor Commented:
Forget the code - I forgot to remove it (surprised?)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Frank FreeseAuthor Commented:
Fantastic help and patience and I greatly appreciate it!
0
Martin LissOlder than dirtCommented:
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
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
I'll leave it for now - no problem
0
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
Appreciate it Marty - give yourself a raise (wish I could give more points)
0
Frank FreeseAuthor Commented:
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
Martin LissOlder than dirtCommented:
Do you understand everything I did?

Did you see the comment about the wrong image in InsertImage?
0
Frank FreeseAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
worked perfectly - thank you
having fun?  :)
0
Martin LissOlder than dirtCommented:
Yes actually.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.