By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,913 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,913 IT Pros & Developers. It's quick & easy.

Macro assigned to an update button

P: 3
So.... I have an UpdateButton Macro assigned to my P.O. Generator, and want it to stop and ensure all fields are filled out prior to update.
Expand|Select|Wrap|Line Numbers
  1. Sub UpdateButton()
  2.   Application.ScreenUpdating = False
  3.   Dim copySheet As Worksheet
  4.   Dim pasteSheet As Worksheet
  5.  
  6.   Set copySheet = Worksheets("Purchase Order")
  7.   Set pasteSheet = Worksheets("PO Log")
  8.  
  9.   copySheet.Range("POnumber").Copy
  10.   pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  11.   Application.CutCopyMode = False
  12.   Application.ScreenUpdating = True
  13.  
  14.   copySheet.Range("OrderSummary").Copy
  15.   pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  16.   Application.CutCopyMode = False
  17.   Application.ScreenUpdating = True
  18.  
  19.   copySheet.Range("POdate").Copy
  20.   pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  21.   Application.CutCopyMode = False
  22.   Application.ScreenUpdating = True
  23.  
  24.   copySheet.Range("RequestedBy").Copy
  25.   pasteSheet.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  26.   Application.CutCopyMode = False
  27.   Application.ScreenUpdating = True
  28.  
  29.   copySheet.Range("Vendor").Copy
  30.   pasteSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  31.   Application.CutCopyMode = False
  32.   Application.ScreenUpdating = True
  33.  
  34.   copySheet.Range("DeliveryDate").Copy
  35.   pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  36.   Application.CutCopyMode = False
  37.   Application.ScreenUpdating = True
  38.  
  39.   copySheet.Range("Cost").Copy
  40.   pasteSheet.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  41.   Application.CutCopyMode = False
  42.   Application.ScreenUpdating = True
  43.  
  44.   Range("='Purchase Order'!$B$2:$N$47").PrintOut
  45.  
  46.   End Sub
  47.  
1 Week Ago #1

✓ answered by Barracuda72

For all that are interested.... The solution to my issue was
Expand|Select|Wrap|Line Numbers
  1.  
  2.  If IsEmpty(Range("OrderSummary")) Or IsEmpty(Range("RequestedBy")) Or IsEmpty(Range("POnumber")) Then
  3.  MsgBox "Information is Missing:Requested By, Order Summary, and P.O. Require User Input", vbRetryCancel
  4.  Exit Sub
  5.  End If
That is all...

Share this Question
Share on Google+
2 Replies


P: 3
Okay... So I think I have figured part of it out with
Expand|Select|Wrap|Line Numbers
  1. If Cells(4, 3).Value = "" Then
  2. MsgBox "Requested By Requires User Input"
  3.  
  4. Cancel = True
  5. End If
But I would like this add-on to stop the updating process as well... HELP!!
Here's entire code with new MsgBox code.
Expand|Select|Wrap|Line Numbers
  1. Sub UpdateButton()
  2.   Application.ScreenUpdating = False
  3.   Dim copySheet As Worksheet
  4.   Dim pasteSheet As Worksheet
  5.  
  6.   Set copySheet = Worksheets("Purchase Order")
  7.   Set pasteSheet = Worksheets("PO Log")
  8.  
  9.   copySheet.Range("POnumber").Copy
  10.   pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  11.   Application.CutCopyMode = False
  12.   Application.ScreenUpdating = True
  13.  
  14.   copySheet.Range("OrderSummary").Copy
  15.   pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  16.   Application.CutCopyMode = False
  17.   Application.ScreenUpdating = True
  18.  
  19.   copySheet.Range("POdate").Copy
  20.   pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  21.   Application.CutCopyMode = False
  22.   Application.ScreenUpdating = True
  23.  
  24.   copySheet.Range("RequestedBy").Copy
  25.   pasteSheet.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  26.   Application.CutCopyMode = False
  27.   Application.ScreenUpdating = True
  28.  
  29.   copySheet.Range("Vendor").Copy
  30.   pasteSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  31.   Application.CutCopyMode = False
  32.   Application.ScreenUpdating = True
  33.  
  34.   copySheet.Range("DeliveryDate").Copy
  35.   pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  36.   Application.CutCopyMode = False
  37.   Application.ScreenUpdating = True
  38.  
  39.   copySheet.Range("Cost").Copy
  40.   pasteSheet.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  41.   Application.CutCopyMode = False
  42.   Application.ScreenUpdating = Tru
  43.  
  44. If Cells(4, 3).Value = "" Then
  45. MsgBox "Requested By Requires User Input"
  46.  
  47. Cancel = True
  48. End If
  49.  
  50. Range("='Purchase Order'!$B$2:$N$47").PrintOut
  51.  
  52.   End Sub
1 Week Ago #2

P: 3
For all that are interested.... The solution to my issue was
Expand|Select|Wrap|Line Numbers
  1.  
  2.  If IsEmpty(Range("OrderSummary")) Or IsEmpty(Range("RequestedBy")) Or IsEmpty(Range("POnumber")) Then
  3.  MsgBox "Information is Missing:Requested By, Order Summary, and P.O. Require User Input", vbRetryCancel
  4.  Exit Sub
  5.  End If
That is all...
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.