worksheet change event needs to stop after 4th time

[logandiana]

I have to enter 4 strings of around 1000 or so rows in to column 1.
So for example if the 4 strings were exactly 1000 (which they'll never be), then I enter in cell A1 and the code would automatically select A1001 for the next set of data.
I don't want to scroll to the bottom after each time so I created a worksheet change event.
I made this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Data")
    .Cells(LR + 1, 1).Select
End With
End Sub

It works the way I want it to, but after the 4th instance of adding data I want it to snap back up to the top and select cell(1,1).
How do I do that?
And also how do I turn this worksheet event off afterwards to keep it from interfering with my main macro?

 

[Norie]

What exactly are you trying to put in the column?

Is it 4 strings repeated multiple times?

Also, what are you using the Change event for?

All it appears to do is select the next cell down which should happen automatically when you press return.

 

[StephenCrump]

It works the way I want it to, but after the 4th instance of adding data I want it to snap back up to the top and select cell(1,1).
How do I do that?

It can be done, but won't you also need a way to turn the feature ON, e.g. what if when you open the workbook you want to make changes without being redirected to the bottom of Column A each time?

If there were no gaps in the column A data, I'd be happy to do it manually myself. It's just as fast:

Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-UP to get back to A1

And also how do I turn this worksheet event off afterwards to keep it from interfering with my main macro?

If you do continue to use Sub Worksheet_Change, you'll need:

Application.EnableEvents = False

'any other code making changes to the worksheet but which you [U]don't[/U] want to trigger Worksheet_Change

Application.EnableEvents = True

 

[logandiana]

It can be done, but won't you also need a way to turn the feature ON, e.g. what if when you open the workbook you want to make changes without being redirected to the bottom of Column A each time?

If there were no gaps in the column A data, I'd be happy to do it manually myself. It's just as fast:

Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-DOWN, DOWN
Copy/Paste CTRL-UP to get back to A1



If you do continue to use Sub Worksheet_Change, you'll need:

Application.EnableEvents = False

'any other code making changes to the worksheet but which you [U]don't[/U] want to trigger Worksheet_Change

Application.EnableEvents = True

I pull 4 separate worksheets of data from another system that exports in excel. The data is actually in columns A-Q, but with no gaps in column A data. The current procedure is that I copy the data from the first output sheet with Ctrl-Shift Right Down, then Ctrl C. Then I paste the data in cell A1 of the macro (Sheet "Data"). I have to click somewhere in column A to deselect the data that I just pasted, then I do Ctrl-Down, Down to get to the next open cell in Column A. Next go to the second output sheet, copy... and repeat until I'm done with the 4 output sheets. Then I do a Ctrl-Up to get me to the top of the worksheet so I can press the main macro button. It combines, filters all the data etc., closes the 4 data output sheets, then does a save-as to create a dated completed report on a shared folder.
I am comfortable with the Ctrl-Down part, but I am passing the project to a group of others that I have been training on what to do and it's just comical watching them try to remember which combination of keystrokes to press, as well as the contortions they go through to actually press the keys.
So the idea came to me that if I could have the worksheet snap down to the next place to put the data automatically. I've done that with the change event on the worksheet, but I'd like a way for it to snap back up to the top of the worksheet after 4 sets of data are entered.

For turning off the events, does it only affect workbook and worksheet events? Will it have any effect on my macro contained in a module? This is the first time I've written anything as a worksheet event and I am still learning about them.

 

[StephenCrump]

I am passing the project to a group of others that I have been training on what to do and it's just comical watching them try to remember which combination of keystrokes to press, as well as the contortions they go through to actually press the keys.

Their eyes will pop out of their heads when your Worksheet_Change code keeps taking them to the end of Column A!

Why not fully automate the process in a Sub the user can trigger when they're ready:

Dim wbInput As Workbook
Dim rngInput As Range
Dim i As Long, LR As Long

Application.ScreenUpdating = False
Set wbInput = Workbooks("YourInputWorkbook.xlsx")    'Change as appropriate (or use code to open, or prompt user to select workbook for code to open?)
LR = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1

For i = 1 To 4
    With wbInput.Worksheets(i)
        Set rngInput = .Range("A1:Q" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    rngInput.Copy
    ThisWorkbook.Sheets("Data").Range("A" & LR).PasteSpecial Paste:=xlPasteValues
    LR = LR + rngInput.Rows.Count
Next i
    
Application.ScreenUpdating = True
MsgBox "Copy/paste finished!"

For turning off the events, does it only affect workbook and worksheet events? Will it have any effect on my macro contained in a module? This is the first time I've written anything as a worksheet event and I am still learning about them.

Setting Application.EnableEvents = False will affect all Application Events, of which Worksheet_Change is one. But it's probably used most often to prevent triggering of Worksheet_Change events - sometimes you'll have code that makes changes to a worksheet, but you don't want to trigger your Sub WorkSheet_Change to run.

And if Sub Worksheet_Change itself makes changes to the worksheet, it will otherwise call itself and trigger a potentially infinite loop, terminated by an "out of Stack Space" error message.

Setting Application.EnableEvents = False should be used carefully and sparingly. If your code crashes for any reason before you have reset to True, then Application events will continue to be suppressed. That's why it's recommended you wrap just the bit of code that makes changes:

'code
'code
'code
Application.EnableEvents = False
'code that makes a change to a worksheet with a Sub Worksheet_Change
Application.EnableEvents = True
'more code
'more code