Worksheet Event Code to fill the highlighted.

[adam]

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.

[HansV]

Right-click the sheet tab of the Previous sheet and copy the following code into the worksheet module:

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

The Invoice sheet doesn't contain an invoice date so I don't know what to enter in cell O11.

See the attached version.

Book1.xlsm

[adam]

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.

[HansV]

If the user clears 0006 and then presses Save, how would the code know that 0006 had been deleted, and not 0005?

[adam]

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.

[HansV]

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.

[adam]

I would be happy if a suggestion or an alternative is being given; how to develop this idea.

Thanks in advance.

[HansV]

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.

[adam]

Thanks for the reply; Hans.

[PJ_in_FL]

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?

[adam]

Thanks for the suggestion. How could that be done?

[PJ_in_FL]

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.

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

[adam]

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.

[PJ_in_FL]

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.

[adam]

Thanks for the help PJ_in_FL I do appreciate it. and do take your advice.

[adam]

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.

[adam]

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.

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

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.

[HansV]

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.

[adam]

Thanks for the help Hans. But the code still Does not work. What may be the reason for this?