Toggle button, color control

[Excelscared]

Hey everybody,

this is my first post and my chosen name is not because I'm such an Excel genius...

Anyhow, for the last three days I'm trying to figure out how to handle these silly toggle buttons. It's the first time that I try to deal with macros (can you actually already call them macros?) and I'm feeling pretty lost by now.

---------------

What I have in mind is a simple spread sheet to help with reservations.

The first sheet (Availability overview) in the workbook is supposed to only display available slots by means of color coding (Every single unit (4), for every single day):
- green = all day free
- yellow = difficult but still possible
- red = all slots occupied

The following sheets display single days with all 4 units. Here I want to fill in the actual reservation. Every unit has two colums with 30 minute slots for the entire day: One column with individual toggle buttons for each slot and one column next to it for data such as the name, etc.
The toggle button is supposed to do two things:
- 1st it shall change the color of the adjacent right cell to red, and
- 2nd it shall change the color of the corresponding cell on the first sheet (Availability overview) between green-yellow-red, according to an if-then formula: if 0 slots taken, then green, if < 1 slots taken, then yellow and if < 16 slots taken, then red.

How do I get there? Please mind my comlpete incompetence regarding Excel terminology and macros coding.

Thanks in advance,
e-Scared

 

[Excelscared]

Here's how the overview sheet will look like... more or less:

 

[Excelscared]

Ahh, that's starts to look like a solution. Thanks.
Unfortuantely, I can't test it right now but only tomorrow.

Anyhow, where do I change the toggle on/off value? In the parameters?
And also: Where do I enter the code?

Thanks a lot. I'll write tomorrow how this first part works.

Cheers,
e-scared

 

[SuperFerret]

Ok. I've played with this to try and quickly replicate your sheet

The code I used was:

Private Sub ToggleButton1_Click()
Range("D4").Value = (Range("D4").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D4").Value = 0, "Book", "Booked")
End Sub

If you insert a Toggle Button, then right click and View Code. Paste this into the large white space. Change the D4 values to whatever the cell next to your Toggle Button is.

This (on mine at least) toggles the value 0/1 in the cell, and at the same time the button label changes from Book to Booked accordingly. Then I just used Conditional Formatting in the cell =D4=1 format (fill and font) RED (I also used =D4=0 and set font to the background colour to make the cell appear blank either way)

Someone may have a more elegant solution but I hope this gets you started

 

[Excelscared]

Hey Ferret,

the code for the buttons works almost fine. However there is something funny happening.

Problem:
If I press the first button, everything is fine. Now if I press the second button (under the first), the caption of the first turns into "book" again and doesn't stay "booked". Also, the numbers seemed to work out fine in the beginning, but for some reason it reads "2" after summing up all the values even though all buttons are toggled off (state: book).

What's happening there?

Here's the code:

Private Sub ToggleButton1_Click()
Range("D4").Value = (Range("D4").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D4").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton2_Click()
Range("D5").Value = (Range("D5").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D5").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton3_Click()
Range("D6").Value = (Range("D6").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D6").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton4_Click()
Range("D7").Value = (Range("D7").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D7").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton5_Click()
Range("D8").Value = (Range("D8").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D8").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton6_Click()
Range("D9").Value = (Range("D9").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D9").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton7_Click()
Range("D10").Value = (Range("D10").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D10").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton8_Click()
Range("D11").Value = (Range("D11").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D11").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton9_Click()
Range("D12").Value = (Range("D12").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D12").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton10_Click()
Range("D13").Value = (Range("D13").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D13").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton11_Click()
Range("D14").Value = (Range("D14").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D14").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton12_Click()
Range("D15").Value = (Range("D15").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D15").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton13_Click()
Range("D16").Value = (Range("D16").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D16").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton14_Click()
Range("D17").Value = (Range("D17").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D17").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton15_Click()
Range("D18").Value = (Range("D18").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D18").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton16_Click()
Range("D19").Value = (Range("D19").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D19").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton17_Click()
Range("D20").Value = (Range("D20").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D20").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton18_Click()
Range("D21").Value = (Range("D21").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D21").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton19_Click()
Range("D22").Value = (Range("D22").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D22").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton20_Click()
Range("D23").Value = (Range("D23").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D23").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton21_Click()
Range("D24").Value = (Range("D24").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D24").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton22_Click()
Range("D25").Value = (Range("D25").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D25").Value = 0, "Book", "Booked")
End Sub
Private Sub ToggleButton23_Click()
Range("D26").Value = (Range("D26").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("D26").Value = 0, "Book", "Booked")
End Sub

 

[Excelscared]

It seems to be somehow related to the first and the last toggle buttons. I could see the "1" remaining after the buttons were toggled to off.

Yet another thing:
How can I make "Conditional formatting" check adjacent cells to format itself. Or the other way around, check itself and format another cell?

 

[SuperFerret]

If I press the first button, everything is fine. Now if I press the second button (under the first), the caption of the first turns into "book" again and doesn't stay "booked". Also, the numbers seemed to work out fine in the beginning, but for some reason it reads "2" after summing up all the values even though all buttons are toggled off (state: book).

Hmm.. I get that on mine too. Does it have to have the Book/Booked? I took that out and don't get any further issues with it.
I changed my code to

Private Sub ToggleButton1_Click()
Range("D4").Value = (Range("D4").Value + 1) Mod 2
End Sub

I don't have any problems with the 1's remaining in place after the toggle is switched off have you tried switching them all off and just clearing the contents of those cells?

As for the Conditional Formatting, have you got the Toggle in the same cell as your value is appearing? Or is it next to it?

<TABLE style="WIDTH: 171pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=228><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 24pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32 width=64>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 19pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=75>TOGGLE</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OR</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE (1)</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE (1)</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TOGGLE</TD></TR></TBODY></TABLE>

Mine also seems to SUM ok when all the Toggles are set to OFF (I get 0)

 

[Excelscared]

Great,

this works beautifully. I've already spreaded into all colums. I've got 92 toggle buttons per sheet now... I just hope MS Excel will manage.

Anyhow, next step on the daysheet:
How do I make the adjacent (D4 --> E4) cell change colour when D4 was toggled? The generated value is hidden under the button, D4 that is.
What I would like, is to make E4 change colour when D4+D5=2. This is in order to make room for an uncoloured slot at the end of the booked time... in order to prepare for the next customer.
Basically, I only want that E4 turns red/dark pink, when D4 AND D5 are toggled, E5 when D5 and D6, and so on...

Cheers,
e-scared

 

[Excelscared]

Oh, a little add:

It must still be possible to WRITE into the cells that change colour.

Hope that doesn't mess up things.
e-scared

 

[SuperFerret]

Glad you got it working

Right, select the first cell you want to colour. Then select Conditional Formatting, you want Formula Is and type =AND(D4=1,D5=1) and select your formatting (red/pink/whatever)

This will check that BOTH D4 and D5 equal 1 and highlight the cell you selected once that happens. So you should get something like my crude attempt at a mock up (I can't use the HTML Maker at work):

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=217><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><TBODY><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 24pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32 width=78>TOGGLE ON</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=139>(cell highlighted red)</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE ON</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=32>TOGGLE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>

Is that what you want?

You should be able to type in the highlighted cell as well with no problems

 

[Excelscared]

Sorry mate,

I can't find that formula. I have "Equal to", "Greater than", "Less than", etc. Can't find anything that says "Is".

Maybe I'm looking at the wrong place? Excel 2007.