?
Solved

Suggestion on checkbox code

Posted on 2005-04-26
3
Medium Priority
?
186 Views
Last Modified: 2010-04-17
Here's what I currently have.  But I would like to add a functionality where I can make the cell change either gray or blue color.  I indicated the line I've added in with * to show what I'm conceptually trying to do.

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
        Range("D4").Interior.ColorIndex = 48 *Or  Range("D4").Interior.ColorIndex = 23
        Range("D3").Interior.ColorIndex = xlNone
        Sheets("Sheet3").Range("A3").Value = 1
    Else
        Sheets("Sheet3").Range("A3").Value = 0
        Range("D4").Interior.ColorIndex = xlNone
    End If
End Sub

Thanks
0
Comment
Question by:nelson97
  • 2
3 Comments
 
LVL 1

Expert Comment

by:CyberSwine
ID: 13876025
This looks like Excel VBA code, is this correct?

Anyhow, I can't seem to find any clear criteria for *when* the cell is to turn gray and *when* it is to turn blue. Do you want this to just happen randomly or based on some other information that you didn't mention?


Anyhow, this should make it happen randomly:

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
        ' Rnd will be a random number between 0 and 1. To truly make it random,
        ' add a Randomize statement somewhere in your startup code. Otherwise

        If Rnd < 0.5 Then
                Range("D4").Interior.ColorIndex = 48
        Else
                Range("D4").Interior.ColorIndex = 23
        End If

        Range("D3").Interior.ColorIndex = xlNone
        Sheets("Sheet3").Range("A3").Value = 1
    Else
        Sheets("Sheet3").Range("A3").Value = 0
        Range("D4").Interior.ColorIndex = xlNone
    End If
End Sub


If what you want is different colors for cells D3 and D4 (I'm just guessing now), this should do the trick:

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
        Range("D4").Interior.ColorIndex = 48

        Range("D3").Interior.ColorIndex = xlNone
        Sheets("Sheet3").Range("A3").Value = 1
    Else
        Range("D3").Interior.ColorIndex = 23

        Sheets("Sheet3").Range("A3").Value = 0
        Range("D4").Interior.ColorIndex = xlNone
    End If
End Sub


Some background on what you're trying to do would help a lot, though.
0
 

Author Comment

by:nelson97
ID: 13886088
CyberSwine,  the code you modified below is almost what I'm trying to accomplish.  I like that it turns blue first and eventually turns gray after multiple clicks.  Any way I can get to turn gray at the third click?  So I click once (assuming that the initial status is unchecked) and it turns blue, 2nd click would clear it, then I'd like the 3rd click to be gray.

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
        ' Rnd will be a random number between 0 and 1. To truly make it random,
        ' add a Randomize statement somewhere in your startup code. Otherwise

        If Rnd < 0.5 Then
                Range("D4").Interior.ColorIndex = 48
        Else
                Range("D4").Interior.ColorIndex = 23
        End If

        Range("D3").Interior.ColorIndex = xlNone
        Sheets("Sheet3").Range("A3").Value = 1
    Else
        Sheets("Sheet3").Range("A3").Value = 0
        Range("D4").Interior.ColorIndex = xlNone
    End If
End Sub

0
 
LVL 1

Accepted Solution

by:
CyberSwine earned 1080 total points
ID: 13892502
First I'd like to point out that the reason the code I wrote isn't truly random but always gives the same sequence is the way Rnd works; this is fixed by the Randomize statement, which you run once in your startup code. Not that I think this is what you want, I just thought I'd point it out.

To accomplish what you're describing you'll have to keep track somehow on the number of times the user has clicked the checkbox, or something similar. Keeping track of things is generally done with variables, so you should use at least one for this purpose. I'm assuming that the fifth time you want a blue cell, the seventh time a gray cell, the nineth time a blue cell and so on?

Since I don't know which of the colors is blue and which is gray, I'm assuming that 48 is blue and 23 is gray. If it's the wrong way around just exchange the color index numbers. Here is one possible solution:

Private Sub CheckBox6_Click()
Static NextIsGray As Boolean    ' This line was added

If CheckBox6.Value = True Then
        If NextIsGray = False Then   ' This line was changed
                Range("D4").Interior.ColorIndex = 48
                NextIsGray = True    ' This line was added
        Else
                Range("D4").Interior.ColorIndex = 23
                NextIsGray = False    ' This line was added
        End If

        Range("D3").Interior.ColorIndex = xlNone
        Sheets("Sheet3").Range("A3").Value = 1
    Else
        Sheets("Sheet3").Range("A3").Value = 0
        Range("D4").Interior.ColorIndex = xlNone
    End If
End Sub


Let me elaborate on this solution a bit. I added a static variable called NextIsGray. Static means that instead of creating the variable when the procedure starts and deleting it when the procedure is done, the variable is *always* there, so the value is remembered between clicks. The same thing could be achieved with a global variable if you need to access it from other procedures, but static is generally safer if you don't need that because then you hide the variable from potential mistakes.

The reason I added the NextIsGray variable is that I needed to keep track on which color to apply next time the checkbox is set. It is not used when the checkbox is cleared (but it still keeps track of the color). When the checkbox is set it is used, and also changed to prepare for the next time we use it.

When the program starts NextIsGray will contain the default value for a Boolean (False). When you check the checkbox the first time (first click) the NextIsGray = False condition is true, so the color index used will be 48, and NextIsGray is set to True (but the cell still has the color index 48).

Then you clear the checkbox (second click) and the NextIsGray variable is not touched or used in any way.

Then you set the checkbox a second time (third click) and the NextIsGray = False condition will be false, since the variable contains True. Thus the color index used will be 23 and NextIsGray is set to False. Now it has the same value as when we started (but the cell still has the color index 23).

Then you clear the checkbox again (fourth click) and the NextIsGray variable is not touched or used in any way.

At this point you have exactly the same state as when you started the program (except maybe some cells have changed). So the next click (fifth click) will have the same effect as the first one, the one after that (sixth click) the same as the second one, and so on.

Hope this explains it well enough. If not, just ask.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Loops Section Overview

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question