Ryanchris
12-28-2019, 09:56 AM
I have an upload button that transfers the data from the userform to an excel ws. I have a msgbox in place with vbYesNo that asks if you'd still like to upload the data. Even if "No" is selected, the data is still uploading and I don't know how to prevent that :banghead:. I'm sure its simple but this newb can't find it anywhere on the "internets". See code below:
Private Sub CommandButton23_Click()
'-----------------Mandatory Fields---------------------------------------
' ------------------------------------------------------
' Reset the back color back to the original white !
' ------------------------------------------------------
SysNameText.BackColor = RGB(255, 255, 255)
KitIDText.BackColor = RGB(255, 255, 255)
' ---------------------------------------------------------
' Check if SysNameText/KitIDText were entered,
' if not show the message and color the field/s in red !
' ---------------------------------------------------------
If Len(Trim(SysNameText.Text)) = 0 Or _
Len(Trim(KitIDText.Text)) = 0 Then
MsgBox "Please enter a System Name and/or Kit ID", vbExclamation
If Len(Trim(SysNameText.Text)) = 0 Then
SysNameText.BackColor = RGB(255, 255, 0)
End If
If Len(Trim(KitIDText.Text)) = 0 Then
KitIDText.BackColor = RGB(255, 255, 0)
End If
Exit Sub
End If
Dim iexit As VbMsgBoxResult
iexit = MsgBox("Confirm you want to upload your current data. This will clear all fields but keep the same System Name.", vbQuestion + vbYesNo, "Tufflok Gears")
If iexit = vbYes Then
'----------------------------------Keep System Label but reset form--------------------------
Dim ctl As Control
For Each ctl In PipeKitConfig.Controls
If TypeName(ctl) = "TextBox" Then
' This is the only change !
If ctl.Name <> "SysNameText" And ctl.Name <> "Liq_1" And ctl.Name <> "Suc_1" And ctl.Name <> "Gas_1" Then
ctl.Value = ""
End If
End If
Next ctl
End If
'----------------------Start Data Transfer------------------------------------
Dim wks As Worksheet
Dim addnew As Range
Set wks = Sheet15
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
'LIQUID PIPE KIT
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
addnew.Offset(1, 0).Value = SysNameText.Text
addnew.Offset(1, 1).Value = KitIDText.Text
addnew.Offset(1, 2).Value = Liq_1.Text
addnew.Offset(1, 3).Value = TextBox1.Text
addnew.Offset(1, 4).Value = TextBox2.Text
addnew.Offset(1, 5).Value = TextBox3.Text
addnew.Offset(1, 6).Value = TextBox4.Text
addnew.Offset(1, 7).Value = TextBox5.Text
addnew.Offset(1, 8).Value = TextBox19.Text
'SUCTION PIPE KIT
Dim inletFilled As Boolean: inletFilled = False
If TextBox6.Text <> "" Or _
TextBox7.Text <> "" Or _
TextBox8.Text <> "" Or _
TextBox9.Text <> "" Or _
TextBox10.Text <> "" Or _
TextBox20.Text <> "" Then
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
If inletFilled = False Then
addnew.Offset(1, 0).Value = SysNameText.Text
addnew.Offset(1, 1).Value = KitIDText.Text
addnew.Offset(1, 2).Value = Suc_1.Text
inletFilled = True
End If
addnew.Offset(1, 3).Value = TextBox6.Text
addnew.Offset(1, 4).Value = TextBox7.Text
addnew.Offset(1, 5).Value = TextBox8.Text
addnew.Offset(1, 6).Value = TextBox9.Text
addnew.Offset(1, 7).Value = TextBox10.Text
addnew.Offset(1, 8).Value = TextBox20.Text
End If
Private Sub CommandButton23_Click()
'-----------------Mandatory Fields---------------------------------------
' ------------------------------------------------------
' Reset the back color back to the original white !
' ------------------------------------------------------
SysNameText.BackColor = RGB(255, 255, 255)
KitIDText.BackColor = RGB(255, 255, 255)
' ---------------------------------------------------------
' Check if SysNameText/KitIDText were entered,
' if not show the message and color the field/s in red !
' ---------------------------------------------------------
If Len(Trim(SysNameText.Text)) = 0 Or _
Len(Trim(KitIDText.Text)) = 0 Then
MsgBox "Please enter a System Name and/or Kit ID", vbExclamation
If Len(Trim(SysNameText.Text)) = 0 Then
SysNameText.BackColor = RGB(255, 255, 0)
End If
If Len(Trim(KitIDText.Text)) = 0 Then
KitIDText.BackColor = RGB(255, 255, 0)
End If
Exit Sub
End If
Dim iexit As VbMsgBoxResult
iexit = MsgBox("Confirm you want to upload your current data. This will clear all fields but keep the same System Name.", vbQuestion + vbYesNo, "Tufflok Gears")
If iexit = vbYes Then
'----------------------------------Keep System Label but reset form--------------------------
Dim ctl As Control
For Each ctl In PipeKitConfig.Controls
If TypeName(ctl) = "TextBox" Then
' This is the only change !
If ctl.Name <> "SysNameText" And ctl.Name <> "Liq_1" And ctl.Name <> "Suc_1" And ctl.Name <> "Gas_1" Then
ctl.Value = ""
End If
End If
Next ctl
End If
'----------------------Start Data Transfer------------------------------------
Dim wks As Worksheet
Dim addnew As Range
Set wks = Sheet15
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
'LIQUID PIPE KIT
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
addnew.Offset(1, 0).Value = SysNameText.Text
addnew.Offset(1, 1).Value = KitIDText.Text
addnew.Offset(1, 2).Value = Liq_1.Text
addnew.Offset(1, 3).Value = TextBox1.Text
addnew.Offset(1, 4).Value = TextBox2.Text
addnew.Offset(1, 5).Value = TextBox3.Text
addnew.Offset(1, 6).Value = TextBox4.Text
addnew.Offset(1, 7).Value = TextBox5.Text
addnew.Offset(1, 8).Value = TextBox19.Text
'SUCTION PIPE KIT
Dim inletFilled As Boolean: inletFilled = False
If TextBox6.Text <> "" Or _
TextBox7.Text <> "" Or _
TextBox8.Text <> "" Or _
TextBox9.Text <> "" Or _
TextBox10.Text <> "" Or _
TextBox20.Text <> "" Then
Set addnew = wks.Range("G65356").End(xlUp).Offset(0, 0)
If inletFilled = False Then
addnew.Offset(1, 0).Value = SysNameText.Text
addnew.Offset(1, 1).Value = KitIDText.Text
addnew.Offset(1, 2).Value = Suc_1.Text
inletFilled = True
End If
addnew.Offset(1, 3).Value = TextBox6.Text
addnew.Offset(1, 4).Value = TextBox7.Text
addnew.Offset(1, 5).Value = TextBox8.Text
addnew.Offset(1, 6).Value = TextBox9.Text
addnew.Offset(1, 7).Value = TextBox10.Text
addnew.Offset(1, 8).Value = TextBox20.Text
End If