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]

Oh sorry,

I'm running Windows 7 and Excel 2007.

 

[Excelscared]

Is there nobody who can help me?

 

[Excelscared]

Please guys, help me out here.

I'm desperate,
eScared

 

[Excelscared]

Oh c'mon guys, please.
What do I have to do, to get an answer here.

 

[SuperFerret]

It would be helpful if we had something to look at, knowing that we cannot see your spreadsheet. There are ways to display them:
Excel jeanie or RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post or Borders-Copy-Paste

If you could show what you want to happen (even if you have a small section of sample data and colour it manually) you may get more of a response.

Reading your post I'm not entirely sure what: if 0 slots taken, then green, if < 1 slots taken, then yellow and if < 16 slots taken, then red means. Because <1 is 0 (and hence green) yes?

Edit: And a slightly belated Welcome to the Board <!-- / message --><!-- sig -->

 

[Excelscared]

Alright, my mistake.

This is how the day by day chart looks like... more or less. I had to do with OpenOffice, because I don't have Excel at at home but only at work. In the Excel version (2007) I have already included the toggle-buttons.

For the other sheet (overview) I just want to display availability per boat per month. So what you see are four cells (the boats) per day. These cells are meant to change colour according to the number of toggled buttons of the day sheets.

Well, I hope this helps. Please don't hesitate to ask for more.

Cheers,
e-scared

 

[SuperFerret]

It makes a bit more sense now

Ok, so your colour coding. On the sheet you've shown will aways be Red yes?

On your overview sheet, can you clarify the colour change criteria?

You say anything with 0 toggled is green, then is it anything between 1 and 15 that is Yellow and anything 16 is Red?

 

[Excelscared]

The sheet you see, is the day sheet. All fields are green by default before any of the units (boats) is booked/reserved. As soon as somebody books any of the 30 minute slots, they shall turn red.

Reserving in this respect means toggling the button = blocking the slot. In order to make this visible, the slot on the day sheet is supposed to turn red. On the overview sheet however, I want a slightly different effect. Here I want to use three different colours:
Green = no slot occupied
Yellow = 1 - 15 slots occupied
Red = more than 15 slots occupied

Basically, what I need to know is how I can make the toggle button create a value of 1, when toggled. Then I'd just create a summing formula that counts these values. The sum then is checked by the If-Then formula on the overview sheet that generates the output in form of a colour: green, yellow or red.

Therefore:
1. How do I make the Toggle-buttons create a value of 1 when toggled?
2. How do I count this?
3. How can I make the Toggle-buttons on the day sheet change the colour of the adjacent cells?
4. How do I make the overview cells change colour according to the respective If-Then formula?

Hope that's clearer.
Cheers
e-scared

 

[SuperFerret]

Ok, I saw this code on OzGrid (posted by GlennUK) which would do (1) the Toggle On value is 1, off is 0

Private Sub ToggleButton1_Click()
Range("[COLOR=red]B11[/COLOR]").Value = (Range("[COLOR=red]B11[/COLOR]").Value + 1) Mod 2
Me.OLEObjects(1).Object.Caption = IIf(Range("[COLOR=red]B11[/COLOR]").Value = 0, "On", "Off")
End Sub

But if you're just using 1 or 0 for the toggle sections, do you really have to use toggle buttons? Could you not just insert 1's?

(2) You could probably use COUNT or COUNTIF dependent on the layout of your overview

(3) Conditional Formatting - most likely Formula Is =B11=1 then format to Red

(4) Conditional Formatting again, probably a nested IF

Just my suggestions, someone smarter may come up with something cleverer