Optimizing and correcting code

[Phil Hageman]

Would appreciate very much if anyone could help with this code. It does not work at all. Also, it needs to work in newly created monthly worksheets - should it be in a module?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange1 As Range
Dim myRange2 As Range
'MOVE ACTIVE CELL RIGHT ONE CELL IN COLUMNS G, H, I, AND M, N, O.
        'Set the range of cells for activation to occur.
Set myRange1 = Range("G3:I52", "M3:O52")
        'Test whether the cell changed (Target) is within the the range
    If Not Intersect(Target, myRange1) Is Nothing Then
            'It is, so switch events off to prevent a double trigger
        Application.EnableEvents = False
            'Select one cell right (row first number, column second number)
        Target.Offset(0, 1).Select
            'Switch events back on.
        Application.EnableEvents = True
    End If
'MOVE ACTIVE CELL ONE ROW DOWN, THREE COLUMNS LEFT.
        'Set the range of cells for activation to occur. Columns J amd P only need to go to row 51.
Set myRange2 = Range("J3:J51", "P3:P51")
            'Test whether the cell changed (Target) is within the the range.
    If Not Intersect(Target, myRange2) Is Nothing Then
            'It is, so switch events off events prevent a double trigger
        Application.EnableEvents = False
            'Select the cell 1 row down and 3 columns left (row is first number, column is second number.
            'Down and right are positive, up and left are negative.
        Target.Offset(1, -3).Select
            'Switch events back on.
        Application.EnableEvents = True
    End If
End Sub

 

[hotabae]

It does not work at all.

This could mean lots of things. It's generally a good idea to mention what isn't working in the post.

See this post for tips on posing questions: https://www.mrexcel.com/forum/board-announcements/685723-help-us-help-you.html

I can see you commented "Move active cell..."

Do you mean to actually move data, or just select a different cell?

should it be in a module?​

It should be in the worksheet code for each sheet. I usually store a "template" which has the worksheet code on it and the copy the sheet rather than creating a new one each time (then the code transfers to each copy).

BUT if you don't want to copy/paste.... there are ways to add vba code to worksheets. There is some info on that method here: http://www.cpearson.com/Excel/vbe.aspx

 

[Phil Hageman]

Thanks hotabae for your response - appreciated. All I'm trying to do is move to a new cell. What happens: in a row, user posts in a cell, say G10. Then I need H10 to become active, etc. until posting in J10, where G11 is then activated. This continues down the rows to I 52. J51 is as far as the down 1 left 3 needs to go.

No error codes. Nothing happens when I post to the cells.

 

[hotabae]

Alright,

On my end, the code is functioning as described/commented as far as I can tell.

Couple of things I'd like you to check:

1. What is the file extension (.xlsx, .xlsm, .xls - etc...)

- .xlsm is the "Macro-Enabled" filetype

​

2. Right Click the Tab for this worksheet and select "View Code".

- Your code should appear here; if it does not, then it is in the wrong place

3. Navigate to the Immediate code window (Ctrl + G) and enter/run this code:

​

MsgBox(Application.EnableEvents)

- We are hoping for a result of True.

- If False, run this line (still in immediate) and then try to trigger the macro again:

​

Application.EnableEvents = True

4. If we haven't discovered the issue at this point, place a MsgBox() prompt at the beginning of your code and then try to trigger it (by entering info into G10 and tabbing out)

Like this:

[LEFT][COLOR=#333333][FONT=monospace]Private Sub Worksheet_Change(ByVal Target As Range)

[/FONT][/COLOR][/LEFT]
[COLOR=#0000ff][LEFT]MsgBox("Hello World")[/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=monospace]

Dim myRange1 As Range
Dim myRange2 As Range
[/FONT][/COLOR][/LEFT]

Follow these steps and let me know what happens. Also... It doesn't hurt to Restart your computer; it can sometime fix issues with Excel.