help to Copy symbols indicators as per range parameter set.

[VISHAL120]

Hi,


We have a performance monitoring file where place indicators on every parameters being controlled that is for efficiency, quality and rejects which helps us a lot in terms of visual management control.

Every indicator is copied from the <<indicators sheet>> and place on the <<monthly sheet>> according to the specific range defined for efficiency, quality and rejects.

Right now we are doing it manually that is copy and paste and this takes a lot of time and also sometimes we do make errors. For info we have around 50 modules that we need to copy paste for efficiency, quality and rejects.

Please can someone just guide me on how I can do it with VBA so as to not loose time by copy paste and also eliminate the risk of errors.

Many thanks for a start guidance.


Please see the attached file to have the idea.


Thanking in advance for a quick feeback.

[SamT]

In the attached, I've listed all the shapes on "Indicators" with the index number, the current name and a suggested new name. You will have to write a one time code to individually rename them.

Note that each shape in your workbook adds more than 1.5 KB to the file size. After deleting all shapes in the book, its size was only 52.5KB. Compare that to the attachments size.

The code in the attachment, "Indicators" code page, shows how to manipulate shapes by cell.
I used this code to delete all the shapes.

Sub DelShp()
Dim Shp As Object

For Each Shp In Sheet1.Shapes
  Shp.Delete
Next Shp

For Each Shp In Sheet8.Shapes
  Shp.Delete
Next Shp
End Sub

I would suggest that you use WingDings or other symbol font in custom colors and sizes for indicators.

You can also use VBA's RGB function to make custom graduated color Constants to use as cell backgrounds.

[Paul_Hossler]

I manually named the Indicator icons since there was only a handful. That way I could refer to them

Option Explicit
Dim wsIcon As Worksheet, wsData As Worksheet
Sub InsertIcons()
    Dim rData As Range, rCell As Range
    Dim shpIcon As Shape

    'setup
    Set wsIcon = ThisWorkbook.Worksheets("INDICATORS")
    Set wsData = ThisWorkbook.Worksheets("MONTHLY")
    Application.ScreenUpdating = False

    'delete old shapes
    For Each shpIcon In wsData.Shapes
        If shpIcon.Type <> msoChart Then shpIcon.Delete
    Next
    
    'effeciency
    Set rData = wsData.Range("A8").CurrentRegion
    For Each rCell In rData.Columns(3).Cells
        If rCell.Value >= 0.9 Then
            Call PasteIcon("EFF1", rCell)
        ElseIf rCell.Value >= 0.89 Then
            Call PasteIcon("EFF2", rCell)
        ElseIf rCell.Value >= 0.86 Then
            Call PasteIcon("EFF3", rCell)
        ElseIf rCell.Value >= 0.83 Then
            Call PasteIcon("EFF4", rCell)
        ElseIf rCell.Value >= 0.8 Then
            Call PasteIcon("EFF5", rCell)
        Else
            Call PasteIcon("EFF6", rCell)
        End If
    Next
        
        
    'AQL fail
    Set rData = wsData.Range("E8").CurrentRegion
    For Each rCell In rData.Columns(3).Cells
        If rCell.Value >= 0.1 Then
            Call PasteIcon("QUAL4", rCell)
        ElseIf rCell.Value >= 0.08 Then
            Call PasteIcon("QUAL3", rCell)
        ElseIf rCell.Value >= 0.06 Then
            Call PasteIcon("QUAL2", rCell)
        Else
            Call PasteIcon("QUAL1", rCell)
        End If
    Next

    'Rejects        
    'I don't see what the rules are    

    Application.CutCopyMode = False
    Application.ScreenUpdating = False
        
        
End Sub
Private Sub PasteIcon(IconName As String, DestCell As Range)
    wsIcon.Shapes(IconName).Copy
    DestCell.Offset(0, 1).Select
    wsData.Paste
    With Selection
        .Height = DestCell.Offset(0, 1).Height
        .Width = DestCell.Offset(0, 1).Width
    End With
    Application.CutCopyMode = False
End Sub

[VISHAL120]

Hi Thanks SamT and Paul for your precious time.

Am using the method shown by Paul its working but the only problem i see when its pasting its not placing the indicators on the required column and cell example it shall placed the indicator on cell D8 but instead its placing aside in between. am tyring to see on the code to solve but once again can you please assist me on that.

thanks in advance.

by the way can someone tell why every time i shall log in even i have said remember me. and also am not having any mails when we have reply for the post.

thanks again

[Paul_Hossler]

1. Which is the right column? It seems to do the same thing as in your example. See the picture. Tell me where the icon should go and I can fix it

2. Under the [Sign In] there's a check box that says something "Remember Me". I'd check it out out but I can't figure out how to sign out

Paul

[VISHAL120]

Hi,

my is coming like below screen shots but as you shown on your attachement like for efficiency its placed on Column D8 and quality on H8.
can you also explained me how to have renamed the indicator icon please. thanks for help.

see the screen shot below.

[VISHAL120]

Hi,

my is coming like below screen shots but as you shown on your attachement like for efficiency its placed on Column D8 and quality on H8.
can you also explained me how to have renamed the indicator icon please. thanks for help.

see the attached screen shot

[VISHAL120]

Hi sorry for the post i copy paste directly. can you please see the attachement below thanks to advise.

[SamT]

Change Icon Names

Shapes("oldName").Name = "newName"
'Repeat for each Icon

[Paul_Hossler]

1. Possibly there's a difference in Excel versions, but I don't have any issue with the icon being placed correctly

2. To 'name' a shape, select it and they enter the name in the area to the left of the formula bar -- see the screenshot

Select 'Winner' and enter the Name -- I used EFF1

Paul

[VISHAL120]

thanks to help.

Hi am actually using excel 2003 and every time am trying its becoming same as i have shown to you. is there not anything that i must do please.

[VISHAL120]

i have checked this on excel 2007 and its works correctly but not on excel 2003. hence its solved.

[Paul_Hossler]

I am glad that you figured it out. Possibly the technique can be made to handle both versions (??). VBA can determine which version is in use and you could handle the positioning differently.