Checkbox value result in unintended code firing

[RockEd]

Hello,

I cannot get my head around the order of this code and I hope someone can help - I've tried to explain myself as clear as possible, but if you want to cut to the chase then the end of this question has the problem line of code..)!

I have a spreadsheet with a column called 'date of decs' (decs stands for 'declaration'). This can either be empty or have a date in it. The column header has a named range as 'Date_of_decs'.

When the user presses the 'menu' button to bring up a userform, the userform (amongst other things) contains a checkbox ('CB_date_of_decs') asking whether the date of declaration has been signed, and a textbox for the date.

The textbox ('TB_Date_of_Decs') is linked to the spreadsheet, like so (this is held in 'UserForm_Activate')

With ActiveCell

        Date_of_Decs = ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column)

        TB_Date_of_Decs.Value = Date_of_Decs
End with

I want to be able to have the checkbox already checked if a date a filled out on the spreadsheet, and I am achieving this like so (this is also held in 'UserForm_Activate'): (This code also changes the colour of the textbox to grey or white, and later I will enable/disable it)

With ActiveCell

If Not IsEmpty(ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column).Value) Then

    CB_date_of_decs.Value = True
    TB_Date_of_Decs.BackColor = vbWhite
    
Else

    CB_date_of_decs.Value = False
    TB_Date_of_Decs.BackColor = &H80000010
    
End If

End With

Finally, and this is where the issues arise, what I want to happen is if the user enables the checkbox, then the textbox is enabled (and turns white)

or if they disable the checkbox then:

1) The textbox is disabled;
2) If there are any dates in the textbox then the user is given a warning saying that the date will be deleted if they continue, however it shouldn't give this message if the textbox is already empty.

To do this here is my code:

Private Sub CB_date_of_decs_Click()

Dim response_decs As VbMsgBoxResult

If CB_date_of_decs.Value = True Then

    TB_Date_of_Decs.BackColor = vbWhite
    TB_Date_of_Decs.Enabled = True

Else

    TB_Date_of_Decs.BackColor = &H80000010
    TB_Date_of_Decs.Enabled = False

End If

If Not IsEmpty(TB_Date_of_Decs.Value) Then
         
        response_decs = MsgBox("This will delete the date of decs entered", vbQuestion + vbOKCancel)
    
        If response_decs = vbOK Then
        
            TB_Date_of_Decs.Value = Empty
            
        Else
        
            CB_date_of_decs.Value = True
            TB_Date_of_Decs.Enabled = True
            Exit Sub
    
        End If
End If

End Sub

The problem I have is that this code is firing alongside the 'UserForm_Activate' (which I guess would slow things down ever so slightly) and the bit 'If Not IsEmpty(TB_Date_of_Decs.Value) Then' doesn't seem to work - even if the textbox is empty it still fires the message box.

Can anyone shed some light into where I am going wrong? Thank you!

 

[RockEd]

I couldn't work this out and so changed it to an option button instead and made it work (the separate 'yes' and 'no' parts make working the code in my eyes so much easier). Let me know if anyone wants to see the final code to make that work.

 

[smozgur]

Let me know if anyone wants to see the final code to make that work.

Yes, that would be great if you could post your final solution to help future readers. Then please mark that post as the solution.
Thank you.

 

[RockEd]

Posted too soon... just editing...

No problem; I did 4 things to make this work:

1) Made a 'frame' with 2 buttons inside. The caption is - decs completed? - and the 2 buttons are 'yes' and 'no'. The buttons are renamed

 

[RockEd]

Ignore the above, I don't know how to delete it now its' been posted!!

No problem; I did 4 things to make this work:

1) Made a 'frame' with 2 buttons inside. The caption is - decs completed? - and the 2 buttons are 'yes' and 'no'. The buttons are renamed as 'OB_DOD_Yes' and 'OB_DOD_No' (OB stands for OptionButton and DOD is date of decs). I then made a textbox called 'TB_Date_of_Decs' (TB = Textbox).

2) In the Userform activate sub I checked whether there is already a date in the spreadsheet under the date of decs column to auto update the values of the Option buttons yes and no respectively, and also to update the colour of the textbox (white if the button is yes, grey if the button is no), like so:

   If Not IsEmpty(ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column).Value) Then
   
        OB_DOD_Yes.Value = True
        TB_Date_of_Decs.BackColor = vbWhite
       
    Else
   
        OB_DOD_No.Value = True
        TB_Date_of_Decs.BackColor = &H80000010
       
    End If

3) I made 2 further subs for the respective clicks on the option buttons yes and no; this is to change the colour of the textbox (again white is button is yes, grey if the button is no), but also to enable and disable the textbox respectively.

For the yes button:

Private Sub OB_DOD_Yes_Click()

Dim response_decs As VbMsgBoxResult

If OB_DOD_Yes.Value = True Then

    TB_Date_of_Decs.BackColor = vbWhite
    TB_Date_of_Decs.Enabled = True

Else

    TB_Date_of_Decs.BackColor = &H80000010
    TB_Date_of_Decs.Enabled = False


End If
End Sub

For the No button I also added in some code to check whether there is anything in the textbox and then warn the user that this will be deleted if they change to No (i.e. it was 'completed' but they are now saying it isn't completed, so the date should also be removed):

Private Sub OB_Report_No_Click()
Dim response_decs As VbMsgBoxResult
'This is the check if the textbox is empty:
If Me.TB_Date_of_Report <> "" Then
        
        response_decs = MsgBox("This will delete the date of report sent entered.  Press OK to continue.", vbCritical + vbOKCancel)
   
        If response_decs = vbOK Then
       
            TB_Date_of_Report.Value = Empty
           
        Else
       
            TB_Date_of_Report.Enabled = True
            OB_Report_Yes.Value = True
           
            Exit Sub
   
        End If
End If

If OB_Report_No.Value = True Then

    TB_Date_of_Report.BackColor = &H80000010
    TB_Date_of_Report.Enabled = False

Else

    TB_Date_of_Report.BackColor = vbWhite
    TB_Date_of_Report.Enabled = True
   
End If
End Sub

4) Finally I have the code to save it to the spreadsheet, this checks whether there is a valid date in the date of decs field and then saves it in the right place (I have used named ranges to help sort where it should be placed in the sheet):

Dim Date_of_Decs As String

With ActiveCell
   
    Date_of_Decs = Range("Date_of_Decs").Column
       
    If Not TB_Date_of_Decs.Value = "" Then
 
        If IsDate(TB_Date_of_Decs.Text) Then
                               
            ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column) = CDate(TB_Date_of_Decs.Text)
           
        Else
           
            MsgBox ("You did not enter a valid date in the date of decs field. This particular field will be not be saved to the spreadsheet."), vbCritical + vbOKOnly
              
        End If
           
    Else
   
        ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column) = TB_Date_of_Decs.Value
       
    End If
End with

Throughout the above code I have missed off other bits that are happening that were irrelevant to my question so I'm missed things like 'End sub' but hopefully that is obvious why..

 

[RockEd]

Oh the only other thing I did was populate the textbox with the date of decs from the spreadsheet when the userform is activated:

Dim Date_of_Decs as String
With ActiveCell


        Date_of_Decs = ActiveSheet.Cells(.Row, Range("Date_of_Decs").Column)
        TB_Date_of_Decs.Value = Date_of_Decs
End With