VBA, Date changes when box is checked

[brt21]

Hi,

I have been working with an Excel file to send automatic mail based on some criteria and change dates when the ActiveX check box is checked.

Condition:

If I check the box, 'TRUE' is displayed in the adjacent cell. If TRUE, the current date has to be added with numbers (7, 30, 120,..; weekly, monthly, quarterly..) and the new date is displayed in a cell in the same row. The new date is then copied to the current date and the checkbox has to be unchecked automatically. if FALSE, the current date has to be displayed as the new date (either Copy+Paste or no change at all).

Problem:

I am almost successful with my code but still have a small Problem with the For and If statements. The code as displayed below contains also the commented statements which I used for this purpose. But the problem is that regardless of whether the checkbox is checked, all the Dates in the new date column changes automatically. I am trying alternatives myself. If anyone could provide me a solution sooner, it is much appreciated.

The sample document can be found here.

Thanks a lot!

Cheers,
Bharath

Sub CommandButton2_Click()
    Dim x As Long
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lastrow
        
        If Cells(x, 13).Value <> "TRUE" Then
        
            'Cells(x, 11).Select
            'Selection.Copy
            'Cells(x, 6).Select
            'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Cells(x, 11).Select
            'Cells(x, 13)= Application.Sum(Range(Cells(x, 6), Cells(x, 10))
            ActiveCell.FormulaR1C1 = "=RC[-5]+RC[-1]"
            Application.CutCopyMode = False
            'ActiveCell.FormulaR1C1 = "FALSE"
            'Cells(x, 13).Select
        
        ElseIf Cells(x, 13).Value <> "FALSE" Then
        
            Cells(x, 6).Select
            Selection.Copy
            Cells(x, 11).Select
            Selection.Paste
        
        End If
    
    Next x
    
End Sub

 

[Kamolga]

I don't get your formula but something like this

Sub CommandButton2_Click()


    Dim x As Long


    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lastrow
        
        If Cells(x, "M") = True Then
            Cells(x, "F").Select
            Cells(x, "K").Copy
            Selection.PasteSpecial Paste:=xlPasteValues
            Cells(x, "M") = False
         Else 'False
            Cells(x, "K").Select
            Cells(x, "F").Copy
            ActiveSheet.Paste
        End If
    Next x
    
End Sub

?

 

[brt21]

I don't get your formula but something like this

Hi, I figured out the problem. I tried different methods to get my 'If' statement to accept the 'TRUE' or 'FALSE' but for some reason, VBA doesnt accept it at all. Hence, I added another column to display numbers for TRUE and FALSE and mapped it to the cell in which I wanted to change the date. Thanks a lot for spending your time. I hope someone will benefit from this code.

Sub CommandButton2_Click()


    Dim x As Long


    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lastrow
        
        If Cells(x, 14).Value = 1 Then
        
            Cells(x, 6).Value = Application.Sum(Cells(x, 6).Value, Cells(x, 10).Value)
            Cells(x, 13).Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "FALSE"
            
        
        ElseIf Cells(x, 14).Value = 0 Then
        
            Cells(x, 6).Select
            Selection.Copy
            Cells(x, 11).Select
            ActiveSheet.Paste
        
        End If
    
    Next x
    
End Sub

 

[Kamolga]

Of course vba works with True and false,

[COLOR=#333333]If Cells(x, "M") = True[/COLOR]

works perfectly and

[COLOR=#333333] Cells(x, "M") = False[/COLOR]

put it back to false (untick).

note: You don't need to select to copy

[COLOR=#333333]Cells(x, "M").[/COLOR]copy

instead of

[COLOR=#333333] Cells(x, "M").select
[/COLOR]selection.copy

 

[brt21]

Of course vba works with True and false,

QUOTE]

Thanks again. I guess I made a beginners mistake

If Cells(x, "M").Value = True Then

.Value would look for a number and not a word or?

Also, I figured out that I do not need the extra 'New date' column, hence, no 'Else' Statement. Now the code look precise!

Sub CommandButton2_Click()
    Dim x As Long
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lastrow
        
        If Cells(x, "M") = True Then
        
            Cells(x, 6).Value = Application.Sum(Cells(x, 6).Value, Cells(x, 10).Value)
            Cells(x, 13).Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "FALSE"
        
        End If
    
    Next x
    
End Sub

 

[Kamolga]

True or false is kind of a boolean status, you should not use .value or ""

You can take Application.CutCopyMode = False out of the loop (I would even delete it). No need to select Cells(x, 13) to make it false either

[COLOR=#333333][COLOR=#333333]Sub CommandButton2_Click()[/COLOR][/COLOR]
[COLOR=#333333]Dim x As Long[/COLOR]
[COLOR=#333333]lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]

[COLOR=#333333]For x = 2 To lastrow[/COLOR]

[COLOR=#333333]If Cells(x, "M") = True Then[/COLOR]
[COLOR=#333333]Cells(x, 6).Value = Application.Sum(Cells(x, 6).Value, Cells(x, 10).Value)[/COLOR]
[COLOR=#333333]Cells(x, 13) = false[/COLOR]
[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]Next x[/COLOR]

[COLOR=#333333]End Sub[/COLOR]