VBA TRUE/FALSE help

[hissonrr]

I am using a Macro I pulled offline to timestamp certain cells when changed

Is there a way to alter this macro so that it works if instead of looking for any value and blank values, it will add a date/time if TRUE is inputted in O11 and clears it if O11 is FALSE?

Any help would be appreciated as I am a VBA novice.

-R

Private Sub Worksheet_Change(ByVal Target As Excel.Range)        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("O11"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    End Sub

 

[Mentor82]

Hi,
Try this.

Sub Worksheet_Change(ByVal Target As Excel.Range)        
With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("O11"), .Cells) Is Nothing Then
                If Range("O11").value=False then
   
                Application.EnableEvents = False
                    .Offset(0, 1).ClearContents
                Else
                    .Offset(0, 1).NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Offset(0,1).Value = Now
                End If
                Application.EnableEvents = True
            End If
        End With
    End Sub

 

[steve the fish]

Try this:

With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("O11"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        Select Case LCase(.Value)
            Case True
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Now
                End With
            Case False
                .Offset(0, 1).ClearContents
        End Select
        Application.EnableEvents = True
    End If
End With

 

[hissonrr]

neither are working

It may be because the cell O11 is linked to a checkbox, so if it is checked it will be TRUE and if not it will be FALSE

-R

 

[Mentor82]

Hi,
Check now. It's not because you have the cell linked to the check box.

Sub Worksheet_Change(ByVal Target As Excel.Range)        
With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("O11"), .Cells) Is Nothing Then
                If LCase(Range("O11")).value="false" then
   
                Application.EnableEvents = False
                    .Offset(0, 1).ClearContents
                Else
                    .Offset(0, 1).NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Offset(0,1).Value = Now
                End If
                Application.EnableEvents = True
            End If
        End With
End Sub

 

[hissonrr]

Nope

still is blank when I check/uncheck the checkbox that makes O11 TRUE/FALSE


using the first one you posted If I change the O11 to a actual number it works

 

[steve the fish]

Assuming its a form checkbox then place this in a standard module:

Sub CheckBoxO11()

With Range("O11")
    Select Case LCase(.Value)
        Case true
            With .Offset(0, 1)
                .NumberFormat = "dd mmm yyyy hh:mm:ss"
                .Value = Now
            End With
        Case false
            .Offset(0, 1).ClearContents
    End Select
End With

End Sub

Then right click on the checkbox and assign the macro to the checkbox.

 

[hissonrr]

Perfect Steve!



Thanks I appreciate the help both of you