none
Automatically update a table in one sheet directly from another data entry sheet RRS feed

  • Question

  • I am working n excel 2013, and borrowed the code from the following page:

    http://www.contextures.com/excel-data-validation-add-tables.html

    In order to be able to add or change products in a table on one sheet directly from the data entry sheet (a separate worksheet).

    The sort code did not work but the data entry code works great. If I type something that is not on the list it creates a pop up box that asks me if I want to add it to the list, I say yes, and it adds it to the table (it adds it at the bottom of the table and I have to manually sort it in the table, if I want it sorted, so the sort code isn't working for whatever reason but not a big deal).

    However, I can only update the product, which is in the first column of the table, and not the price of the product, which is in another column on both the data entry sheet and the table.  Is there a way I could also, at the same time I update the product, also update the price, or even just update the price and not the product, all from the data entry sheet?


    Wednesday, February 25, 2015 12:15 AM

Answers

  • Hi

    >>Is there a way I could also, at the same time I update the product, also update the price, or even just update the price and not the product, all from the data entry sheet?

    I have followed the steps to add an item in the dropdown box, and this is the code to add the item from another sheet

    Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    On Error Resume Next

    Dim ws As Worksheet

    Dim str As String

    Dim i As Integer

    Dim rngDV As Range

    Dim rng As Range

    Dim lCol As Long

    Dim myRsp As Long

    Dim strList As String

    If Target.Count > 1 Or Target.Value = "" Then Exit Sub

     

    If Target.Row > 1 Then

       If Target.Validation.Type <> 3 Then Exit Sub

      str = Target.Validation.Formula1

      str = Right(str, Len(str) - 1)

     

      Set rng = ThisWorkbook.Names(str).RefersToRange

    ' also need to check the targrt  is in Price column

      If rng Is Nothing Then Exit Sub

       Set ws = rng.Parent

     

      If Application.WorksheetFunction _

        .CountIf(rng, Target.Value) Then

        Exit Sub

      Else

       myRsp = MsgBox("Add this item to the drop down list?", _

          vbQuestion + vbYesNo + vbDefaultButton1, _

          "New Item -- not in drop down")

       If myRsp = vbYes Then

          lCol = rng.Column

          i = ws.Cells(Rows.Count, lCol).End(xlUp).Row + 1

          ws.Cells(i, lCol).Value = Target.Value

         

          strList = ws.Cells(1, lCol).ListObject.Name

      

          With ws.ListObjects(strList).Sort

             .SortFields.Clear

             .SortFields.Add _

                 Key:=Cells(2, lCol), _

                 SortOn:=xlSortOnValues, _

                 Order:=xlAscending

             .Header = xlYes

             .MatchCase = False

             .Orientation = xlTopToBottom

             .SortMethod = xlPinYin

             .Apply

          End With

         

          With ws.ListObjects(strList)

            .Resize .DataBodyRange.CurrentRegion

          End With

         

       End If

      End If

    End If

    End Sub

    I am afraid it can't update product and price columns at the same time in Worksheet_Change event. But if you want to update the price and not the product, you need to add a check to determine whether the target is in Price column.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by L.HlModerator Wednesday, March 4, 2015 9:00 AM
    • Marked as answer by L.HlModerator Thursday, March 5, 2015 2:06 AM
    Friday, February 27, 2015 1:32 AM
    Moderator

All replies

  • This appears to be the same question that you posted at the following link so if you can upload a copy of your workbook as requested then I will take a look at it.

    http://answers.microsoft.com/en-us/office/forum/office_2013_release-customize/automate-updating-a-table-in-a-separate-worksheet/b02fba60-2060-4202-9569-732c5f93cb07


    Regards, OssieMac

    Thursday, February 26, 2015 1:49 AM
  • Hi

    >>Is there a way I could also, at the same time I update the product, also update the price, or even just update the price and not the product, all from the data entry sheet?

    I have followed the steps to add an item in the dropdown box, and this is the code to add the item from another sheet

    Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    On Error Resume Next

    Dim ws As Worksheet

    Dim str As String

    Dim i As Integer

    Dim rngDV As Range

    Dim rng As Range

    Dim lCol As Long

    Dim myRsp As Long

    Dim strList As String

    If Target.Count > 1 Or Target.Value = "" Then Exit Sub

     

    If Target.Row > 1 Then

       If Target.Validation.Type <> 3 Then Exit Sub

      str = Target.Validation.Formula1

      str = Right(str, Len(str) - 1)

     

      Set rng = ThisWorkbook.Names(str).RefersToRange

    ' also need to check the targrt  is in Price column

      If rng Is Nothing Then Exit Sub

       Set ws = rng.Parent

     

      If Application.WorksheetFunction _

        .CountIf(rng, Target.Value) Then

        Exit Sub

      Else

       myRsp = MsgBox("Add this item to the drop down list?", _

          vbQuestion + vbYesNo + vbDefaultButton1, _

          "New Item -- not in drop down")

       If myRsp = vbYes Then

          lCol = rng.Column

          i = ws.Cells(Rows.Count, lCol).End(xlUp).Row + 1

          ws.Cells(i, lCol).Value = Target.Value

         

          strList = ws.Cells(1, lCol).ListObject.Name

      

          With ws.ListObjects(strList).Sort

             .SortFields.Clear

             .SortFields.Add _

                 Key:=Cells(2, lCol), _

                 SortOn:=xlSortOnValues, _

                 Order:=xlAscending

             .Header = xlYes

             .MatchCase = False

             .Orientation = xlTopToBottom

             .SortMethod = xlPinYin

             .Apply

          End With

         

          With ws.ListObjects(strList)

            .Resize .DataBodyRange.CurrentRegion

          End With

         

       End If

      End If

    End If

    End Sub

    I am afraid it can't update product and price columns at the same time in Worksheet_Change event. But if you want to update the price and not the product, you need to add a check to determine whether the target is in Price column.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by L.HlModerator Wednesday, March 4, 2015 9:00 AM
    • Marked as answer by L.HlModerator Thursday, March 5, 2015 2:06 AM
    Friday, February 27, 2015 1:32 AM
    Moderator