Consulting

Results 1 to 4 of 4

Thread: Data uploading from userform to excel when "No" is selected in msgbox

  1. #1

    Data uploading from userform to excel when "No" is selected in msgbox

    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 . 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

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Change the line

    If iexit = vbYes Then
    to

    If iexit <> vbYes Then Exit Sub
    and remove the End If after the Next ctl
    Semper in excretia sumus; solum profundum variat.

  3. #3
    I forgot to comment that this worked great!
    Thank you!!

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    No prob's, glad to have helped
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •