Command buttons that change colour

[delexcel]

In summary:

My query is that I would like to change the colour of a command button when another command button is pressed.

The purpose of the document is for a quiz night.

The idea of the command buttons that change colour is that they are to visually indicate which rounds have been completed.

In detail:

- Sheet 1 is a summary sheet of the quiz results
- On it, there are 10 command buttons (titled Round 1-10) which when pressed navigate the user to one of 10 worksheets

- Sheets 2-11 are the 10 worksheets noted above (10 rounds of the quiz)

- The idea I had was to have an individual command button in each of the 10 quiz rounds worksheets (2-11), which is to be pressed at the completion of each round.

- I had envisaged that the code would recognise when the user has pressed the button
- modify the heading in cell A1 of that particular page, say from 'Round 1' to 'Round 1 Complete'
- as well as modifying the colour of the relevant command button in the summary sheet (Sheet 1), say greying the button out.

Can anyone help with some code for the above?

 

[sijpie]

Greying the button out is easy. Changing the colour of a normal button is more difficult, if you want to do that the easiest is to make images of buttons in the required colours, and hide or show them. (You can link macros to images as well as to buttons).

However let's look at greying out a button:

...
            With Sheets("Sheet1")
                .Buttons("Button 1").Enabled = False
                .Buttons("Button 1").Font.ColorIndex = 16
            end with
...

 

[Sektor]

Are buttons ActiveX or Forms?

 

[delexcel]

*Bump* - is anyone able to provide some assistance on this?

Sektor - The buttons can either be an object or a command button, it doesnt matter from my end.

 

[BrandonWLH]

This isn't a simple post you some code type of problem.

You could do this whole thing without a single command button though.

You can capture the mouse click in a cell on a spreadsheet with the SelectionChange event. You check the location of the selection change using the provided variable Target and if it matches the location of the 'answer' then you can just use Target.Interior.Color to change the clicked on cells color.

I know this works because I wrote an employee evaluation program that did just this, I cannot provide this due to an NDA though, sorry. You would click in the cell for either above, below or meeting expectations in various categories and it would highlight the cell you chose and leave it highlighted and then it would grey out the other 2 choices.

For sheet navigation, just put text in a cell and hyperlink it to the other locations in the workbook and set the font to something better looking than underlined blue, it won't effect its ability to link. U can border and gradient shade for the appearance of a 'button'.

 

[sijpie]

delexcel what is wrong with the code and the ideas I provided?

 

[Sektor]

Sektor - The buttons can either be an object or a command button, it doesnt matter from my end.

But it matters for code.

 

[delexcel]

First of all thank you to all that have replied, it is very much appreciated.

Sijpie - thank you for your code to change the buttons color, however i am still struggling to write the macro to complete the remainder of what i was after.

Sektor - I realise it matters for code however what I meant was that it is not important from my end what format the button is, so whichever is the simpler to code/most effective to use I guess.

Brandon - thanks also for your answer. Ideally I would like to use buttons as that is what I am most familiar with though I can see the benefits to using hyperlinks for navigation. As with what I just wrote to Sijpie, it is actually the other parts of the 'problem' that I am struggling to nut out how to deal with using vba.

 

[sijpie]

The easiest is to have all 10 sheets hidden, only a master sheet visible to start the process.

When the user presses the button for the first sheet, it gets unhidden. When compplete the 2nd sheet gets unhidden and the first hidden, etc till all 10 are done. Then back to the mastersheet or an evaluation sheet or whatever.