Need Help with BEFORECLOSE command in Excel 2

[dddivers]

Hi - I have an Excel 2003 version worksheet where I'm tracking the number of items ordered (e.g. magazine orders).

I've created 2 Checkboxes and 3 Textboxes (from the Control Toolbox toolbar). The 1st Textbox adds the number of times the 1st Checkbox is clicked and subtracts out if 1st Checkbox is deselected by the user. The 2nd Textbox does the same thing coordinated with the 2nd Checkbox.

The 3rd Textbox is doing an on-going grand total of the clicks in the 1st and 2nd Checkboxes. The code I've created for these Check & Text boxes seems to be working fine.

However, what I can't get to work is to have both Checkboxes and the first 2 Textboxes automatically clear out BUT have the on-going value in the 3rd Textbox retain its value, and I want this action to occur when the Workbook IS CLOSED, and have the workbook automatically save itself, too.

Here is the code I've been playing with but won't work. WHAT AM I DOING WRONG? Thank you.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Box1Retain As Integer
Box1Retain = TextBox1.Text

Dim Box2Retain As Integer
Box2Retain = TextBox2.Text

CheckBox1 = False
CheckBox2 = False

TextBox1.Text = Box1Retain
TextBox2.Text = Box2Retain

Dim Box3Retain As Integer
Box3Retain = Box1Retain + Box2Retain

TextBox3.Text = Box3Retain

ActiveWorkbook.Save

End Sub

 

[SkipVought]

Hi,

What, specfically, is not working as intended?

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[dddivers]

Hi Skip - most of the things I want to happen upon closing the workbook are not working:

The Activeworkbook.Save code is not saving (I'm still getting the prompt to save the file.....I don't want the prompt...want it to save for me).

The first two Checkboxes aren't clearing out, nor are the first two Textboxes....when I reopen the file, the values that were there when I last closed the file are still there (want them to be empty/refreshed).

And, I think the 3rd Textbox is retaining its values but I can't be sure because the rest of the BEFORECLOSE code isn't working properly.

Below is my entire code module if that will help you in determining the big picture. The first two code sets are doing what I want....it's the BEFORECLOSE part that isn't. Any suggestions? Thanks, Skip.

Private Sub CheckBox1_Click()

Dim box1tot As Integer
Dim box2tot As Integer
Dim box3tot As Integer


If CheckBox1 = True Then
TextBox1 = TextBox1 + 1

Else

TextBox1 = TextBox1 - 1

End If

box1tot = TextBox1.Value
box2tot = TextBox2.Value

box3tot = box1tot + box2tot

TextBox3.Text = box3tot

End Sub
__________________________________________________
Private Sub CheckBox2_Click()

If CheckBox2 = True Then
TextBox2 = TextBox2 + 1

Else

TextBox2 = TextBox2 - 1

End If

box1tot = TextBox1.Value
box2tot = TextBox2.Value

box3tot = box1tot + box2tot

TextBox3.Text = box3tot

End Sub
________________________________________________________
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Box1Retain As Integer
Box1Retain = TextBox1.Text

Dim Box2Retain As Integer
Box2Retain = TextBox2.Text

CheckBox1 = False
CheckBox2 = False

TextBox1.Text = Box1Retain
TextBox2.Text = Box2Retain

Dim Box3Retain As Integer
Box3Retain = Box1Retain + Box2Retain

TextBox3.Text = Box3Retain

Activeworkbook.save

End Sub

 

[SkipVought]

1) the before close procedure MUST be in the ThisWorkbook Object Code Sheet and must explicitly reference the UserForm Object.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With UserForm1
      Dim Box1Retain As Integer
      Box1Retain = CInt(.TextBox1.Text)
      
      Dim Box2Retain As Integer
      Box2Retain = CInt(.TextBox2.Text)
      
      Application.EnableEvents = False
      
      .CheckBox1 = False
      .CheckBox2 = False
      
      Application.EnableEvents = True
      
      .TextBox1.Text = Box1Retain
      .TextBox2.Text = Box2Retain
      
      Dim Box3Retain As Integer
      Box3Retain = Box1Retain + Box2Retain
      
      .TextBox3.Text = Box3Retain
      Application.DisplayAlerts = False
      ActiveWorkbook.Save
   End With
End Sub

To do math with textboxes, you must convert text to a numeric type...

Sub AddBoxes()
Dim box1tot As Integer
Dim box2tot As Integer
Dim box3tot As Integer
    box1tot = CInt(TextBox1.Value)
    box2tot = CInt(TextBox2.Value)
    
    box3tot = box1tot + box2tot
    
    TextBox3.Text = box3tot

End Sub
Private Sub CheckBox1_Click()

    If CheckBox1 = True Then
        TextBox1.Text = CInt(TextBox1.Text) + 1
        
    Else
    
        TextBox1.Text = CInt(TextBox1.Text) - 1
        
    End If
    
   AddBoxes
    
End Sub
Private Sub CheckBox2_Click()

   If CheckBox2 = True Then
        TextBox2.Text = CInt(TextBox2.Text) + 1
        
    Else
    
        TextBox2.Text = CInt(TextBox2.Text) - 1
        
    End If
    
   AddBoxes
    
End Sub

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[dddivers]

Hi Skip - I like your checkbox/textbox code much better than mine. Thank you very much. Regarding your BEFORECLOSE code above, I put it in the THIS WORKBOOK node in VBA but it's not working. Still getting prompted to save when I close and when I re-open the file, my checkboxes are still checked and the first 2 textboxes are not cleared (I want the 3rd textbox to keep ongoing value so I don't want it to clear out).

I'm a relative novice to VBA, as I'm sure you can tell. I have the checkbox/textbox code in the SHEET1 node in VBA (that's the sheet that houses the actual objects), and I have the BEFORECLOSE code in the THIS WORKBOOK node. Is there something I'm doing wrong? Thank you for your time and genius.