Simplificar codigo " change" y "press key"
Para "dante amor"
Este es el codigo que utilizo en cada text box (k1 . K2 etc)
Private Sub kx2_Change()
w_pesx2 = 0
w_kx2 = 0
If IsNumeric(pesx2.Value) Then w_pesx2 = CDbl(pesx2.Value)
If IsNumeric(kx2.Value) Then w_kx2 = CDbl(kx2.Value)
stx2 = Format(w_pesx2 * w_kx2, "$ #,##0.00")
'x Elsamatilde
If pesx2 <> "" And kx2 <> "" Then
stx2 = pesx2 * kx2
End If
End Sub
Private Sub kx2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii < 48 And KeyAscii <> 44) Or KeyAscii > 57 Then
KeyAscii = 0
MsgBox "Error en el dato. Solo se aceptan valores numéricos."
End If
End Sub
1 Respuesta
Respuesta de Dante Amor
1
En el evento initialize
Dim colTbxs As Collection 'Collection Of Custom Textboxes
'
Sub Sumar()
'Por Dante Amor
w_tot = 0
For i = 1 To 23
w_pes = 0
w_k = 0
w_pesx = 0
w_kx = 0
If i < 4 Then
If IsNumeric(Me.Controls("pesx" & i).Value) Then w_pesx = CDbl(Me.Controls("pesx" & i).Value)
If IsNumeric(Me.Controls("kx" & i).Value) Then w_kx = CDbl(Me.Controls("kx" & i).Value)
w_imp = w_pesx * w_kx
Me.Controls("stx" & i).Value = Format(w_imp, "$ #,##0.00")
w_tot = w_tot + w_imp
End If
If IsNumeric(Me.Controls("pes" & i).Value) Then w_pes = CDbl(Me.Controls("pes" & i).Value)
If IsNumeric(Me.Controls("k" & i).Value) Then w_k = CDbl(Me.Controls("k" & i).Value)
w_imp = w_pes * w_k
Me.Controls("st" & i).Value = Format(w_imp, "$ #,##0.00")
w_tot = w_tot + w_imp
Next
stotal.Value = Format(w_tot, "$ #,##0.00")
End Sub
'
Private Sub UserForm_Activate()
pes1.Text = Format(Range("precios!d2").Value, "$ #,##0.00")
pes2.Text = Format(Range("precios!e2").Value, "$ #,##0.00")
pes3.Text = Format(Range("precios!f2").Value, "$ #,##0.00")
pes4.Text = Format(Range("precios!g2").Value, "$ #,##0.00")
pes5.Text = Format(Range("precios!h2").Value, "$ #,##0.00")
pes6.Text = Format(Range("precios!i2").Value, "$ #,##0.00")
pes7.Text = Format(Range("precios!j2").Value, "$ #,##0.00")
pes8.Text = Format(Range("precios!k2").Value, "$ #,##0.00")
pes9.Text = Format(Range("precios!l2").Value, "$ #,##0.00")
pes10.Text = Format(Range("precios!m2").Value, "$ #,##0.00")
pes11.Text = Format(Range("precios!n2").Value, "$ #,##0.00")
pes12.Text = Format(Range("precios!o2").Value, "$ #,##0.00")
pes13.Text = Format(Range("precios!p2").Value, "$ #,##0.00")
pes14.Text = Format(Range("precios!q2").Value, "$ #,##0.00")
pes15.Text = Format(Range("precios!r2").Value, "$ #,##0.00")
pes16.Text = Format(Range("precios!s2").Value, "$ #,##0.00")
pes17.Text = Format(Range("precios!t2").Value, "$ #,##0.00")
pes18.Text = Format(Range("precios!u2").Value, "$ #,##0.00")
pes19.Text = Format(Range("precios!v2").Value, "$ #,##0.00")
pes20.Text = Format(Range("precios!w2").Value, "$ #,##0.00")
pes21.Text = Format(Range("precios!x2").Value, "$ #,##0.00")
pes22.Text = Format(Range("precios!y2").Value, "$ #,##0.00")
pes23.Text = Format(Range("precios!z2").Value, "$ #,##0.00")
pesx1.Text = Format(Range("precios!aa2").Value, "$ #,##0.00")
pesx2.Text = Format(Range("precios!ab2").Value, "$ #,##0.00")
pesx3.Text = Format(Range("precios!ac2").Value, "$ #,##0.00")
End Sub
Private Sub UserForm_Initialize()
'
Dim rango, celda As Range
Dim UltimaFila As Long
Let UltimaFila = Sheets("compradores").Cells(Rows.Count, 1).End(xlUp).Row
Set rango = Sheets("compradores").Range("A2:A" & UltimaFila)
For Each celda In rango
Cmb1.AddItem celda.Value
Next celda
'
Txtdia = Date
Txtdia.Locked = True
txthora = Format(Now, "hh:mm")
txthora.Locked = True
txtday.Locked = True
txtmes.Locked = True
txtaño.Locked = True
'
txtday.Text = Range("precios!a2").Value
txtmes.Text = Range("precios!b2").Value
txtaño.Text = Range("precios!c2").Value
'--------------------------------------------------
For i = 1 To 23
Me.Controls("pes" & i).Locked = True
Me.Controls("st" & i).Locked = True
Me.Controls("k" & i).Value = 0
Next
For i = 1 To 3
Me.Controls("stx" & i).Locked = True
Me.Controls("pesx" & i).Locked = True
Me.Controls("kx" & i).Value = 0
Next
'
w_tot = w_st1 + w_st2 + w_st3 + w_st4
stotal.Value = Format(w_tot, "$ #,##0.00")
'
'CARGAR LOS textbox K en la Clase
Dim ctlLoop As MSForms.Control
Dim clsObject As Clase1
Set colTbxs = New Collection
For Each ctlLoop In Me.Controls
If TypeOf ctlLoop Is MSForms.TextBox Then
If Left(ctlLoop.Name, 1) = "k" Then
Set clsObject = New Clase1
Set clsObject.tbxCustom1 = ctlLoop
colTbxs.Add clsObject
End If
End If
Next ctlLoop
End SubEn la clase1
Public WithEvents tbxCustom1 As MSForms.TextBox 'Custom Textbox 'Public numero ' 'Referencia 'http://www.ozgrid.com/forum/showthread.php?t=80631 ' Private Sub tbxCustom1_Change() Call frmcargapedidos.Sumar End Sub ' Private Sub tbxCustom1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If Not (KeyAscii >= 48 And KeyAscii <= 57) And Not KeyAscii = 46 Then KeyAscii = 0 End If End Sub
sal u dos
