update value and edit items already update on sheet by userform

Hi tjxc32m

Try the Code in the attached.

User can Add New Recipe Items and or change Quantities on existing Items. User cannot modify Price or Cost.

Price is changed only on the Master List. Changes made on the Master List also update all Recipe Worksheets.

Password is "password" without the quotes.

Hi tjxc32m

I'd suggest those cells are not Locked. Unprotect the Sheet, Lock the Cells and Protect the Sheet...Save.

Regarding this...actually the Code does just that

i want to edit the quantity part of my say i want to change it from 8 grms to 10 grms and also the respective change in pricing is also done when the quantity is changed. all this with out unlocking the sheet.

Try it...

PS: you'll need to check ALL your Worksheets to make certain ALL the Cells are Locked...

Hi tjxc32m

Who can update the Master Sheet?

Hi tjxc32m

Some of your sheets are protected by an unknown password. What is it?

Hi tjxc32m

In the attached this Code is in ThisWorkbook Module and runs each time the Workbook is opened. It protects each Worksheet with the appropriate Password; the Protection applied allows Macros to run on Protected Worksheets.

Private Sub Workbook_Open()
    Dim PW As String, PW1 As String
    Dim wSht As Worksheet

    PW = "godspeed"
    PW1 = "edolpsgge"
    For Each wSht In ActiveWorkbook.Sheets
        If wSht.Name = "ITEMS MASTER LIST" Then
            'set protection using UserInterface to allow macros to work
            With wSht
                .Protect _
                        Password:=PW1, _
                        DrawingObjects:=True, _
                        Contents:=True, _
                        Scenarios:=True, _
                        UserInterfaceOnly:=True
                wSht.EnableSelection = xlUnlockedCells
            End With
        Else
            With wSht
                .Protect _
                        Password:=PW, _
                        DrawingObjects:=True, _
                        Contents:=True, _
                        Scenarios:=True, _
                        UserInterfaceOnly:=True
                wSht.EnableSelection = xlUnlockedCells
            End With
        End If
    Next wSht
End Sub

This Code is in "ITEM MASTER LIST" Code Module and allows changes made to "ITEM MASTER LIST" to be populated to all other Worksheets (without unprotecting the other Worksheets). You WILL need to unprotect "ITEM MASTER LIST" to make changes and then protect it after your changes are made.

You COULD unlock the Cells you wish to change but then they would be available to anyone with access to "ITEM MASTER LIST".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range, cel As Range

    If Not Target.Column = 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "ITEMS MASTER LIST" Then
            With ws
                Set rng = .Columns(3)
                Set cel = rng.Find(Target.Offset(0, -3).Value, , xlValues, xlWhole, xlByRows, xlNext, False)
                If Not cel Is Nothing Then
                    cel.Offset(0, 3).Value = Target.Offset(0, 1).Value
                    cel.Offset(0, 4).Value = cel.Offset(0, 1).Value * cel.Offset(0, 3).Value
                End If
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

This Code (revised from your original Code) is in the UserForm and is attached to the Submit Button

Private Sub btnsubmit_Click()
    Dim rowcount As Long
    Dim c As Range, rng As Range
    If Me.Cbitem.Text = "" Then
        MsgBox " Please Enter Ingredient "
        Me.Cbitem.SetFocus
    End If
    'range above item
    If Not IsNumeric(Me.tbqty.Value) Then
        MsgBox " Quantity to be Numeric Value "
        Me.tbqty.SetFocus
    End If
    'range above quantity
    If Me.Cbunit.Text = "" Then
        MsgBox " Unit to be Metrics "
        Me.Cbunit.SetFocus
    End If
    'range about unit
    If Not IsNumeric(Me.cbprice.Value) Then
        MsgBox " Price to be Numeric Value "
        Me.cbprice.SetFocus
    End If
    'range for price
    If Me.cbprice.Value = 0 Then
        MsgBox " NO PRICE ASSIGNED "
        Me.cbprice.SetFocus
    End If
    'range for no price input

    With ActiveSheet
        Set rng = .Range(("C6"), .Range("C6").End(xlDown))

        Set c = rng.Find(Me.Cbitem.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            '            Me.btnundo.Enabled = False
            .Cells(c.Row, 3) = Cbitem.Text
            .Cells(c.Row, 4) = tbqty.Value
            .Cells(c.Row, 5) = Cbunit.Text
            .Cells(c.Row, 6) = cbprice.Value
            .Cells(c.Row, 7) = tbcost.Value
            '            Me.btnundo.Enabled = False
        Else
            '            Me.btnundo.Enabled = True
            erow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

            .Cells(erow, 3) = Cbitem.Text
            .Cells(erow, 4) = tbqty.Value
            .Cells(erow, 5) = Cbunit.Text
            .Cells(erow, 6) = cbprice.Value
            .Cells(erow, 7) = tbcost.Value

        End If
        Flag = True
        Cbitem.ListIndex = -1
        tbqty.Value = ""
        Cbunit.Value = ""
        cbprice.Value = ""
        tbcost.Value = ""
        Flag = False
    End With
End Sub