Macro help here!!!!

[thunder_anger]

<TABLE style="WIDTH: 108pt; BORDER-COLLAPSE: collapse" dir=rtl border=0 cellSpacing=0 cellPadding=0 width=144><COLGROUP><COL style="WIDTH: 54pt" span=2 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 height=19 width=72 align=left>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 width=72 align=left>Stock</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 height=19 align=left>Tea</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl64 align=right>30</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 height=19 align=left>Coffee</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl64 align=right>100</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 height=19 align=left>Gum</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl64 align=right>125</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl63 height=19 align=left>Water</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl64 align=right>400</TD></TR></TBODY></TABLE>

in the above example i made this macro not to allow user to delete or edit any cell that contans an item that have a stock so if he tries to delete the word tea a message box will appear saying that this item can not be deleted because the stock is greater than zero

Sub Hos()
Dim StRng As Range
Dim StRngC As Range
Set StRng = Sheet1.Range("A6:A8")
For Each StRngC In StRng
If StRngC.Value = "" And StRngC.Offset(0, 1) > 0 Then
MsgBox "you can not delete the item! stock greater than (0)"
Application.Undo
End If
Next StRngC
End Sub

and this code for the sheet1 change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A6:A8"), Target) Is Nothing Then Call Hos
End Sub

but i discovered that i can edit the cells

any one help me not to allow users edit or delete an item unless stock is 0

 

[thunder_anger]

Any Body
i am still waiting......

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Range("A6:A8"), Target) Is Nothing Then
    For Each c In Target
        If c.Value = "" And c.Offset(, 1).Value <> "" Then
            Application.EnableEvents = False
            Application.Undo
            MsgBox "verboten", vbInformation
            Application.EnableEvents = True
            Exit Sub
        End If
    Next c
End If
End Sub

 

[thunder_anger]

Thanks for the reply
but
I still can edit the cells so i can change the words in cells i can change Tea to thunder and that is not ok

 

[VoG]

Why not simply protect the cells that cannot be changed?

 

[sykes]

thunder anger

How about simply applying a test to the number in stock, when the "item" cell is selected, and if there are stock items still remaining, "Bounce" the user into a different cell, then give them the message:
NB this must be in the worksheet's code module (where you've got your intersect code already)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

Set rng = Me.Range("B2:B5")

    If Not Intersect(rng, Target) Is Nothing Then
        If Target.Offset(0, -1).Value > 0 Then
            Me.Range("A1").Select
            MsgBox "You can't edit that cell - there are still some units in stock!", 48, "don't touch !!"
        End If
    End If
End Sub

 

[sykes]

...........Do make sure that you change the tested range of my code ("rng") to that of your ITEMS column, and NOT your STOCK column, so that the code fires when the user selects "Tea, Coffee" etc. This is because it's these entries you've stated you wish to protect from being changed when there are still some in stock.

 

[thunder_anger]

Sykes

Thank you it worked like a charm

 

[sykes]

Pleasure, and glad it worked for you.