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

 

[My Aswer Is This]

So if range("F20").value="Yes" then
Range("F21").value=Range("F19").value

That is what you last posting to me said.
So that is the only time the script should run?
Here is your Quote:
"So if F20 is yes then F21 should be the same as F19"

 

[My Aswer Is This]

Try running a script like this to see if you have turned off sheet change events.

Application.EnableEvents =True

 

[supermom28115]

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? View attachment 64263


Or this? View attachment 64264

That was the first thing I tried but it was not protected. I don't know, but excel just shut down and I had to restart my workbook. So maybe one of the code windows I had open was interfering.

However, I did what you said and right clicked on the sheet tab to view code. The sheet 5 code window opened. (Just wanted to make sure it was not supposed to be in a module window).

Private Sub SameLocation_Click()

'Dim CopyRange As Range
'Dim PasteRange As Range

'Set CopyRange = Application.InputBox(prompt:="What is the cell range to copy?", Title:="Copy Cells", Type:=8)
'Set PasteRange = Application.InputBox(prompt:="What is the cell range to paste to?", Title:="Paste Cells", Type:=8)
         'With CopyRange.Copy
        'End With
        'With PasteRange.PasteSpecial
      'End With
      
    Dim RecLoc, BirLoc As Range
 
 
 Set RecLoc = Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-1, 0))
 Set BirLoc = Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(4, 0))
 BirLoc.Value = RecLoc.Value
    
End Sub


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
       If LCase(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

This is the code now for this sheet. Since excel restarted I can enter data and when I enter yes into row 20 it makes the two cells the same. If I enter no it does nothing. Now that I have good code as example and I understand about 95% of the code right now, I should be able to use this as a blueprint for similar areas in my workbook.
Thank you all so much for your help, I was having such a hard time finding the answer on my own.

 

[Peter_SSs]

Glad that you have it working now & thanks for posting the code.

That code does highlight one other point that you may not be aware of

Dim RecLoc, BirLoc As Range

This line of code in your button code does not declare both those variables as Range variable as you may have thought. ReLoc will be a Variant. That will still work but since you are using it for a range it would be best to be declared as such. For that, you need to specify for both like this (& like I did for my two range variables in the worksheet change code)

Dim RecLoc as Range, BirLoc As Range

 

[supermom28115]

Glad that you have it working now & thanks for posting the code.

That code does highlight one other point that you may not be aware of

Dim RecLoc, BirLoc As Range

This line of code in your button code does not declare both those variables as Range variable as you may have thought. ReLoc will be a Variant. That will still work but since you are using it for a range it would be best to be declared as such. For that, you need to specify for both like this (& like I did for my two range variables in the worksheet change code)

Dim RecLoc as Range, BirLoc As Range

Thank you. I found a place that said you could write Dim statements that way so it is good to know I need to do it differently. I was doing each Dim statement separately and was trying to make my code cleaner.

 

[My Aswer Is This]

Glad you have things working

 

[supermom28115]

Glad you have things working

Thank you for your help.

 

[My Aswer Is This]

I like double click event codes.
Easier then entering "yes"
This script runs when you double click on any cell in row(20)
If you have "Alpha" in any cell in row 19
And then double click on cell below "Alpha" The term "Alpha" will be entered in that cell.

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Row = 20 Then
Target.Value = Target.Offset(-1).Value
End If
End Sub

You do not always have to enter "Alpha" you can enter any value

 

[My Aswer Is This]

This code will work on any cell if you double click on it.
Enters the same value you double click on in the cell below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified  5/10/2022  3:43:31 AM  EDT
Target.Value = Target.Offset(-1).Value
End Sub

 

[supermom28115]

Glad that you have it working now & thanks for posting the code.

That code does highlight one other point that you may not be aware of

Dim RecLoc, BirLoc As Range

This line of code in your button code does not declare both those variables as Range variable as you may have thought. ReLoc will be a Variant. That will still work but since you are using it for a range it would be best to be declared as such. For that, you need to specify for both like this (& like I did for my two range variables in the worksheet change code)

Dim RecLoc as Range, BirLoc As Range

Peter_SSs if I could take a moment to (1) thank you for all your help last night, or this morning since it was around 1 or 2 AM when we were messaging, it was truly appreciated and you guys responded very quickly to my post and (2) take some more of your vast knowledge of VBA coding by asking you how to handle creating multiple worksheet changes that would be almost exactly like this in that I have a target cell the user will enter yes or no, no being sub do nothing (user will enter alternate data) and if yes then a cell or small range of cells will need to be same value. Obviously the same structure with labels in column C and each record in a new column. Each change event would be based on the same concept of target being a specific row and the cell or cells above and below have same value for yes.

I hope that isn't too confusing. I am working on adding some records back into my worksheet so that I have more data to work with and I also downloaded that program (i think L2BB) so that I could show you what I am working with so you might understand what kind of data I am working with.
Would you create separate sub events and then call them with a worksheet change event sub or do each of them as a separate worksheet change event (and if so would they need to be in separate code windows since they would all be named worksheet change? I am not able to use the same variable named cell if I try to put them in the same change event code? I need the variable cell as cell for the code to work, correct?) Is there a better option that I haven't seen due to how new I am to VBA?
On a separate but similar topic do you have a suggestion for resources to learn VBA that would be structured for a person that learns better by doing? Preferably free, cheap or not extravagant this is a personal self improvement venture not for job skill building.