Multicoloured data bars: negative values and colour themes

Negative bars

One thing I noticed almost immediately with my version 1 was that negative values didn’t work correctly. If you set the default data bar range to include negative numbers you have the option to select a single colour for negative bars.

2014-07-08_085105

But if I’m going to have glorious technicolor on one side of the scale, I want it on both! So I tweaked and tested and finally got that working too.

2014-07-08_085119

 

Create your own colour theme

2014-06-29_143838

The themes are contained in the macro that fires when the dropdown combobox is changed.

Private Sub ThemeBox_Change()
 If ThemeBox.ListIndex = 0 Then Exit Sub
 Dim i As Integer, j As Integer, n As Integer
 j = ButtonCounter.Caption
 Dim cArray() As Variant, cArray1() As Variant, cArray2() As Variant, cArray3() As Variant, cArray4() As Variant, cArray5() As Variant, cArray6() As Variant, cArray7() As Variant
 cArray1 = Array(16379861, 16115648, 15916972, 15718553, 15387253, 14990676, 14659377, 13277984, 11108891, 8808213, 6639120)
 cArray2 = Array(16769416, 16765517, 16761873, 14065152, 12553984, 11173888, 9793536, 8413184, 6967296, 5586944, 4206592)
 cArray3 = Array(8210719, 12419407, 5066944, 5880731, 10642560, 13020235, 4626167, 192, 255, 49407, 7681280)
 cArray4 = Array(7889754, 44528, 13415776, 8219878, 7190379, 5342952, 4671686, 192, 255, 49407, 7681280)
 cArray5 = Array(15266303, 13821695, 12310783, 10931455, 9551871, 7975935, 6596351, 5085439, 3640575, 2195711, 2195711) 
 cArray6 = Array(10931455, 9551871, 7975935, 6596351, 5085439, 3640575, 2195711, 290815, 25318, 22218, 18860)
 cArray7 = Array(255, 5193716, 5853180, 5857017, 4626167, 11524246, 10210928, 6331904, 5342976, 4156160, 16777215) 
 
 cArray = Array(cArray1, cArray2, cArray3, cArray4, cArray5, cArray6, cArray7)
 i = ThemeBox.ListIndex - 1
 For n = 0 To j - 1
 Controls("Colour_" & n + 1).BackColor = cArray(i)(n)
 Next
End Sub

The steps to follow are:

  1. Add “, cArray*() as Variant” to the list of arrays at the top that starts with “Dim”, substituting * for the next unused consecutive number. In this example “, cArray8() as Variant”
  2. Just before the line that starts “cArray = Array…” add a line “cArray8 = Array(” then eleven numbers separated by commas and finish with “)”
  3. Add “, cArray8” to the list of arrays in brackets
  4. Also add a line to the “UserForm_Initialize” routine so your theme will be listed in the combobox: ThemeBox.AddItem “name of my theme”

If you already know all the code numbers of your favourite colours, then this next part is unnecessary. I don’t, so I made a button that saves all the current button colours to a txt file.

2014-07-08_103849

So when you get to step 2 above you just paste the 11 numbers from the txt file into the brackets.

Annunci

Informazioni su DiGiMac

I'm an ex-pat Londoner. I've been living near Lake Garda in North Italy for more than 30 years.
Questa voce è stata pubblicata in English e contrassegnata con , . Contrassegna il permalink.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...