worksheet change event code problem

[supermom28115]

I am working with a worksheet where the user enters data and at several places they indicate whether the value in a cell will be the same value as the cell just entered. I was trying to write a worksheet change event code that would check for the target cell (row 20 of any column) value to be equal to yes and if so make the value in cell 21 (same column) equal to cell 19(same column). This is only my 4th or 5th set of code that I have written that has been productive in that something happened. I can not enter anything into my worksheet at all now. It is a new workbook and only has 5 sheets and this sheet has one column of labels and one column of data. It did have 6 columns of data, but stuff happened. Anyway, I tried to debug but it didn't step into the code even after i reset the code. I am sure I have done something wrong that is so simple but I have already spent 6 hours trying to find any reference to dealing with a target range that was an entire row. I found a few that discussed columns but the data and action needed were extremely different than my needs.
I am simply looking for a set of code that will recognize that the cell changed, check for yes (no means do nothing) and make the two cells values the same.
This set of code will be a workhorse in my spreadsheets due to so much of my data is very likely the same, but the data structure for each data type will be different so I will need to be able to repurpose the code in multiple places on the sheet, in the workbook, and in other workbooks.
This is my first post so if I haven't provided the right or enough information I apologize and do appreciate any help you can provide this new vba user.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range

 If Not Intersect(Target, Range("20:20")) Is Nothing Then
 
 Set changed = Target.Address
    If changed = "yes" Then
        changed.Offset(1, 0).Value = changed.Offset(-1, 0).Value
        
    
   Application.EnableEvents = False
   Target = newinput
   Application.EnableEvents = True
   End If
 End If
 
End Sub

 

[TryingToLearn]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range

 If Not Intersect(Target, Range("20:20")) Is Nothing Then
 
 Set changed = Target.Address
    If changed = "yes" Then
[/QUOTE]
             Application.EnableEvents = False
[QUOTE="supermom28115, post: 5881497, member: 492960"]

        changed.Offset(1, 0).Value = changed.Offset(-1, 0).Value
       
 
   Target = newinput
   Application.EnableEvents = True
   End If
 End If
 
End Sub

l think you are looping a change event. Try turning off events a little earlier in the routine.

 

[supermom28115]

I moved the enable events false statement to your suggestion and still can not put data into the worksheet. i can move around and select cells but can not enter data into any cell. I also reset the code after making the change. Thank you for your advice, more than likely it is only one of many problems.

 

[My Aswer Is This]

Are you looking to see if the value "Yes" is entered into any cell in row 20?
If not what must be entered for the script to activate and then do what?

 

[supermom28115]

Are you looking to see if the value "Yes" is entered into any cell in row 20?
If not what must be entered for the script to activate and then do what?

I am looking for the value yes in row 20, if not yes then i dont need this code to run at all. It's only job is to make cell 21 the same as cell 19 if the word yes is in 20. After the workbook is set up and functioning then possibly 6 - 12 records a month may be added to this worksheet. All the worksheets within the workbook will have similar record entry numbers. I am sure copy and paste or even retyping the data sounds easier, however there are many places within each worksheet that I could use this code to automate by simply typing "yes".

 

[Peter_SSs]

Welcome to the MrExcel board!

Glad that you have had a go at writing your own code and providing it as a starting point here.
However, there are a few "issues" with your code

• You have declared changed as Range but then tried to set it equal to a string (Target.Address) which will cause an error.
• Of course it is possible to change more than one cell at a time in Excel and if you say, wanted to clear one of your columns and selected D1:D21 and pressed the Delete key you would also get errors because your code is written with the assumption that what will be changed is a single cell.
• I'm not certain what your intention with newinput is. As your code is written that would be a variable - which you have not dimensioned or given a value to. Did you mean that to be a string "newinput" perhaps?
• As mentioned already, the EnableEvents = False needs to move

I have taken a guess regarding "newinput" so that part may not be correct, but this is how I would structure the code to be more robust, and also allow that you might enter "yes" in a number of row 20 cells at the same time.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim changed As Range, cell As Range
  
  Set changed = Intersect(Target, Rows(20))
  If Not changed Is Nothing Then
    Application.EnableEvents = False
      For Each cell In changed
        If cell.Value = "yes" Then
          cell.Offset(1, 0).Value = cell.Offset(-1, 0).Value
          cell.Value = "newinput"  'Not sure about this part
        End If
      Next cell
    Application.EnableEvents = True
  End If
End Sub

One other point is that your code, and therefore mine too, is case-sensitive so if "Yes" is entered, the value would not be copied from row 19 to 21.
If you want to allow any form of "yes" to work then change the relevant line in the code to

If LCase(cell.Value) = "yes" Then

 

[My Aswer Is This]

So if the value "Yes" in any cell in Row(20) do nothing.
There are a lot of cells in Row(20) to check. You keep saying:
"to make cell 21 the same as cell 19"

There is no cell 21 there is a row(21)

 

[supermom28115]

Welcome to the MrExcel board!

Glad that you have had a go at writing your own code and providing it as a starting point here.
However, there are a few "issues" with your code

• You have declared changed as Range but then tried to set it equal to a string (Target.Address) which will cause an error.
• Of course it is possible to change more than one cell at a time in Excel and if you say, wanted to clear one of your columns and selected D1:D21 and pressed the Delete key you would also get errors because your code is written with the assumption that what will be changed is a single cell.
• I'm not certain what your intention with newinput is. As your code is written that would be a variable - which you have not dimensioned or given a value to. Did you mean that to be a string "newinput" perhaps?
• As mentioned already, the EnableEvents = False needs to move

I have taken a guess regarding "newinput" so that part may not be correct, but this is how I would structure the code to be more robust, and also allow that you might enter "yes" in a number of row 20 cells at the same time.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim changed As Range, cell As Range
 
  Set changed = Intersect(Target, Rows(20))
  If Not changed Is Nothing Then
    Application.EnableEvents = False
      For Each cell In changed
        If cell.Value = "yes" Then
          cell.Offset(1, 0).Value = cell.Offset(-1, 0).Value
          cell.Value = "newinput"  'Not sure about this part
        End If
      Next cell
    Application.EnableEvents = True
  End If
End Sub

One other point is that your code, and therefore mine too, is case-sensitive so if "Yes" is entered, the value would not be copied from row 19 to 21.
If you want to allow any form of "yes" to work then change the relevant line in the code to

If LCase(cell.Value) = "yes" Then

Thank you for the instruction. Thew new input statement was a line of code I found in one of the books I am using but it did not explain why it was being used or what purpose it served. I thought it might be some line of code to tell the system to reset and wait for newinput to run again since it was sandwiched between the enable events false and enable events true statements.

so I hope you don't mind but I basically made my code all statements and copy and pasted your coed into my window.
I did statement out the cell.value = new input and the original if yes then statement and added the if yes then statement that converted the value to lowercase before evaluating.
I still can not type into my spreadsheet though. I have no idea what I have done. When I click save in the workbook window , I get the hourglass and Saved beside the workbook name. I can change tabs and select cells. I was even able to delete the yes that was in cell E20 but I can not type into any cell on any sheet in the workbook.
I have done a file save in the editor window and nothing is working. I was able to click in the formula bar in type in there.
This is very strange behavior.
Where should this new code be placed? I have it in the sheet 5 code window, which is also where I have the code that is connected to my button on the same sheet

 

[supermom28115]

So if the value "Yes" in any cell in Row(20) do nothing.
There are a lot of cells in Row(20) to check. You keep saying:
"to make cell 21 the same as cell 19"

There is no cell 21 there is a row(21)

yes, you are right. I am thinking in terns of cell address. However I need the column reference to be the same as the target column, So if F20 is yes then F21 should be the same as F19. And it doesn't seem that elaborate in my mind because basically at this point the sheet is close to empty so row 20 barely has any data and only one column with the word yes

 

[Peter_SSs]

Where should this new code be placed?

It should be placed in the sheet's module of the worksheet that is is to act on. An easy way to get to the correct place is to right chick that sheet's name tab and choose "View Code" and the code should go in the right hand pane that opens.

It might be helpful if you posted the code as you have modified it.

This code should have nothing to do with not being able to type in the worksheet. Are you sure that you have not accidentally (or deliberately) Protected the sheet? With that sheet active what do you see on the 'Review' tab as the first option in the 'Protect' group?

This?

Or this?