Help with checkbox click event...

rlink_23

Board Regular
Joined
Oct 30, 2015
Messages
149
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

Code:
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
Code:
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 :laugh::laugh::laugh:
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
TextBox1.Visible = False
Else
TextBox1.Visible = True
End If
End Sub
 
Upvote 0
Just like you may want to make sure all textbox's have values entered.

Code:
Private Sub CommandButton2_Click()
If TextBox1.Value = "" Then MsgBox "You must enter a value in TextBox1"
End Sub
 
Upvote 0
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
 
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top