re order code

[chimp]

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

 

[al_b_cnu]

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

 

[chimp]

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

 

[al_b_cnu]

Sorry - the 'cmdok_Click' event

Alan

 

[chimp]

thanks that worked

 

[chimp]

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

 

[al_b_cnu]

Hi,

try

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


HTH

Alan