Help with checkbox

Originally Posted by 6StringJazzer

It appears that you are using a Forms checkbox (instead of an ActiveX checkbox). My personal opinion is that the best way to do this is to link the checkbox to a cell (right-click on the checkbox, Control tab, fill in Cell Link), then check to see if that cell is True or False in this macro. Note that the same macro is called if you click on the checkbox, regardless of whether you are checking it or unchecking it. So the same macro has to do both jobs.

But beyond that, undoing an action means that you have to save the prior value of this cell somewhere. You should identify a cell someplace to save the value before you change it. Also note that if cell D1 is changed anywhere but in this code, such as by the user, you are going to need a more complex solution than this.

Let's say you link the checkbox to L1 (L for Link). And you use S1 to save the value (S for save).

Please note that I have compiled but not tested this, so it is just an example to illustrate the concept. If we're both lucky it will work.

Sub autotext()

    Dim ws1 As Worksheet:   Set ws1 = Sheets("Rough")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Actual")
    
    ' Save Value
    ws2.Range("S1") = ws2.Range("D1").Value
    
    ' Change Value if checkbox is checked
    If ws2.Range("L1") Then
    
        ' Checkbox is checked
    
        If ws2.Range("D1").Value = "" Then
           ws2.Range("D1").Value = "Please resend the file"
        Else
           ws2.Range("D1").Value = ws2.Range("D1").Value & vbLf & vbLf & "Please resend the file"
        End If
    
    Else
        
        ' Checkbox was unchecked
        ' Restore value
        ws2.Range("D1").Value = ws2.Range("S1")
        
    End If
    
End Sub

Hi,

Does that mean using Active X checkbox will solve the problem?

Thank you

First it is not necessary to quote an entire post if you are replying. It adds a lot of volume to the thread without adding clarity. If you need to emphasize something you are responding to, just quote the relevant portion.

The problem can definitely be solved with Forms checkboxes, as I described. I would have done it for you if you had attached your file. But using ActiveX checkboxes makes many things easier, IMHO. VBA offers better support for them, although they use a slightly different concept. Instead of assigning a macro to them, you use macros with special names that are called automatically when the corresponding event occurs. For example

Private Sub CheckBox1_Click()

will be called if the user clicks on CheckBox1. You do not have to do anything to assign this macro to the checkbox, it is taken care of automatically by VBA. Then in the code you can have logic like

If CheckBox1 Then

   ' The checkbox is ticked

Else
 
   ' The checkbox is clear

End If