Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format Negative to Postiive Numbers w/3 Symbol Icon Se kms77 Excel Discussion (Misc queries) 2 March 4th 10 02:07 AM
Using the Icon Set for Conditional Formatting Gino59 Excel Worksheet Functions 1 February 11th 10 03:12 AM
Conditional Formatting - Formulas with IconSets genxgeek Excel Discussion (Misc queries) 1 July 15th 09 05:03 PM
Formatting Conditional Formatting Icon Sets The Rook[_2_] Excel Discussion (Misc queries) 3 March 7th 09 09:48 PM
Conditional Formatting with the icon set Tubthumper Excel Discussion (Misc queries) 1 November 5th 07 11:12 AM


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"