Conditional formatting problem excel 2007 [SOLVED]

[WouterJ]

Hello,


At my work, we use excel 2007.


I tried to make a little KPI dashboard with a List box (in which the users can choose several KPI's).

One of the KPi's is shown as a percentage. Of course, I tried to make some conditional format that changes the number from a normal number to a percentage whenever that KPI is selected.


See file (small example):

https://www.dropbox.com/sh/t628bvtkdteo59w/ylZZPWzYFK/Book3.xlsx


The problem is as follows:


Whenever I select the KPI's that are not as percentage, the formatting shows the 'normal' format. When I select the KPI that is shown as percentage, the format changes to a percentage. But when I change back to a 'normal' KPI, the format is still as percentage, when it clearly should change back to a 'normal' number.


I'm only getting this weird thing in excel 2007. Whenever I use excel 2010, the formatting seems to work properly.


Any thoughts about how to work around this?


Thanks in advance!


cheers,


Wouter

 

[SirJB7]

Hi, WouterJ!

Haven't checked your file, but should this help?

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/conditional-formatting-not-displaying-correctly-in/22b49dc6-9a6e-4ef4-9ed2-0d53c8a77453

http://www.mrexcel.com/forum/excel-questions/590693-visual-basic-applications-conditional-formatting-2007-vs-2010-a.html

Regards!

 

[NARAYANK991]

Hi Wouter ,


Try this code in a module :

[pre]

Sub ListBox1_Change()
[A13] = ActiveSheet.Shapes(1).ControlFormat.ListIndex
If ActiveSheet.Shapes(1).ControlFormat.ListIndex = 5 Then
ActiveSheet.Range("D13:G13").NumberFormat = "00 %"
Else
ActiveSheet.Range("D13:G13").NumberFormat = "#,##0"
End If
End Sub

[/pre]
Narayan

 

[WouterJ]

Thanks for all the replies


I'll check on monday if these solutions will help me.


cheers,


Wouter

 

[WouterJ]

Hello,


I've chosen to work with the code Narayank optioned, and I've assigned the macro to the Listbox where I can choose the KPI's.


Bu, whenever I click on the KPI I want, the macro seems to change my choice into the first KPI that is in the listbox (in my example file: whenever I choose option b,c,d, or e, the macro changes this to a).


If I assign the macro to a button (no to the listbox) and run it, this happens aswell.


(I'm not really experienced with VBA, but I can manage to understand the script above)

 

[NARAYANK991]

Hi Wouter ,


The code was specific to the sample file you had uploaded ; the following statements in it :

[pre]

[A13] = ActiveSheet.Shapes(1).ControlFormat.ListIndex
If ActiveSheet.Shapes(1).ControlFormat.ListIndex = 5 Then

[/pre]
used the index number of 1 for the listbox , since this was the only shape in the worksheet ; if your actual file contains other shapes , you need to find out the shape index number of the listbox , and change the 1 to that.


I had tried out the macro on your sample file , and it did not behave the way you are describing ; is it possible you can upload your actual file , or do you want me to upload your original sample file with the macro in it ?


Narayan

 

[WouterJ]

Hey Narayank,


I've got it .


I've given the listbox a name and added the name instead of the index number (i couldnt find it), which works just fine


Thanks for your help!


cheers,


Wouter