Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
The values in Range("C1:D12") will hand entered as 1, 2, 3 or 4 only. Cells can be blank.
When I run the code the range is nicely CF'ed to; 1 = Red Diamond 2 = Yellow triangle 3 = Green circle 4 = Green Circle Blank = 'no icon' The problem is I want the 4 to = the icon green check MARK not a green CIRCLE. However, the green check mark is part of the xl3Symbol icon set. I would be happy with a Blue Star if I could make that happen, as long as it shows in the cell like the xl3Signs do. Anyone have a suggestion? Thanks. Howard Option Explicit Sub CreateIconSetCF() Dim cfIconSet As IconSetCondition Range("C1:D12").Select On Error Resume Next Set cfIconSet = Selection.FormatConditions.AddIconSetCondition cfIconSet.IconSet = ActiveWorkbook.IconSets(xl3Signs) With cfIconSet.IconCriteria(1) .Type = xlConditionValueNumber .Value = 1 .Operator = 3 End With With cfIconSet.IconCriteria(2) .Type = xlConditionValueNumber .Value = 2 .Operator = 3 End With With cfIconSet.IconCriteria(3) .Type = xlConditionValueNumber .Value = 3 .Operator = 3 End With With cfIconSet.IconCriteria(4) .Type = xlConditionValueNumber .Value = 4 .Operator = 4 End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
Am Wed, 5 Feb 2014 00:03:19 -0800 (PST) schrieb L. Howard: The values in Range("C1:D12") will hand entered as 1, 2, 3 or 4 only. Cells can be blank. When I run the code the range is nicely CF'ed to; 1 = Red Diamond 2 = Yellow triangle 3 = Green circle 4 = Green Circle Blank = 'no icon' The problem is I want the 4 to = the icon green check MARK not a green CIRCLE. However, the green check mark is part of the xl3Symbol icon set. with CF and xl3Symbol you can't get 4 symbols. Have a look he http://www.contextures.com/xlCondFormat03.html#Shape for "Create Coloured Shapes" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
Am Wed, 5 Feb 2014 00:03:19 -0800 (PST) schrieb L. Howard: 1 = Red Diamond 2 = Yellow triangle 3 = Green circle 4 = Green Circle Blank = 'no icon' copy the icons as pictures and insert them in order in Z1:Z4 Then with Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1:D12")) Is Nothing Or _ Target.Count 1 Then Exit Sub With Target If .Value 0 And .Value < 5 Then Shapes("Grafik " & .Value).Copy .Select ActiveSheet.Paste End If End With End Sub In German the shapes are named "Grafik" with an index. Change the name for your english system. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
have a look: https://skydrive.live.com/#cid=9378A...121822A3%21326 for workbook "Icons" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi again,
Am Wed, 5 Feb 2014 10:59:00 +0100 schrieb Claus Busch: https://skydrive.live.com/#cid=9378A...121822A3%21326 after posting the link I improved the code that you can change existing values. Make sure that you have the newest version. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
On Wednesday, February 5, 2014 1:59:00 AM UTC-8, Claus Busch wrote:
Hi Howard, have a look: https://skydrive.live.com/#cid=9378A...121822A3%21326 for workbook "Icons" Regards Claus B. -- Thanks, Claus. That should get me going, I'll work on a way to clear the shape if the cell is returned to blank. Regards, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
On Wednesday, February 5, 2014 3:07:31 AM UTC-8, Claus Busch wrote:
Hi again, Am Wed, 5 Feb 2014 10:59:00 +0100 schrieb Claus Busch: https://skydrive.live.com/#cid=9378A...121822A3%21326 after posting the link I improved the code that you can change existing values. Make sure that you have the newest version. Regards Claus B. -- Yes, much nicer, Thanks again. Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Thanks, Claus. That should get me going, I'll work on a way to clear the shape if the cell is returned to blank. Regards, Howard This little addition seems to work well to blank out a cell. Plus a small adjustment to the Top and Left. Thanks for the heavy lifting, appreciate it. Regards, Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
On Wednesday, February 5, 2014 4:11:04 AM UTC-8, L. Howard wrote:
Thanks, Claus. That should get me going, I'll work on a way to clear the shape if the cell is returned to blank. Regards, Howard This little addition seems to work well to blank out a cell. Plus a small adjustment to the Top and Left. Thanks for the heavy lifting, appreciate it. Regards, Howard Forgot to post the code, duh. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1:D12")) Is Nothing Or _ Target.Count 1 Then Exit Sub Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "Shp" & Target.Address(0, 0) Then shp.Delete End If Next With Target If .Value 0 And .Value < 5 Then For Each shp In ActiveSheet.Shapes If shp.Name = "Shp" & Target.Address(0, 0) Then shp.Delete Exit For End If Next Shapes("Grafik " & .Value).Copy .Select ActiveSheet.Paste With Selection .Top = Target.Top + 0.5 .Left = Target.Left + 4 .Name = "Shp" & Target.Address(0, 0) End With .Select End If End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
Am Wed, 5 Feb 2014 04:12:22 -0800 (PST) schrieb L. Howard: Forgot to post the code, duh. have another look Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
On Wednesday, February 5, 2014 4:28:24 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 5 Feb 2014 04:12:22 -0800 (PST) schrieb L. Howard: Forgot to post the code, duh. have another look Regards Claus B. -- I don't know what could be better. A question on the graphics/pictures in column Z. Did you make them from the Shapes menu on the main ribbon or did you import them? I am a bit surprised that Grafik works fine in the English version, I'm not sure what I would change it to for in English. Howard Shapes("Grafik " & .Value).Copy |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
Am Wed, 5 Feb 2014 10:01:41 -0800 (PST) schrieb L. Howard: Did you make them from the Shapes menu on the main ribbon or did you import them? I created two CFs with 3 and 4 symbols and copied the icons out of the cells. If "Grafik " works for you no changing is needed. But if you copy the icons as pictures and paste it you see how they are named in english version. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
On Wednesday, February 5, 2014 10:05:29 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 5 Feb 2014 10:01:41 -0800 (PST) schrieb L. Howard: Did you make them from the Shapes menu on the main ribbon or did you import them? I created two CFs with 3 and 4 symbols and copied the icons out of the cells. If "Grafik " works for you no changing is needed. But if you copy the icons as pictures and paste it you see how they are named in english version. Regards Claus B. -- I put the word Pictures in for Grafik and it did not error but also would not import the icon when a number was entered. So I went back to Grafik and then it did not work the same way using Picture failed. I'll go back to SkyDrive and start a new workbook. Howard |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
I put the word Pictures in for Grafik and it did not error but also would not import the icon when a number was entered. So I went back to Grafik and then it did not work the same way using Picture failed. I'll go back to SkyDrive and start a new workbook. Howard All is working well! Howard |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting .IconSets plus one other icon/symbol
Hi Howard,
Am Wed, 5 Feb 2014 10:29:55 -0800 (PST) schrieb L. Howard: All is working well! that is fine. But this morning I had no time and no calm hand working with Snipping Tool. Perhaps you make some nicer copies of the icons ;-) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Negative to Postiive Numbers w/3 Symbol Icon Se | Excel Discussion (Misc queries) | |||
Using the Icon Set for Conditional Formatting | Excel Worksheet Functions | |||
Conditional Formatting - Formulas with IconSets | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Conditional Formatting with the icon set | Excel Discussion (Misc queries) |