Worksheet Event Code to fill the highlighted.
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Worksheet Event Code to fill the highlighted.
Hi anyone,
I'm trying to figure out a code that would fill the "yellow highlighted" cells in the "previous" sheet by appropriate data from the sheet "Invoice", when the serial number is written in the cell O6 of the sheet "previous".
I've attached the workbook of interest for further explanation.
Any help on this would be kindly appreciated.
Thanks in advance.
I'm trying to figure out a code that would fill the "yellow highlighted" cells in the "previous" sheet by appropriate data from the sheet "Invoice", when the serial number is written in the cell O6 of the sheet "previous".
I've attached the workbook of interest for further explanation.
Any help on this would be kindly appreciated.
Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet Event Code to fill the highlighted.
Right-click the sheet tab of the Previous sheet and copy the following code into the worksheet module:
The Invoice sheet doesn't contain an invoice date so I don't know what to enter in cell O11.
See the attached version.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wshI As Worksheet
Dim rng As Range
If Not Intersect(Range("O6"), Target) Is Nothing Then
Set wshI = Worksheets("Invoice")
Set rng = wshI.Range("B:B").Find(What:=Range("O6").Value, LookAt:=xlWhole)
If rng Is Nothing Or Range("O6") = "" Then
Range("I9:I10,M9:M10,M15,O9:O11").ClearContents
Else
Range("I9") = rng.Offset(0, 13)
Range("I10") = rng.Offset(0, 1)
Range("M9") = rng.Offset(0, 11)
Range("M10") = rng.Offset(0, 2)
Range("M15") = rng.Offset(0, 5)
Range("O9") = rng.Offset(0, 12)
Range("O10") = rng.Offset(0, 10)
Range("O11") = "?"
End If
End If
End Sub
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Thanks for the help Hans. The cell O11 was a posting mistake. I've now removed that part from the code.
Also I've modified your code with additional lines so that when the serial number is written in the cell "O6" the columns F & N gets filled.
What I'm trying to get is; suppose if the user clears the code 0006 from the sheet "Previous" and press the save button, How could the row containing the code 0006 would be deleted from the sheet "Orders".
Any help on this would be kindly appreciated.
I've attached the workbook for further reference.
Thanks in advance.
Also I've modified your code with additional lines so that when the serial number is written in the cell "O6" the columns F & N gets filled.
What I'm trying to get is; suppose if the user clears the code 0006 from the sheet "Previous" and press the save button, How could the row containing the code 0006 would be deleted from the sheet "Orders".
Any help on this would be kindly appreciated.
I've attached the workbook for further reference.
Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet Event Code to fill the highlighted.
If the user clears 0006 and then presses Save, how would the code know that 0006 had been deleted, and not 0005?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
When the code 0006 is cleared the adjacent column where formulas have been applied will get cleared from the previous sheet. So when the save button is pressed the code could look for the serial number and save data from the previous sheet to the Orders sheet with the row containing the code 0006 being deleted.
I hope this makes the question clear.
I hope this makes the question clear.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet Event Code to fill the highlighted.
But the serial number 0006 isn't available any more in the Previous sheet - you just deleted it. So the code doesn't know which serial number to delete.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
I would be happy if a suggestion or an alternative is being given; how to develop this idea.
Thanks in advance.
Thanks in advance.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet Event Code to fill the highlighted.
You're still trying to create a relational database in Excel; I won't go into the arguments again why that is a bad idea.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- 5StarLounger
- Posts: 1108
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Worksheet Event Code to fill the highlighted.
Could you add a column to the Orders sheet for a "Deleted" flag then flag the row 0006 as deleted but keep it in the Orders sheet?
PJ in (usually sunny) FL
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Thanks for the suggestion. How could that be done?
Best Regards,
Adam
Adam
-
- 5StarLounger
- Posts: 1108
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Worksheet Event Code to fill the highlighted.
First, what are you trying to accomplish by removing the records associated with a particular order number?
Do you want to limit the number of records in "Orders"?
I can see the scenario of someone clearing Serial Number and pressing the SAVE button, but the user doesn't want to delete the data or expect that the data will be deleted, as that's action is not explicitly called out. They may just want to clear the form and then type in a new serial number.
If you clear the records out in that instance, you'll permanently remove the data, which may not be the user's intent.
However, if you want to manage the "database" in the "Orders" sheet, one suggestion would be to create a button called "Delete", and build a macro -- with a verification step! -- to remove all rows in "Orders" that have the same serial number.
To provide a delete function, put this code into a MODULE and assign the "DeleteOrder" macro to a button on the "Previous" sheet.
Do you want to limit the number of records in "Orders"?
I can see the scenario of someone clearing Serial Number and pressing the SAVE button, but the user doesn't want to delete the data or expect that the data will be deleted, as that's action is not explicitly called out. They may just want to clear the form and then type in a new serial number.
If you clear the records out in that instance, you'll permanently remove the data, which may not be the user's intent.
However, if you want to manage the "database" in the "Orders" sheet, one suggestion would be to create a button called "Delete", and build a macro -- with a verification step! -- to remove all rows in "Orders" that have the same serial number.
To provide a delete function, put this code into a MODULE and assign the "DeleteOrder" macro to a button on the "Previous" sheet.
Code: Select all
Option Explicit
Sub DeleteOrder()
Dim serial As Integer
Dim ans As Integer
Dim i As Integer
' grab the serial order serial number and confirm user WANTS to delete order
serial = Worksheets("Previous").Range("o6").Value
ans = MsgBox("Delete order number " & str(serial) & " ?", vbYesNo, "Delete Order")
' if they really do, then remove all the records that contain that order number
If ans = vbYes Then
Application.ScreenUpdating = False
Worksheets("Orders").Activate
' find last row
Range("b4").Select
Selection.End(xlDown).Select
' set the search range from the last row to the row below the labels
For i = ActiveCell.Row To 5 Step -1
If Cells(i, "B") = serial Then Cells(i, "B").EntireRow.Delete
Next i
End If
Worksheets("Previous").Activate
Range("o6").Activate
Application.ScreenUpdating = True
End Sub
PJ in (usually sunny) FL
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Thanks for the code. But your code deletes all the records related with the serial number; lets say for example 0133.
What I'm trying to accomplish is when the user clears the number from the sheet previous; lets say for example 0006 only the row containing the number 0006 with the serial number 0133 gets deleted from the sheet Orders when the macro button is clicked. Not all the records related to the serial number 0133 should get deleted.
I hope this makes my question clear.
What I'm trying to accomplish is when the user clears the number from the sheet previous; lets say for example 0006 only the row containing the number 0006 with the serial number 0133 gets deleted from the sheet Orders when the macro button is clicked. Not all the records related to the serial number 0133 should get deleted.
I hope this makes my question clear.
Best Regards,
Adam
Adam
-
- 5StarLounger
- Posts: 1108
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Worksheet Event Code to fill the highlighted.
Sorry for the misunderstanding.
Then if I understand correctly, you want to remove all rows of items from the "Orders" sheet that have been removed from the "Previous" sheet. The problem I believe other posters have pointed out is that you want "Orders" updated in "real-time" as soon as a row is cleared in "Previous". I believe I understood previous (and MUCH more experienced) posters have indicated there are problems with that strategy.
However, if your SAVE macro writes everything from "Previous" into "Orders", then will your application work if the "Orders" sheet is updated (new rows added, old rows deleted) only when the SAVE button is pushed? The example I downloaded from your previous post didn't have that macro so I can't look at what SAVE does.
If so, then call the DeleteOrder subroutine I posted in the SAVE macro just before you save all the data from "Previous" into "Orders". The end result will be the same.
Then if I understand correctly, you want to remove all rows of items from the "Orders" sheet that have been removed from the "Previous" sheet. The problem I believe other posters have pointed out is that you want "Orders" updated in "real-time" as soon as a row is cleared in "Previous". I believe I understood previous (and MUCH more experienced) posters have indicated there are problems with that strategy.
However, if your SAVE macro writes everything from "Previous" into "Orders", then will your application work if the "Orders" sheet is updated (new rows added, old rows deleted) only when the SAVE button is pushed? The example I downloaded from your previous post didn't have that macro so I can't look at what SAVE does.
If so, then call the DeleteOrder subroutine I posted in the SAVE macro just before you save all the data from "Previous" into "Orders". The end result will be the same.
PJ in (usually sunny) FL
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Thanks for the help PJ_in_FL I do appreciate it. and do take your advice.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
The cells highlighted in yellow gets filled when the serial number is written in cell O6 with the help of the code provided in Post 40750.
If the user edits any of the copied fields and press a save button how could the changed cell be made to get saved to the appropriate columns of the Invoice sheet?
Any help on this would be kindly appreciated.
If the user edits any of the copied fields and press a save button how could the changed cell be made to get saved to the appropriate columns of the Invoice sheet?
Any help on this would be kindly appreciated.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Here is the code I've got so far. But this code does not seem to work. I've adopted this code from a reply by HansV in a previous post and modified a little.
I've also applied formulas as per the requirements. Still the code does not seem to work.
Formulas applied is as below
=OFFSET(Invoice!$A$3,1,MATCH("Serial ", Invoice!$3:$3,0)-1,COUNTA(Invoice!$A:$A)-1,1)
=OFFSET(Invoice!$A$3,1,MATCH("Customer ID ", Invoice!$3:$3,0)-1,COUNTA(Invoice!$A:$A)-1,1)
and so on
Any help to over come this would be kindly appreciated.
Thanks in advance.
I've also applied formulas as per the requirements. Still the code does not seem to work.
Code: Select all
Sub Edit()
Dim r As Long
On Error Resume Next
r = Application.WorksheetFunction.Match(CLng(Range(“O6â€).Value), _
Range("Serial"), 0)
If Err = 0 Then
Range("CustomerID").Cells(r).Value = Range(“I10â€).Value
Range("ReceiptNo").Cells(r).Value = Range(“M10â€).Value
Range("BillUser").Cells(r).Value = Range(“M15â€).Value
Range("RequestNo").Cells(r).Value = Range(“O15â€).Value
Range("Insured").Cells(r).Value = Range(“M9â€).Value
Range("PriceType").Cells(r).Value = Range(“O10â€).Value
Range("Location").Cells(r).Value = Range(“O9â€).Value
Range("PaymentType").Cells(r).Value = Range(“I9â€).Value
End If
ThisWorkbook.Save
End Sub
=OFFSET(Invoice!$A$3,1,MATCH("Serial ", Invoice!$3:$3,0)-1,COUNTA(Invoice!$A:$A)-1,1)
=OFFSET(Invoice!$A$3,1,MATCH("Customer ID ", Invoice!$3:$3,0)-1,COUNTA(Invoice!$A:$A)-1,1)
and so on
Any help to over come this would be kindly appreciated.
Thanks in advance.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet Event Code to fill the highlighted.
If you look closely at Range(“I10â€).Value etc., you'll see that you have used curly quotes “ †instead of straight quotes " ". VBA only works with straight quotes.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Worksheet Event Code to fill the highlighted.
Thanks for the help Hans. But the code still Does not work. What may be the reason for this?
Best Regards,
Adam
Adam