re order code :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

re order code
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

chimp
Board Regular


Joined: 17 Nov 2003
Posts: 45

Flag: Blank

Status: Offline

 Reply with quote  

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

Post Mon Nov 24, 2003 4:07 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 730
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

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

Post Mon Nov 24, 2003 4:20 pm 
 View user's profile Send private message Send e-mail

chimp
Board Regular


Joined: 17 Nov 2003
Posts: 45

Flag: Blank

Status: Offline

 Reply with quote  

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

Post Mon Nov 24, 2003 4:24 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 730
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

Re: re order code

Sorry - the 'cmdok_Click' event

Alan

Post Mon Nov 24, 2003 4:25 pm 
 View user's profile Send private message Send e-mail

chimp
Board Regular


Joined: 17 Nov 2003
Posts: 45

Flag: Blank

Status: Offline

 Reply with quote  

Re: re order code

thanks that worked

Post Mon Nov 24, 2003 4:28 pm 
 View user's profile Send private message

chimp
Board Regular


Joined: 17 Nov 2003
Posts: 45

Flag: Blank

Status: Offline

 Reply with quote  

Re: re order code

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

Post Mon Nov 24, 2003 4:29 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 730
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

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

Post Mon Nov 24, 2003 7:24 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.