checkbox with condition on textbox

fher9728

New Member
Joined
Jan 9, 2020
Messages
30
Office Version
365
Platform
Windows
hi, i want to make a command button which calculated a specific formula

the thing i want to do is to grab the data from a textbox and multiplied with the costo of each type, but the textbox only appears if the checkbox is in its value true, for example (it is in spanish):
1580251620913.png
the checkbox is not marked so it doesnt appear the textbox

when i marked it, it appear this way:
1580251677680.png


I really dont have problem in this part, but my problem is when i left the checkbox unmarked it appears me an error, the thing I want to is the when checkbox.value=false the value of the textbox automatically becomes 0 even though the textbox doesnt appear, so the formula I have put on the command button doesnt throw me an error.

I´ll show you parts of the code if this can help out:
This is for showing and hiding the textbox and label
VBA Code:
Private Sub CheckPollo_Click()
If CheckPollo.Value = True Then
pollo.Visible = True
Else
pollo.Visible = False
End If
If CheckPollo.Value = True Then
Label42.Visible = True
Else
Label42.Visible = False
End If
End Sub
This is the command button code (there are multiple checkboxes and textboxes):

VBA Code:
Dim pollo_ud As Integer
Dim chorizo_lb As Double
Dim panbac_ud As Integer
Dim panmax_ud As Integer
Dim torthar_paq As Integer
Dim tortmaz_paq As Integer
Dim ques_lb As Double
Dim lech_ud As Integer
Dim tom_bol As Double
Dim cil_maz As Integer
Dim cub_bol As Integer
Dim ajo_paq As Integer
Dim per_maz As Integer
Dim ace_bot As Integer
Dim may_bol As Integer
Dim esp_bol As Integer
Dim mos_bol As Integer
Dim azu_lb As Double
Dim con_bol As Integer
Dim ach_bol As Integer
Dim pap_lb As Double
Dim pin_ud As Integer
Dim mel_ud As Integer
Dim sand_ud As Integer
Dim mar_bol As Double
Dim tam_bol As Double
Dim gas_paq As Integer
Dim caf_lb As Double
Dim costocalculado As Double
Dim diference As Double
pollo_ud = pollo.Text
chorizo_lb = chorizo.Text
panbac_ud = bacilio.Text
panmax_ud = maxi.Text
torthar_paq = TortHar.Text
tortmaz_paq = TortMaz.Text
ques_lb = quesillo.Text
ajo_paq = ajo.Text
mos_bol = mostaza.Text
lech_ud = lechuga.Text
per_maz = perejil.Text
azu_lb = azucar.Text
tom_bol = tomate.Text
ace_bot = aceite.Text
con_bol = consome.Text
cil_maz = cilantro.Text
may_bol = mayonesa.Text
ach_bol = achote.Text
cub_bol = cubitos.Text
esp_bol = especies.Text
pap_lb = papas.Text
pin_ud = pina.Text
mel_ud = melon.Text
sand_ud = sandia.Text
mar_bol = maracuya.Text
tam_bol = tamarindo.Text
gas_paq = gaseosa.Text
caf_lb = cafe.Text
If CheckPollo.Value = False Then
pollo.Text = 0
Else
End If
If CheckChorizo.Value = False Then
chorizo_lb = 0
Else
End If
If CheckPanCamp.Value = False Then
panbac_ud = 0
Else
End If
If CheckPanTort.Value = False Then
panmax_ud = 0
Else
End If
If CheckTortHar.Value = False Then
torthar_paq = 0
Else
End If
If CheckTortMaz.Value = False Then
tortmaz_paq = 0
Else
End If
If CheckQues.Value = False Then
ques_lb = 0
Else
End If
If CheckAjo.Value = False Then
ajo_paq = 0
Else
End If
If CheckMos.Value = False Then
mos_bol = 0
Else
End If
If CheckLech.Value = False Then
lech_ud = 0
Else
End If
If CheckPer.Value = False Then
per_maz = 0
Else
End If
If CheckAzu.Value = False Then
azu_lb = 0
Else
End If
If CheckTom.Value = False Then
tom_bol = 0
Else
End If
If CheckAce.Value = False Then
ace_bot = 0
Else
End If
If CheckCon.Value = False Then
con_bol = 0
Else
End If
If CheckCil.Value = False Then
cil_maz = 0
Else
End If
If CheckMay.Value = False Then
may_bol = 0
Else
End If
If CheckAch.Value = False Then
ach_bol = 0
Else
End If
If CheckCub.Value = False Then
cub_bol = 0
Else
End If
If CheckEsp.Value = False Then
esp_bol = 0
Else
End If
If CheckPap.Value = False Then
pap_lb = 0
Else
End If
If CheckPin.Value = False Then
pin_ud = 0
Else
End If
If CheckMel.Value = False Then
mel_ud = 0
Else
End If
If CheckSan.Value = False Then
sand_ud = 0
Else
End If
If CheckMar.Value = False Then
mar_bol = 0
Else
End If
If CheckTam.Value = False Then
tam_bol = 0
Else
End If
If CheckGas.Value = False Then
gas_paq = 0
Else
End If
If CheckCaf.Value = False Then
caf_lb = 0
Else
End If

costocalculado = (pollo_ud * 26 + chorizo_lb * 36.5) + (panbac_ud * 7 + panmax_ud * 6 + TortHar * 12 + TortMaz * 10) + (ques_lb * 40 + ajo_paq * 21 + mos_bol * 11 + lech_ud * 12 + per_maz * 4.76 + azu_lb * 11.5 + tom_bol * 32 + ace_bot * 160 + con_bol * 32 + cil_maz * 6 + may_bol * 13.5 + ach_bol * 50 + cub_bol * 54 + esp_bol * 60 + pap_lb * 40) + (pin_ud * 30 + mel_ud * 40 + sand_ud * 50 + mar_bol * 16 + tam_bol * 50 + gas_paq * 137 + cafe_lb * 0)
CalcComp = costocalculado

here is where I cant find out how to do the problem that I exposed before.

If anyone coul help me it would be great, thanks.

Greetings.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Why not use the _Change?
VBA Code:
Private Sub CheckPollo_Change()
  pollo.Visible = CheckPollo.Value
End Sub
 

fher9728

New Member
Joined
Jan 9, 2020
Messages
30
Office Version
365
Platform
Windows
Why not use the _Change?
VBA Code:
Private Sub CheckPollo_Change()
  pollo.Visible = CheckPollo.Value
End Sub
Hi, that is for showing and hiding the textbox? thanks, Ill try, but can you help me with the problem that I had that I need to return value 0 if the checkbox its unmarked.
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
So you want to use same method and when value changes you cal also set value as well.

VBA Code:
Private Sub CheckPollo_Change()
  pollo.Visible = CheckPollo.Value
  If Not CheckPollo.Value Then Pollo.Value = 0
End Sub
 

fher9728

New Member
Joined
Jan 9, 2020
Messages
30
Office Version
365
Platform
Windows
So you want to use same method and when value changes you cal also set value as well.

VBA Code:
Private Sub CheckPollo_Change()
  pollo.Visible = CheckPollo.Value
  If Not CheckPollo.Value Then Pollo.Value = 0
End Sub
Nice, it works well, how do you do if i want to show/hide both textbox and label? thanks for your help.
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
CheckPollo.Caption ="" Will clear the text next to checkbox
 

Watch MrExcel Video

Forum statistics

Threads
1,095,219
Messages
5,443,160
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top