XL2K3: Using a check box to trigger date stamp in a cell 1

[Kkamann]

I've been able to make a spreadsheet that will put a date stamp in a cell (e.g. Now) when another cell has been populated. Below is the code that I've found and slightly tweaked and it works great as designed. For example, if I type the word "true" in A4, it puts the value of =NOW in cell C6. And the date/time won't change when the file is reopened.

However, I don't want the user to be required to manually type the word "true" (or anything else for that matter) in cell A4. I don't even want an in-cell dropdown. I just want to be able to click a check box that's "floating" in the cell. So I did just that. I placed a checkbox next to cell A4 and linked it to A4. So when the box is checked, "TRUE" shows up in A4 and when I de-select it, "FALSE" is shown.

This is where my problem is. When "TRUE" is placed in cell A4 via checking the box, the macro doesn't recognize anything has happened as if I had manually typed it. Why doesn't the macro pick up the cell value as "TRUE"? It clearly says "TRUE" in A4 and I can reference A4 in a formula just fine (i.e. =A4) BTW, I tried removing the quotes in the code and use True (the VBA parameter?), but no dice.

What am I missing here? Although I might be the office geek people call before calling IT, in this forum I'm certainly a novice compared to most of you. So please bear with me.

*****CODE*****
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Value = "TRUE" Then Exit Sub
If Not Intersect(Target, Range("A4:A100")) Is Nothing Then
With Target(1, 3)
.Value = Now
.EntireColumn.AutoFit
End With
End If
End Sub

-Kkamann
A non-professional geek who's a geek.

 

[Kkamann]

I wanted to say that I found Skip's FAQ after I found/modified the code I already had. I figured his was just a different way to do the same thing and didn't want to redo everything. (sorry skip) Not sure if this would've avoided my question though.

-Kkamann
A non-professional geek who's a geek.

 

[SkipVought]

Hi,

"TRUE" is not TRUE.

Your checkbox puts that value (TRUE or FALSE not "TRUE" or "FALSE") in the linked cell.

I don't see, however, what you are doing. The APPARENT change is not a change at all, as it is a LINK.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kkamann]

@Skip - Thanks for you response. I realize there's a difference between "TRUE"/TRUE and "FALSE"/FALSE. My only point was that it didn't matter which way I coded it in the macro; it still didn't work properly.

I made a pdf that will hopefully show more clearly what I am trying to do and what my problem is. See attachment url.

-Kkamann
A non-professional geek who's a geek.

 

[SkipVought]

I already explained why the T/F from the checkbox link will not work. NOTHING CHANGES in the worksheet_change event!

Use the Cilck event of the control to assign the date.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kkamann]

Ahh...Now I understand what you were saying. I thought your last sentence was referring to the change I made in my macro (i.e. quote/no quotes). From that perspective, it confused me a little. LOL But I didn't want to look dumb and ask. I guess it's too late for that. I got it...the value displayed in the cell doesn't change as far as the worksheet_change is concerned.

Anyway, I'm not sure how to correctly use the click event in this situation (I'm not very proficient with VBA at all). I'm assuming you're referring to the CheckBox1_Click property. Here's my novice attempt:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Intersect(Target, Range("B2:B100")
With Target(1, 2)
.Value = Now
.EntireColumn.AutoFit Else
Intersect(Target, Range("B2:B100")
With Target(1, 2)
.Value = Empty
.EntireColumn.AutoFit Else
End If
End Sub

I am hoping this will clear out the date whenever the box is unchecked. That's what I would like. I'm assuming I need a Dim statement? I've never been able to master the Dim statement. If I am doing a checkbox click event, do I even need to link it to a cell?

FYI, I will have about 100-200 lines with a check box on my spreadsheet. I figure I need to incorporate a For Each statement in my macro, but I don't know how to make it do CheckBox_1, CheckBox_2, and so on.

Thanks again for your help. I apologize for the rudimentary questions. But I'm trying to learn more each time.

-Kkamann
A non-professional geek who's a geek.

 

[SkipVought]

Why not try something simpler.

Make a column of cells act like a check box. The you don't have to contend with adding controls and referencing the control to figure out what changed.

Lets assume that column A will be the "checkbox" column. Furthermore, lets assume that row 1 is reserved for headings.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub
    If Target.Column = 1 Then
        With Target
            If .Value = "þ" Then
                .Value = ""
            Else
                .Font.Name = "Wingdings"
                .Value = "þ"
'put your date stamp code here

            End If
        End With
        [A1].Select
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kkamann]

Hmmm I'll have to play with this one. Not sure what the user would be typing in(þ??)or what action the user would take. Also, do I still need the intersect command?

Thanks again for your help.

-Kkamann
A non-professional geek who's a geek.

 

[SkipVought]

NO ONE IS TYPING ANYTHING!!!!!!!

This is merely done by selecting a cell in column A!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kkamann]

Hi Skip. Thanks a bunch for helping me out. I finally got around to dinking with it this afternoon and got it to work how I envisioned (click one place, date stamp in another), thanks largely to your assistance. I would've loved to work on this at home over the weekend, but I have a Mac with Office 2K7, which dumped VBA in favor of AppleScript. Grrr.. But I digress. Anyway, here is what I ended up with (if anyone cares):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo ErrorHandler
    If Target.Row = 1 Then Exit Sub
    If Target.Column = 1 Then
        With Target
        Dim Cell As Range
            If .Value = "ü" Then
                .Value = ""
                .Font.Size = 10
'****Begin clearing date stamp code****
                For Each Cell In Target
                With Cell
                    If .Column = Range("A:A").Column Then
                        Cells(.Row, "C").Value = ""
                    End If
                End With
                Next Cell
'****End clearing date stamp code****
            Else
                .Font.Name = "Wingdings"
                .Font.Size = 18
                .Font.Bold = True
                .Font.Color = RGB(0, 128, 0)
                .Value = "ü"
'****Begin date stamp code****
                For Each Cell In Target
                With Cell
                    If .Column = Range("A:A").Column Then
                        Cells(.Row, "C").Value = Now
                    End If
                End With
                Next Cell
'****End date stamp code****
            End If
        End With
        Columns("C:C").EntireColumn.AutoFit
        [A1].Select
    End If
ErrorHandler:
    Exit Sub
End Sub

I had to throw in the ErrorHandler because I would get error messages whenever I tried to select a row/etc.

My next step is to figure out how to do this with several non-contiguous columns in the same spreadsheet...

-Kkamann
A non-professional geek who's a geek.