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.

 

[MountainFog]

Could you wrap an exit sub in an if statement? If value is not equal to "1234", then exit sub?

 

[data808]

Could you wrap an exit sub in an if statement? If value is not equal to "1234", then exit sub?

Thats a good idea. I think I will try messing with that. I just don't know how to add an if statement with the existing code.

I just realized that I will also need a way to auto-fill each row, that is typed into for column E, with a numeric sequence for column A. So for example, I type in E1 cell then it will trigger the auto-fills for the other cells but I would like A1 to fill with the number "01" in it. Then if I type into cell E3 then I would like it to fill A3 with the number "02" in it, and so forth...

Do you know how to do this kind of magic? lol

 

[Peter_SSs]

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.

Give this code a try with a copy of your workbook. I have changed the concept a bit, not sure if that will suit you.

This code allows several cells at once in column E to be altered and still do the relevant things in columns I & K. This could be relevant if, for example, a few cells are selected in column E and 'Delete' is pressed. The code would then clear column I & K in each of those rows whereas your original code would simply exit since the Target count would be > 1
Or you could select a few cells in column E, type "1234" and confirm with Ctrl+Enter to enter several cells at once. Or several cells might be copied from elsewhere and pasted into column E. In each case all the relevant rows will be processed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("E"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="hello"
    For Each c In Changed
      If c.Text = "1234" Then
        Range("K" & c.Row).Value = "TEST"
        Range("I" & c.Row).Value = "Stuff"
      Else
        Range("K" & c.Row).ClearContents
        Range("I" & c.Row).ClearContents
      End If
    Next c
    ActiveSheet.Protect Password:="hello"
    Application.EnableEvents = True
  End If
End Sub

I just realized that I will also need a way to auto-fill each row, that is typed into for column E, with a numeric sequence for column A. So for example, I type in E1 cell then it will trigger the auto-fills for the other cells but I would like A1 to fill with the number "01" in it. Then if I type into cell E3 then I would like it to fill A3 with the number "02" in it, and so forth...

I don't understand how that would work. If you filled E1, E2, E3, E4 & E5 then column A would look like this

data808.xlsm
	A
1	01
2	02
3	03
4	04
5	05
Sheet1

What should happen if now E3 and E4 are cleared? Would A3:A4 also get cleared and look like this?

data808.xlsm
	A
1	01
2	02
3
4
5	05
Sheet1

If so, what happens if 1234 is then re-entered in E4, what number would go in A4 since there is now no continuous sequence in column A?

Also, are the numbers in column A dependant on the value in column E being "1234" or do the numbers get entered no matter what value is entered in column E?

 

[data808]

Give this code a try with a copy of your workbook. I have changed the concept a bit, not sure if that will suit you.

This code allows several cells at once in column E to be altered and still do the relevant things in columns I & K. This could be relevant if, for example, a few cells are selected in column E and 'Delete' is pressed. The code would then clear column I & K in each of those rows whereas your original code would simply exit since the Target count would be > 1
Or you could select a few cells in column E, type "1234" and confirm with Ctrl+Enter to enter several cells at once. Or several cells might be copied from elsewhere and pasted into column E. In each case all the relevant rows will be processed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("E"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="hello"
    For Each c In Changed
      If c.Text = "1234" Then
        Range("K" & c.Row).Value = "TEST"
        Range("I" & c.Row).Value = "Stuff"
      Else
        Range("K" & c.Row).ClearContents
        Range("I" & c.Row).ClearContents
      End If
    Next c
    ActiveSheet.Protect Password:="hello"
    Application.EnableEvents = True
  End If
End Sub

I don't understand how that would work. If you filled E1, E2, E3, E4 & E5 then column A would look like this

data808.xlsm
	A
1	01
2	02
3	03
4	04
5	05
Sheet1

What should happen if now E3 and E4 are cleared? Would A3:A4 also get cleared and look like this?

data808.xlsm
	A
1	01
2	02
3
4
5	05
Sheet1

If so, what happens if 1234 is then re-entered in E4, what number would go in A4 since there is now no continuous sequence in column A?

Also, are the numbers in column A dependant on the value in column E being "1234" or do the numbers get entered no matter what value is entered in column E?

Thank you very much for your help on this. I have tried your code for the auto-filling of columns I and K. It's working exactly how I want it. As for the counting sequence for column A, I apologize I wasn't clear. I will be typing into every other row. So basically, E1, E3, E5, E7, etc... the number in column A will represent the amount of items listed. Each row that is filled out equals one item. So if I only type in E1, E3, and E5 then A1 will be "01", A3 will be "02", and A5 will be "03". Does that make sense? It would be cool if I were to delete one of the values from column E then the contents would also clear in column A for that row too but if that's too hard then I can just delete it manually. Again, thank you for your help on this.

 

[Peter_SSs]

So basically, E1, E3, E5, E7, etc... the number in column A will represent the amount of items listed. Each row that is filled out equals one item. So if I only type in E1, E3, and E5 then A1 will be "01", A3 will be "02", and A5 will be "03". Does that make sense?

Yes

It would be cool if I were to delete one of the values from column E then the contents would also clear in column A for that row too

This is not quite so clear to me.
If the value deleted was the value in E3 then A3 would be cleared. That is straight forward enough.
What I am not so sure about: Would A5 remain as "03" or would it change to "02" since now there are only two values in column E?

 

[data808]

Yes


This is not quite so clear to me.
If the value deleted was the value in E3 then A3 would be cleared. That is straight forward enough.
What I am not so sure about: Would A5 remain as "03" or would it change to "02" since now there are only two values in column E?

Thanks for bringing that up. The most common scenario would be that I always have consistent items listed with no spaces in between each row.

So for example: If I have 5 items to list then rows 1, 3, 5, 7, and 9 will be used. Then the next time I use this form if I only have 4 items to list then I would use rows 1, 3, 5, and 7. I believe the most items I would have in this form is 14 total and I would always like column A to reflect a two digit counter, meaning 01-09 for the first 9 rows. Then 10-14 for the next 4 rows. I think I can handle that with the formatting of the cell to change it to text so the leading 0 for the first 9 rows will reflect in the cell. Usually by default if I type 01 into a cell, it will remove the 0 and just reflect 1.

 

[data808]

Sorry forgot to mention that I would only like the counter to trigger when a row has values entered into it. If the row is blank with no values then no counter is needed for that row.

Thank you.

 

[Peter_SSs]

OK, see if this would suit you.

1. Format column A as 'General' (Unprotect the sheet first if required)
2. Put this formula in cell A1 and copy it down column A to cell A28. =IF(E1="","",TEXT(COUNTA(E$1:E1),"00"))
3. Use the Worksheet_Change code from post #4

 

[data808]

OK, see if this would suit you.

1. Format column A as 'General' (Unprotect the sheet first if required)
2. Put this formula in cell A1 and copy it down column A to cell A28. =IF(E1="","",TEXT(COUNTA(E$1:E1),"00"))
3. Use the Worksheet_Change code from post #4

Works like a charm. Thank you so much for all your help. This is brilliant and will make life a lot easier. Thanks again.