MsgBox Para formulario de registros repetidos
Para: Dante Amor
Ante todo un cordial saludo amigo espero que me puedas ayudar con un código, lo que busco es que si los registros son repetidos me muestren mensajes es decir en el textbox1+textbox2 pongo nombre y apellido y al guardar si el nombre ya existe me indique un mensaje (Nombre ya registrado Ingrese otro).
En el textbox12 pongo código de personal si al guardar el código ya existe me indique (Código ya registrado Ingrese otro), Gracias por la respuesta que me puedas dar.
Private Sub CommandButton2_Click()
ult = Sheets("Hoja2").Cells(Rows.Count, 2).End(xlUp).Row
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Or TextBox4 = "" Or TextBox5 = "" Or TextBox6 = "" Or TextBox8 = "" Or TextBox9 = "" Or TextBox10 = "" Or TextBox11 = "" Or ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Then
MsgBox "Favor ingrese todos los datos", vbRetryCancel + vbCritical, "ERROR"
Else
Sheets("Hoja2").Cells(ult + 1, 2) = TextBox1 & " " & TextBox2 'MsgBox Indicando (NOMBRE YA REGISTRADO INGRESE OTRO)
Sheets("Hoja2").Cells(ult + 1, 3) = TextBox12 'MsgBox Indicando (CODIGO YA REGISTRADO INGRESE OTRO)
Sheets("Hoja2").Cells(ult + 1, 4) = TextBox3
Sheets("Hoja2").Cells(ult + 1, 5) = TextBox4
Sheets("Hoja2").Cells(ult + 1, 6) = ComboBox1
Sheets("Hoja2").Cells(ult + 1, 7) = TextBox9
Sheets("Hoja2").Cells(ult + 1, 8) = TextBox10
Sheets("Hoja2").Cells(ult + 1, 9) = TextBox5
Sheets("Hoja2").Cells(ult + 1, 10) = TextBox6
If OB1.Value = True Then
Sheets("Hoja2").Cells(ult + 1, 11) = OB1.Caption
ElseIf OB2.Value = True Then
Sheets("Hoja2").Cells(ult + 1, 11) = OB2.Caption
End If
Sheets("Hoja2").Cells(ult + 1, 12) = ComboBox2 & " - " & ComboBox3
Sheets("Hoja2").Cells(ult + 1, 13) = TextBox8
Sheets("Hoja2").Cells(ult + 1, 14) = TextBox11
UserForm1.Hide
X = ult + 1
rango = "B" & X & ":N" & X
Sheets("Hoja2").Select
Range(rango).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
MsgBox "Datos ingresados correctamente"
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
OB1 = Unchecked
OB2 = Unchecked
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
End If
Sheets("Hoja1").Select
End Sub
1 respuesta
Respuesta de Dante Amor
2
H o l a:
Te anexo la macro actualizada
Private Sub CommandButton2_Click()
'Act.Por.Dante Amor
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Or TextBox4 = "" Or TextBox5 = "" Or TextBox6 = "" Or TextBox8 = "" Or TextBox9 = "" Or TextBox10 = "" Or TextBox11 = "" Or ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Then
MsgBox "Favor ingrese todos los datos", vbRetryCancel + vbCritical, "ERROR"
Exit Sub
End If
'
Set h2 = Sheets("Hoja2")
ult = h2.Cells(Rows.Count, 2).End(xlUp).Row + 1
'
nombre = TextBox1 & " " & TextBox2
Set b = h2.Columns("B").Find(nombre, lookat:=xlWhole)
If Not b Is Nothing Then
MsgBox "NOMBRE YA REGISTRADO INGRESE OTRO"
TextBox1.SetFocus
Exit Sub
End If
'
codigo = TextBox12
Set b = h2.Columns("C").Find(codigo, lookat:=xlWhole)
If Not b Is Nothing Then
MsgBox "CODIGO YA REGISTRADO INGRESE OTRO"
TextBox12.SetFocus
Exit Sub
End If
'
h2.Cells(ult, 2) = TextBox1 & " " & TextBox2
h2.Cells(ult, 3) = TextBox12
h2.Cells(ult, 4) = TextBox3
h2.Cells(ult, 5) = TextBox4
h2.Cells(ult, 6) = ComboBox1
h2.Cells(ult, 7) = TextBox9
h2.Cells(ult, 8) = TextBox10
h2.Cells(ult, 9) = TextBox5
h2.Cells(ult, 10) = TextBox6
If ob1.Value = True Then
h2.Cells(ult, 11) = ob1.Caption
ElseIf ob2.Value = True Then
h2.Cells(ult, 11) = ob2.Caption
End If
h2.Cells(ult, 12) = ComboBox2 & " - " & ComboBox3
h2.Cells(ult, 13) = TextBox8
h2.Cells(ult, 14) = TextBox11
'
Unload Me
With h2.Range("B" & ult & ":N" & ult).Borders
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'
MsgBox "Datos ingresados correctamente"
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ob1 = Unchecked
ob2 = Unchecked
h2.Columns("B:B").EntireColumn.AutoFit
h2.Columns("C:C").EntireColumn.AutoFit
h2.Columns("D:D").EntireColumn.AutoFit
Sheets("Hoja1").Select
End Sub':) ':)
