Como puedo hacer para crear un gráfico en código vba?
Para Dante Amor
Hola quisiera saber como puedo crear un gráfico con la columna de las notas finales, pero antes aproximar esas notas y que el gráfico tenga un rango de 1 a 5.
Lo otro, en el botón donde tengo que ingresar las notas, las notas a ingresar van de 1 a 5, y en este momento se ingresar manual, es decir, uno escribe el número, ¿cómo puedo hacer que al usuario se le despliegue la opción de 1 a 5 y escoja la nota?
1 respuesta
Respuesta de Dante Amor
2
Te anexo el código para cargar del 1 al 5 en cada combo
Private Sub CommandButton1_Click()
'Por.Dante Amor
Set h1 = Sheets("Hoja1")
Set h2 = Sheets("Pondera")
If IsNumeric(TextBox1) Then rut = Val(TextBox1) Else rut = TextBox1
Set b = h1.Columns("A").Find(rut, lookat:=xlWhole)
If Not b Is Nothing Then
fila = b.Row
cargo = h1.Cells(fila, "C")
Set c = h2.Columns("A").Find(cargo, lookat:=xlWhole)
If Not c Is Nothing Then
sum1 = (Val(ComboBox6) * h2.Cells(c.Row, "B") + _
Val(ComboBox7) * h2.Cells(c.Row, "C") + _
Val(ComboBox8) * h2.Cells(c.Row, "D") + _
Val(ComboBox9) * h2.Cells(c.Row, "E") + _
Val(ComboBox10) * h2.Cells(c.Row, "F") + _
Val(ComboBox11) * h2.Cells(c.Row, "G") + _
Val(ComboBox12) * h2.Cells(c.Row, "H")) * h2.Cells(c.Row, "I")
sum2 = (Val(ComboBox13) * h2.Cells(c.Row, "J") + _
Val(ComboBox14) * h2.Cells(c.Row, "K") + _
Val(ComboBox15) * h2.Cells(c.Row, "L") + _
Val(ComboBox16) * h2.Cells(c.Row, "M") + _
Val(ComboBox17) * h2.Cells(c.Row, "N")) * h2.Cells(c.Row, "O")
sum3 = (Val(ComboBox18) * h2.Cells(c.Row, "P") + _
Val(ComboBox19) * h2.Cells(c.Row, "Q")) * h2.Cells(c.Row, "R")
resul = sum1 + sum2 + sum3
h1.Cells(fila, "X") = Format(resul, "##0.00")
Call Cargar(h1, fila)
Call Limpiar
MsgBox "Datos actualizados"
Else
MsgBox "El cargo no existe en la hoja de ponderaciones"
End If
Else
MsgBox "El Rut no existe"
End If
End Sub
'Ejemplo
'Sum = (Val(TextBox6) * (50 / (3 * 100)) + Val(TextBox7) * (50 / (3 * 100)) + Val(TextBox8) * (50 / (3 * 100)) + Val(TextBox9) * 0 + _
Val(TextBox10) * 0.3 + Val(TextBox11) * 0.2 + Val(TextBox12) * 0) * META + (Val(TextBox13) * 0.2 + Val(TextBox14) * 0.2 + _
Val(TextBox15) * 0.2 + Val(TextBox16) * 0.2 + Val(TextBox17) * 0.2) * AG + (Val(TextBox18) * 0.1 + Val(TextBox19) * 0.1) * FALLA
'
Sub Cargar(h1, fila)
h1.Cells(fila, "A") = Val(TextBox1)
h1.Cells(fila, "B") = TextBox2
h1.Cells(fila, "C") = TextBox3
h1.Cells(fila, "D") = TextBox4
h1.Cells(fila, "E") = TextBox5
h1.Cells(fila, "F") = Val(ComboBox6)
h1.Cells(fila, "G") = Val(ComboBox7)
h1.Cells(fila, "H") = Val(ComboBox8)
h1.Cells(fila, "I") = Val(ComboBox9)
h1.Cells(fila, "J") = Val(ComboBox10)
h1.Cells(fila, "K") = Val(ComboBox11)
h1.Cells(fila, "L") = Val(ComboBox12)
h1.Cells(fila, "M") = Val(ComboBox13)
h1.Cells(fila, "N") = Val(ComboBox14)
h1.Cells(fila, "O") = Val(ComboBox15)
h1.Cells(fila, "P") = Val(ComboBox16)
h1.Cells(fila, "Q") = Val(ComboBox17)
h1.Cells(fila, "R") = Val(ComboBox18)
h1.Cells(fila, "S") = Val(ComboBox19)
h1.Cells(fila, "T") = TextBox20
h1.Cells(fila, "U") = TextBox21
h1.Cells(fila, "V") = TextBox22
h1.Cells(fila, "W") = TextBox23
End Sub
'
Private Sub TextBox1_Change()
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub
'
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1 = "" Then Exit Sub
Set h = Sheets("hoja1") 'nombre de la hoja
Set b = h.Columns("A").Find(TextBox1, lookat:=xlWhole) 'Busca en la columna A
If Not b Is Nothing Then 'Si lo encuentra
TextBox1 = h.Cells(b.Row, "A").Value
TextBox2 = h.Cells(b.Row, "B").Value
TextBox3 = h.Cells(b.Row, "C").Value
TextBox4 = h.Cells(b.Row, "D").Value
TextBox5 = h.Cells(b.Row, "E").Value
ComboBox6 = h.Cells(b.Row, "F").Value
ComboBox7 = h.Cells(b.Row, "G").Value
ComboBox8 = h.Cells(b.Row, "H").Value
ComboBox9 = h.Cells(b.Row, "I").Value
ComboBox10 = h.Cells(b.Row, "J").Value
ComboBox11 = h.Cells(b.Row, "K").Value
ComboBox12 = h.Cells(b.Row, "L").Value
ComboBox13 = h.Cells(b.Row, "M").Value
ComboBox14 = h.Cells(b.Row, "N").Value
ComboBox15 = h.Cells(b.Row, "O").Value
ComboBox16 = h.Cells(b.Row, "P").Value
ComboBox17 = h.Cells(b.Row, "Q").Value
ComboBox18 = h.Cells(b.Row, "R").Value
ComboBox19 = h.Cells(b.Row, "S").Value
TextBox20 = h.Cells(b.Row, "T").Value
TextBox21 = h.Cells(b.Row, "U").Value
TextBox22 = h.Cells(b.Row, "V").Value
TextBox23 = h.Cells(b.Row, "W").Value
TextBox24 = h.Cells(b.Row, "X").Value
Else
MsgBox "El RUT no existe" 'si no existe
End If
End Sub
'
Private Sub UserForm_Activate()
TextBox24.Enabled = False
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
For i = 6 To 19
For n = 1 To 5
Me.Controls("ComboBox" & i).AddItem n
Next
Next
End Sub
'
Sub Limpiar()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
ComboBox6 = ""
ComboBox7 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox24 = ""
End Sub'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
Te anexo las macros actualizadas
Dim h1, h2
'
Private Sub CommandButton1_Click()
'Por.Dante Amor
If IsNumeric(TextBox1) Then rut = Val(TextBox1) Else rut = TextBox1
Set b = h1.Columns("A").Find(rut, lookat:=xlWhole)
If Not b Is Nothing Then
fila = b.Row
cargo = h1.Cells(fila, "C")
Set c = h2.Columns("A").Find(cargo, lookat:=xlWhole)
If Not c Is Nothing Then
sum1 = (Val(ComboBox6) * h2.Cells(c.Row, "B") + _
Val(ComboBox7) * h2.Cells(c.Row, "C") + _
Val(ComboBox8) * h2.Cells(c.Row, "D") + _
Val(ComboBox9) * h2.Cells(c.Row, "E") + _
Val(ComboBox10) * h2.Cells(c.Row, "F") + _
Val(ComboBox11) * h2.Cells(c.Row, "G") + _
Val(ComboBox12) * h2.Cells(c.Row, "H")) * h2.Cells(c.Row, "I")
sum2 = (Val(ComboBox13) * h2.Cells(c.Row, "J") + _
Val(ComboBox14) * h2.Cells(c.Row, "K") + _
Val(ComboBox15) * h2.Cells(c.Row, "L") + _
Val(ComboBox16) * h2.Cells(c.Row, "M") + _
Val(ComboBox17) * h2.Cells(c.Row, "N")) * h2.Cells(c.Row, "O")
sum3 = (Val(ComboBox18) * h2.Cells(c.Row, "P") + _
Val(ComboBox19) * h2.Cells(c.Row, "Q")) * h2.Cells(c.Row, "R")
resul = sum1 + sum2 + sum3
h1.Cells(fila, "X") = Format(resul, "##0.00")
Call Cargar(h1, fila)
Call Limpiar
TextBox1.SetFocus
MsgBox "Datos actualizados"
Else
MsgBox "El cargo no existe en la hoja de ponderaciones"
End If
Else
MsgBox "El Rut no existe"
End If
End Sub
'
Sub Cargar(h1, fila)
h1.Cells(fila, "A") = Val(TextBox1)
h1.Cells(fila, "B") = TextBox2
h1.Cells(fila, "C") = TextBox3
h1.Cells(fila, "D") = TextBox4
h1.Cells(fila, "E") = TextBox5
h1.Cells(fila, "F") = Val(ComboBox6)
h1.Cells(fila, "G") = Val(ComboBox7)
h1.Cells(fila, "H") = Val(ComboBox8)
h1.Cells(fila, "I") = Val(ComboBox9)
h1.Cells(fila, "J") = Val(ComboBox10)
h1.Cells(fila, "K") = Val(ComboBox11)
h1.Cells(fila, "L") = Val(ComboBox12)
h1.Cells(fila, "M") = Val(ComboBox13)
h1.Cells(fila, "N") = Val(ComboBox14)
h1.Cells(fila, "O") = Val(ComboBox15)
h1.Cells(fila, "P") = Val(ComboBox16)
h1.Cells(fila, "Q") = Val(ComboBox17)
h1.Cells(fila, "R") = Val(ComboBox18)
h1.Cells(fila, "S") = Val(ComboBox19)
h1.Cells(fila, "T") = TextBox20
h1.Cells(fila, "U") = TextBox21
h1.Cells(fila, "V") = TextBox22
h1.Cells(fila, "W") = TextBox23
End Sub
Private Sub CommandButton2_Click()
'Buscar
If TextBox1 = "" Then Exit Sub
Set b = h1.Columns("A").Find(TextBox1, lookat:=xlWhole) 'Busca en la columna A
If Not b Is Nothing Then 'Si lo encuentra
TextBox1 = h1.Cells(b.Row, "A").Value
TextBox2 = h1.Cells(b.Row, "B").Value
TextBox3 = h1.Cells(b.Row, "C").Value
TextBox4 = h1.Cells(b.Row, "D").Value
TextBox5 = h1.Cells(b.Row, "E").Value
ComboBox6 = h1.Cells(b.Row, "F").Value
ComboBox7 = h1.Cells(b.Row, "G").Value
ComboBox8 = h1.Cells(b.Row, "H").Value
ComboBox9 = h1.Cells(b.Row, "I").Value
ComboBox10 = h1.Cells(b.Row, "J").Value
ComboBox11 = h1.Cells(b.Row, "K").Value
ComboBox12 = h1.Cells(b.Row, "L").Value
ComboBox13 = h1.Cells(b.Row, "M").Value
ComboBox14 = h1.Cells(b.Row, "N").Value
ComboBox15 = h1.Cells(b.Row, "O").Value
ComboBox16 = h1.Cells(b.Row, "P").Value
ComboBox17 = h1.Cells(b.Row, "Q").Value
ComboBox18 = h1.Cells(b.Row, "R").Value
ComboBox19 = h1.Cells(b.Row, "S").Value
TextBox20 = h1.Cells(b.Row, "T").Value
TextBox21 = h1.Cells(b.Row, "U").Value
TextBox22 = h1.Cells(b.Row, "V").Value
TextBox23 = h1.Cells(b.Row, "W").Value
TextBox24 = h1.Cells(b.Row, "X").Value
Else
MsgBox "El RUT no existe" 'si no existe
End If
End Sub
'
Private Sub TextBox1_Change()
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
End Sub
'
Private Sub UserForm_Activate()
'Act.Por.Dante Amor
Set h1 = Sheets(1)
Set h2 = Sheets("PONDERACIONES")
TextBox24.Enabled = False
If TextBox1.Text = "" Then
CommandButton1.Enabled = False
Else
CommandButton1.Enabled = True
End If
For i = 6 To 19
For n = 1 To 5
Me.Controls("ComboBox" & i).AddItem n
Next
Next
End Sub
'
Sub Limpiar()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
ComboBox6 = ""
ComboBox7 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox24 = ""
End Sub'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
