Help with checkbox click event...

[rlink_23]

I have my project working great except for one detail that I missed, I am shaking my head in disbelief that I did so. And now I don't know how to proceed. So long story short I have an Userform that totals 4 textboxs and and puts the total in the Total textbox. Then lastly I have a textbox that appears that is the total plus tax when checkBox = True. The problem I have is when I save to Sheet1. It saves the total in column F. Well I failed to figure out how to Save the total With Tax instead when it is clicked with tax.. In a perfect world I would like the Total to disappear when the total with Tax appears and it saves the total with Tax. But I may not get so lucky haha.... Anyways I hope someone can help!


So code that saves to sheet

Private Sub CommandButton5_Click()   Dim rw As Long    'next available row
 
   With Sheets("Sheet1")
 
      'get the next available row in Sheet1
      rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
      'put the text box values in this row
      'put the text box values in this row
      .Range("B" & rw).Value = TextBox1.Value
      .Range("C" & rw).Value = TextBox2.Value + " " + TextBox4.Value + " " + TextBox5.Value
      .Range("E" & rw).Value = TextBox11.Value
      .Range("H" & rw).Value = TextBox7.Value + " " + TextBox8.Value
      .Range("I" & rw).Value =  TextBox9.Value
      .Range("M" & rw).Value = TextBox10.Value
      .Range("F" & rw).Value =  TextBox6.Value
      .Range("J" & rw).Value =  TextBox12.Value
      .Range("K" & rw).Value = TextBox13.Value
      .Range("L" & rw).Value =  TextBox14.Value
      .Range("O" & rw).Value = TextBox15.Value
      .Range("D" & rw).Value = TextBox16.Value
      .Range("G" & rw).Value = TextBox17.Value
      
 End With
 
  
   TextBox1.Value = Clear
   TextBox2.Value = Clear
   TextBox4.Value = Clear
   TextBox5.Value = Clear
   TextBox6.Value = Clear
   TextBox7.Value = Clear
   TextBox8.Value = Clear
   TextBox9.Value = Clear
   TextBox10.Value = Clear
   TextBox11.Value = Clear
   TextBox12.Value = Clear
   TextBox13.Value = Clear
   TextBox14.Value = Clear
   TextBox15.Value = Clear
   TextBox16.Value = Clear
   TextBox17.Value = Clear
   TextBox18.Value = Clear
   TextBox19.Value = Clear
   
End Sub

Then I have the Check Box Click event

Private Sub CheckBox1_Click()

For Each Objctrl In Me.Controls
If Left(Objctrl.Name, 4) = "Text" Then Objctrl.Visible = CheckBox1.Value
Next
If CheckBox1 = True Then
 TextBox6.Value = Cost.Value * 1.088
 TextBox6.Value = Format(TextBox6.Value, "$#,##0.00")
 Else
 CheckBox1 = False
 TextBox6.Value = ""
 
 End If


   End Sub

Also I am guessing there is a better way of doing the coding I did but please don't make fun of me hahaha

 

[My Aswer Is This]

What 4 textboxes total up to the total not including tax
Why do you want to hide and unhide text boxes?

Do you need to see both texboxes on your userform Total and Total with Tax?
If not you should only need one not both.

Textbox Total equals textbox 1+2+3+4 plus tax if checkbox1 equals true

 

[rlink_23]

Well on the userform I thought it looked cleaner when the "tax" textbox was hidden, and only shown when tax is needed. I originally had it set to where u clicked the checkbox it would calculate the tax in the "total" textbox but came to the conclusion that unclicking the textbox it wouldn't be able to revert back to the original amount. What I originally wanted was if it total textbox was 1.00 and tax was clicked it would be 1.08 then when unclicked it would revert back to 1.00 (if clicked by mistake) now that you pose the question I'm not sure how to proceed.. ugh haha

 

[rlink_23]

I also thought it would be cool if one textbox disappeared and another one reappeared. But the problem with that is getting a different total in Sheet1

 

[My Aswer Is This]

Yes I understand. Things get more complicated when you want more and more if statements.

If value is greater or less then do this.
If user makes this mistake or that mistake.
If user enters text instead of number.
Those can go on and on.
Any thing is possible with excel it just takes more if statements
If Checkbox1.value=True Textbox7.visible=true

Like this example:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
TextBox1.Visible = False
Else
TextBox1.Visible = True
End If
End Sub

 

[My Aswer Is This]

Just like you may want to make sure all textbox's have values entered.

Private Sub CommandButton2_Click()
If TextBox1.Value = "" Then MsgBox "You must enter a value in TextBox1"
End Sub

 

[rlink_23]

II will test that, but I think it would be easier to have one textbox and that way whatever the total is will transfer to Sheet1 Column F, unless I can add a click even when I click the save button to transfer whichever one is visible

 

[My Aswer Is This]

I guess you have to decide.

Here is a script for example that would check all textbox's to be sure they have values entered.

Private Sub CommandButton2_Click()
If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Then MsgBox "Some Textbox is empty" & vbNewLine & "Im stopping the script now": Exit Sub
MsgBox "Good Job"
End Sub

There are always other ways to do this. This is just one example.

 

[rlink_23]

I dont understand why I should make sure all text boxes are filled? And is there a way to get one total in column f depending on which textbox is visible? Which string would I put that if statement in? The checkbox click event or the command button click event?

 

[My Aswer Is This]

If your just entering text in boxes like First Name then that is no problem
But if your entering values like 1 in textbox 1 and 2 in textbox 2 and your wanting total to equal 3

And a user entered "George" in textbox 2 then you would get a error.
The script would not know how to add 1+George

And if your trying to add up values in textboxes and one textbox is empty you will get a error unless you have some sort of error catching line of code.

I think if you try it you will see it's true.