PDA

View Full Version : help to Copy symbols indicators as per range parameter set.



VISHAL120
08-01-2013, 05:51 AM
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.

:banghead::banghead::banghead:

SamT
08-01-2013, 07:38 AM
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
08-01-2013, 11:41 AM
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
08-02-2013, 03:40 AM
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
08-02-2013, 04:38 AM
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 :banghead:

Paul

VISHAL120
08-02-2013, 05:35 AM
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
08-02-2013, 05:40 AM
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
08-02-2013, 05:42 AM
Hi sorry for the post i copy paste directly. can you please see the attachement below thanks to advise.

SamT
08-02-2013, 05:52 AM
Change Icon Names

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

Paul_Hossler
08-02-2013, 06:07 AM
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
08-02-2013, 06:42 AM
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
08-10-2013, 02:34 AM
i have checked this on excel 2007 and its works correctly but not on excel 2003. hence its solved.

Paul_Hossler
08-10-2013, 06:04 AM
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.