checkbox with condition on textbox

fher9728

New Member
Joined
Jan 9, 2020
Messages
22
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
678
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
22
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
678
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
22
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
678
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
CheckPollo.Caption ="" Will clear the text next to checkbox
 

Forum statistics

Threads
1,089,582
Messages
5,409,134
Members
403,252
Latest member
iscoupon01

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top