VBA Questions For Auto filling Cells

[data808]

I am using this and it works fine:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("E:E"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("K" & Target.Row)
                ActiveSheet.Unprotect Password:="hello"
                .Value = "TEST"
        With Range("I" & Target.Row)
                .Value = "Stuff"
                ActiveSheet.Protect Password:="hello"
        End With
        End With
        Application.EnableEvents = True
        End If
    End With
End Sub

But now I want to add some if statements to make the user have to type in specific codes for it to trigger. Right now as long as they type anything into column E, it will trigger column I and K to auto-fill "TEST" and "Stuff".

How would I change this VBA so that if the user types "1234" into column E for example, only then will it trigger to auto-fill column I and K?

I would also like it if the user types anything other than "1234" or deletes it to leave column E blank, then columns I and K, for that row, will go back to being null or blank.

Thank you for your help on this.

 

[data808]

Unless I have misunderstood what you are saying, there should be no issue at all with a structure like that. Here it is working ..

data808.xlsm
	A	B
4
5
6	abc 123
7
8
Sheet1

data808.xlsm
	A	B
12
13
14	abc 123
15
16
Sheet2

Cell Formulas
Range	Formula
A14	A14	=Sheet1!A6

Yeah it's odd. It works for me too if I just do it from a blank spreadsheet but once I try it on the one we been working on, it doesn't work. I notice that when I add this =Sheet1!A6 to the cell, that formula does not disappear from the cell. It will register as if it were a value so that tells me it's not recognizing it as a formula but rather a value.

 

[data808]

Unless I have misunderstood what you are saying, there should be no issue at all with a structure like that. Here it is working ..

data808.xlsm
	A	B
4
5
6	abc 123
7
8
Sheet1

data808.xlsm
	A	B
12
13
14	abc 123
15
16
Sheet2

Cell Formulas
Range	Formula
A14	A14	=Sheet1!A6

So it appears to not allow any type of formulas on this sheet. Every type of formula that I type into any of the cells on this sheet will only show the formula in the cell itself but not function at all. If I create a new blank sheet in this workbook it works fine. But if I copy one of the cells from the non-working sheet and paste it into one of the blank sheets, that cell will become unusable to any formulas. Is there any setting in the ribbon that would cause this? I may have touched something to cause all formulas to not function on this sheet.

 

[data808]

Unless I have misunderstood what you are saying, there should be no issue at all with a structure like that. Here it is working ..

data808.xlsm
	A	B
4
5
6	abc 123
7
8
Sheet1

data808.xlsm
	A	B
12
13
14	abc 123
15
16
Sheet2

Cell Formulas
Range	Formula
A14	A14	=Sheet1!A6

I found it. It's because I formatted the cells to Text instead of General. When in General format, the formula works.

 

[Peter_SSs]

Glad you got it sorted.