re order code

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: re order code

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re order code

    i need to somehow modify the following code that the data does not unload until the user has selected yes from the msgbox.

    as it currently is the data is transfered from my userform to the spreadsheet then asks if i want to finalize the sale, but regardless of whether i select yes or no the data has already been processed.

    Private Sub cmdok_Click()
    If Left(TxtSellingPrice.Value, 1) = "" Then
    Sales.TxtSellingPrice.Value = Right(TxtSellingPrice, Len(TxtSellingPrice) - 1)
    End If
    ActiveWorkbook.Sheets("Sales").Activate

    Range("A6").Select

    Do

    If IsEmpty(ActiveCell) = False Then

    ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtDate.Value

    ActiveCell.Offset(0, 1) = cboBranch.Value

    ActiveCell.Offset(0, 2) = cboPrefix.Value

    ActiveCell.Offset(0, 3) = TxtName.Value

    ActiveCell.Offset(0, 4) = CboManufact.Value

    ActiveCell.Offset(0, 5) = CboEdition.Value

    ActiveCell.Offset(0, 6) = TxtSellingPrice.Value

    Range("A6").Select

    If MsgBox("Are you sure you want to Finalize This Sale?", vbYesNoCancel + vbCritical) = vbYes Then
    Unload Me
    End If

    End Sub


    cheers

    Andy

  2. #2
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    Hi,

    Cant you just move the code

    If MsgBox("Are you sure you want to Finalize This Sale....
    ...
    end If

    to the start of the macro?

    HTH

    ALan

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    out of curiousity, which macro ??? this is all done through code, or at least i thought it was....

    this is all the code for my userform


    Private Sub ComboBox2_Change()

    End Sub

    Private Sub Calendar1_Click()
    Dim dt1 As String 'Add this line

    dt1 = Calendar1.Value
    Label2.Caption = dt1
    txtDate.Text = dt1


    End Sub

    Private Sub CommandButton1_Click()
    Calendar1.Visible = True
    CommandButton2.Visible = True
    End Sub

    Private Sub CommandButton2_Click()
    Calendar1.Visible = False
    CommandButton2.Visible = False
    End Sub

    Private Sub CboEdition_Click()
    Dim mycarvalue As Long
    mycarvalue = WorksheetFunction.VLookup(CboEdition.Value, Sheets("Data").Range("A34:B48"), 2, 0)
    Sales.TextBox1.Value = "" & mycarvalue
    Sales.TxtSellingPrice.Value = ""
    End Sub

    Private Sub TxtSellingPrice_Change()

    End Sub

    Private Sub UserForm_Activate()
    Calendar1.Visible = False
    CommandButton2.Visible = False
    Dim dt
    dt = Date
    txtDate.Text = dt
    End Sub

    Private Sub cboBranch_Change()

    End Sub

    Private Sub CboEdition_Change()

    End Sub

    Private Sub CboManufact_Change()

    End Sub

    Private Sub cmdCancel_Click()
    If MsgBox("Are you sure you want to cancel?", vbYesNoCancel + vbCritical) = vbYes Then
    Unload Me
    End If
    End Sub


    Private Sub cmdClearForm_Click()

    Call UserForm_Initialize

    End Sub

    Private Sub cmdok_Click()
    If Left(TxtSellingPrice.Value, 1) = "" Then
    Sales.TxtSellingPrice.Value = Right(TxtSellingPrice, Len(TxtSellingPrice) - 1)
    End If
    ActiveWorkbook.Sheets("Sales").Activate

    Range("A6").Select

    Do

    If IsEmpty(ActiveCell) = False Then

    ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtDate.Value

    ActiveCell.Offset(0, 1) = cboBranch.Value

    ActiveCell.Offset(0, 2) = cboPrefix.Value

    ActiveCell.Offset(0, 3) = TxtName.Value

    ActiveCell.Offset(0, 4) = CboManufact.Value

    ActiveCell.Offset(0, 5) = CboEdition.Value

    ActiveCell.Offset(0, 6) = TxtSellingPrice.Value

    Range("A6").Select

    If MsgBox("Are you sure you want to Finalize This Sale?", vbYesNoCancel + vbQuestion) = vbYes Then
    Unload Me
    End If

    End Sub




    ' Private Sub cmdOK_Click()
    ' End Sub

    Private Sub Label1_Click()

    End Sub

    Private Sub Label3_Click()

    End Sub

    Private Sub Label6_Click()

    End Sub

    Private Sub txtDate_Change()

    End Sub

    Private Sub UserForm_Initialize()
    'populate the first combo box

    txtDate.Value = ""

    TxtName.Value = ""

    TxtSellingPrice.Value = ""

    With cboBranch

    .AddItem "London"

    .AddItem "Birmingham"

    .AddItem "Cardiff"

    .AddItem "Glasgow"

    End With

    cboBranch.Value = ""

    With cboPrefix

    .AddItem "Mr"

    .AddItem "Mrs"

    .AddItem "Miss"

    .AddItem "Other"

    End With

    cboPrefix.Value = ""

    With CboManufact
    .AddItem
    .AddItem "Ford"
    .AddItem "Vauxhall"
    .AddItem "Toyota"
    .AddItem "VW"
    .AddItem "Subaru"
    End With
    End Sub

    Private Sub cboManufact_Click()
    CboEdition.Clear 'clear the type combo before call or recall

    Call FillBox 'call sub routine
    End Sub
    Sub FillBox()
    Dim b
    b = CboManufact.Text
    If CboManufact.Text = "Ford" Then
    CboEdition.AddItem "KA 1.3i [70] 3dr"
    CboEdition.AddItem "KA 1.3i Collection [70] 3dr"
    CboEdition.AddItem "Fiesta 1.4 Finesse 5dr Auto"
    CboEdition.AddItem "Fiesta 1.4 Ghia 5dr Auto"
    ElseIf CboManufact.Text = "Vauxhall" Then
    CboEdition.AddItem "Vectra LS 2.0DTi 16v - 5 Dr Estate"
    CboEdition.AddItem "Vectra LS 2.2DTi 16v - 5 Dr Estate"
    CboEdition.AddItem "Zafira 2.0 DTi Club 5dr Auto"
    CboEdition.AddItem "Zafira 2.0 DTi Design 5dr"
    ElseIf CboManufact.Text = "Toyota" Then
    CboEdition.AddItem "Avensis 2.0 VVT-i T4 5dr Estate"
    CboEdition.AddItem "Avensis 2.0 VVT-i T4 5dr Liftback"
    CboEdition.AddItem "MR-2 1.8 VVTi 2dr"
    CboEdition.AddItem "MR-2 1.8 VVTi 2dr [AC+Hard Top]"
    ElseIf CboManufact.Text = "VW" Then
    CboEdition.AddItem "Golf 1.8 T GTi 5dr Hatchback"
    CboEdition.AddItem "Golf 1.8 T Sport 5dr Estate"
    ElseIf CboManufact.Text = "Subaru" Then
    CboEdition.AddItem "Impreza 2.0 WRX STi Prodrive 4dr"
    End If



    End Sub

  4. #4
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    Sorry - the 'cmdok_Click' event

    Alan

  5. #5
    Board Regular
    Join Date
    Nov 2003
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    thanks that worked

  6. #6
    Board Regular
    Join Date
    Nov 2003
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    hang on no it didnt, if i click the no button it still transfers the data across

  7. #7
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: re order code

    Hi,

    try

    If MsgBox("Are you sure you want to Finalize This Sale?", vbYesNoCancel + vbCritical) <> vbYes Then exit sub


    HTH

    Alan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com