FOLLOW PATTERN FOR DATA ENTRY

samsilverman · Sep 8, 2022

I have a workbook with several worksheets. Each worksheet has up to 100 pages. Some information on these pages is already prefilled for the client.

There are then several cells in which new data needs to be put into every 3 months or so and I'm wondering if a pattern can be established through VBA coding or otherwise so that when it comes time to enter this new data, I hit enter it will simply go to the next cell needing input, instead of me having to move to the next cell with my mouse or laptop mouse pad.

If possible, I would want this pattern to be followed.

C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39

The pattern would then pick up 47 rows down for each subsequent page and follow the same exact sequence. The next page being as follows...

C63-C77, then E63-E77, then L61, L63, L64, then L70-L74, then B86

This pattern would continue by hitting the enter key. If I want to break the pattern, I want to simply use my mouse and click on a cell to enter data there. If it is a cell within the pattern, clicking enter would then go to the next cell in the pattern.

I need it to follow this pattern, jumping from one page to the next for as many pages as I might have for a client.

The starting point for every page's pattern would be C16, C63, C110, C157, C204....and so on.

I have other worksheets that I would like to employ this on as well. Those worksheets would have a slightly different pattern and starting cells. But I'm confident once I see the initial VBA coding that I can edit the code to fit that worksheet.

Appreciate any help!!

samsilverman · Sep 12, 2022

John_w said:
You have put the two pieces of code in the correct modules, if indeed the VENTILATION sheet is your data input sheet. You don't need to change any of my code.

The Compile Error: Ambiguous Name Detected: Worksheet_Change suggests there are two Private Sub Worksheet_Change(ByVal Target As Range) routines in the VENTILATION sheet's module. You can only have one such routine in the sheet module and if you are already using the Worksheet_Change event handler then my code will need to be integrated into the existing code.

Akuini's code is a different solution and completely separate from mine. You should test both solutions in separate workbooks.

OK, one last try...and thank you for your patience.
I attempted to integrate the code into the existing code, but with NO SUCCESS. My existing code is below. Any help on the integration of yours would be appreciated.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, MyRange As Range
    Application.ScreenUpdating = False
    
    If Target.Cells.count = 1 And Target.HasFormula = False Then
        On Error Resume Next
        Application.EnableEvents = False
        If InStr(Target.Value, "@") = 0 Then
            Target = UCase(Target)
        End If
    
        If Not Intersect(Target, Range("C:C, E:E")) Is Nothing Then
            '<VBA_INSPECTOR>
            '   <REMOVED>
            '       <MESSAGE>Potentially contains removed items in the object model</MESSAGE>
            '       <ITEM>[mso]ODSOFilter.Column</ITEM>
            '       <URL>http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
            '   </REMOVED>
            '</VBA_INSPECTOR>
            Select Case Target.Column
                Case 5
                    If Target > 0 Then Target = -Target
                Case 3
                    If Target < 0 Then Target = -Target
            End Select
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

John_w · Sep 13, 2022

Put my code, obviously without the Private Sub ... and End Sub, between the End If and Application.EnableEvents = True.

samsilverman · Sep 14, 2022

John_w said:
Put my code, obviously without the Private Sub ... and End Sub, between the End If and Application.EnableEvents = True.

Well, I apologize because it appears I've wasted your time. I can't get it to work. I pasted your General Module code in Module 2. It's the only thing in there. This is it:

VBA Code:

Option Explicit


Public Sub SelectNextCell(currentCell As Range, inputCells As String, inputRowOffset As Long, patternRepeatRows As Long)

    Dim inputCellsCsv As String
    Dim p1 As Long, p2 As Long
    Dim nextCell As String

    'Create csv string of individual input cells
    
    inputCellsCsv = ExpandInputCells(inputCells, inputRowOffset)
    
    'Does the current cell occur in the input cells?
    
    p1 = InStr(inputCellsCsv, "," & currentCell.Address(False, False) & ",")
    If p1 > 0 Then
        'Yes - find next cell in input cells
        p1 = p1 + Len("," & currentCell.Address(False, False) & ",")
        If p1 < Len(inputCellsCsv) Then
            'Select next cell in input cells
            p2 = InStr(p1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1, p2 - p1)
            Range(nextCell).Select
        Else
            'Reached last cell in input cells.  Select first cell in input cells offset by the number of pattern repeat rows
            p1 = InStr(inputCellsCsv, ",")
            p2 = InStr(p1 + 1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1 + 1, p2 - p1 - 1)
            Range(nextCell).Offset(patternRepeatRows).Select
        End If
    End If

End Sub


Private Function ExpandInputCells(inputCells As String, inputRowOffset As Long) As String

    Dim allCells As String
    Dim csvRanges As Variant, csvRange As Variant
    Dim cell As Range
    
    'Create comma-separated string of all individual cells in inputCells.
    'E.g. inputCells string "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39" is returned as "C16,C17,C18,C19,...,C30,E16,E17,...,E30,..." etc.
    
    allCells = ","
    csvRanges = Split(inputCells, ",")
    For Each csvRange In csvRanges
        If InStr(csvRange, ":") Then
            For Each cell In Range(csvRange)
                allCells = allCells & cell.Offset(inputRowOffset).Address(False, False) & ","
            Next
        Else
            allCells = allCells & Range(csvRange).Offset(inputRowOffset).Address(False, False) & ","
        End If
    Next
    
    ExpandInputCells = allCells
    
End Function

I also integrated your worksheet code into the VENTILATION module. It looks like this now:

Code:

'<VBA_INSPECTOR_RUN />
'The code in notes was too slow. The active coding below is much faster.
'Private Sub Worksheet_Change(ByVal Target As Range)
  'Dim hnd As New cls_shtEvents
 'Call hnd.Worksheet_ChangeEvents("VENTILATION", Target, "C:C, E:E", 5, 3)
'End Sub
Private Sub Worksheet_Change(ByVal target As Range)
    Dim cel As Range, MyRange As Range
    Application.ScreenUpdating = False
    
    If target.Cells.count = 1 And target.HasFormula = False Then
        On Error Resume Next
        Application.EnableEvents = False
        If InStr(target.Value, "@") = 0 Then
            target = UCase(target)
        End If
    
        If Not Intersect(target, Range("C:C, E:E")) Is Nothing Then
            '<VBA_INSPECTOR>
            '   <REMOVED>
            '       <MESSAGE>Potentially contains removed items in the object model</MESSAGE>
            '       <ITEM>[mso]ODSOFilter.Column</ITEM>
            '       <URL>http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
            '   </REMOVED>
            '</VBA_INSPECTOR>
            Select Case target.Column
                Case 5
                    If target > 0 Then target = -target
                Case 3
                    If target < 0 Then target = -target
            End Select
        End If
Option Explicit

    If target.Row >= 14 And target.Cells.count = 1 Then
        '1st arg - current cell
        '2nd arg - input cells
        '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
        '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
        SelectNextCell target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (target.Row \ 47) * 47, 47
    End If

        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

But nothing works. When I start in C16 and hit 'Enter' it just goes straight down the page. Thanks so much for your attempts. I really admire you guys for your talents and willingness to help.

John_w · Sep 14, 2022

The Option Explicit shouldn't be there and I think the On Error Resume Next is hiding the problem, although it is being used to suppress the error which would occur with target = -target if target is not numeric.

Try this, where I've removed the On Error Resume Next and used IsNumeric(Target.Value) instead:

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count = 1 And Target.HasFormula = False Then
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        If InStr(Target.Value, "@") = 0 Then
            Target.Value = UCase(Target.Value)
        End If
    
        If Not Intersect(Target, Range("C:C, E:E")) Is Nothing Then
            '<VBA_INSPECTOR>
            '   <REMOVED>
            '       <MESSAGE>Potentially contains removed items in the object model</MESSAGE>
            '       <ITEM>[mso]ODSOFilter.Column</ITEM>
            '       <URL>http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
            '   </REMOVED>
            '</VBA_INSPECTOR>
            If IsNumeric(Target.Value) Then
                Select Case Target.Column
                    Case 5
                        If Target.Value > 0 Then Target.Value = -Target.Value
                    Case 3
                        If Target.Value < 0 Then Target.Value = -Target.Value
                End Select
            End If
        End If
        
        If Target.Row >= 14 And Target.Cells.Count = 1 Then
            '1st arg - current cell
            '2nd arg - input cells
            '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
            '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
            SelectNextCell Target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (Target.Row \ 47) * 47, 47
        End If
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End If
    
End Sub

samsilverman · Sep 14, 2022

John_w said:

The Option Explicit shouldn't be there and I think the On Error Resume Next is hiding the problem, although it is being used to suppress the error which would occur with target = -target if target is not numeric.

Try this, where I've removed the On Error Resume Next and used IsNumeric(Target.Value) instead:

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Cells.Count = 1 And Target.HasFormula = False Then
  
        Application.ScreenUpdating = False
        Application.EnableEvents = False
      
        If InStr(Target.Value, "@") = 0 Then
            Target.Value = UCase(Target.Value)
        End If
  
        If Not Intersect(Target, Range("C:C, E:E")) Is Nothing Then
            '<VBA_INSPECTOR>
            '   <REMOVED>
            '       <MESSAGE>Potentially contains removed items in the object model</MESSAGE>
            '       <ITEM>[mso]ODSOFilter.Column</ITEM>
            '       <URL>http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
            '   </REMOVED>
            '</VBA_INSPECTOR>
            If IsNumeric(Target.Value) Then
                Select Case Target.Column
                    Case 5
                        If Target.Value > 0 Then Target.Value = -Target.Value
                    Case 3
                        If Target.Value < 0 Then Target.Value = -Target.Value
                End Select
            End If
        End If
      
        If Target.Row >= 14 And Target.Cells.Count = 1 Then
            '1st arg - current cell
            '2nd arg - input cells
            '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
            '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
            SelectNextCell Target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (Target.Row \ 47) * 47, 47
        End If
      
        Application.EnableEvents = True
        Application.ScreenUpdating = True
      
    End If
  
End Sub

I tried your new coding that incorporated my existing code, and it didn't work. I even took my original coding out of the worksheet module and just replaced it with your original coding. Still didn't work. Is there something deeper going on in another general module that may affect this? Finally, I created a new blank workbook and just placed the 2 different codes you originally provided, and it didn't work. I must be doing something wrong. I've included screenshots to show where I pasted your codes.

John_w · Sep 14, 2022

samsilverman said:
Finally, I created a new blank workbook and just placed the 2 different codes you originally provided, and it didn't work. I must be doing something wrong. I've included screenshots to show where I pasted your codes.

The codes are in the correct places. With that new workbook, put a Stop statement in the Worksheet_Change, so it looks like this:

VBA Code:

Private Sub Worksheet_Change(ByVal target As Range)
    Stop
    If target.Row >= 14 And target.Cells.Count = 1 Then
        '1st arg - current cell
        '2nd arg - input cells
        '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
        '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
        SelectNextCell target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (target.Row \ 47) * 47, 47
    End If
    
End Sub

Now enter something in cell C16 (press return to enter the value) on Sheet1. The code should stop in the VBA editor at the Stop statement and you can now press the F8 key line by line to step through the code and see what it's doing.

samsilverman · Sep 15, 2022

John_w said:
The codes are in the correct places. With that new workbook, put a Stop statement in the Worksheet_Change, so it looks like this:

VBA Code:

Private Sub Worksheet_Change(ByVal target As Range) Stop If target.Row >= 14 And target.Cells.Count = 1 Then '1st arg - current cell '2nd arg - input cells '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc. '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47 SelectNextCell target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (target.Row \ 47) * 47, 47 End If End Sub

Now enter something in cell C16 (press return to enter the value) on Sheet1. The code should stop in the VBA editor at the Stop statement and you can now press the F8 key line by line to step through the code and see what it's doing.

I believe I have discovered the problem and it was something you just said, "Now enter something in cell C16 (press return to enter the value) on Sheet1."

When I visit a facility every 3 months, etc some data in the cells may change and other data in other cells in the ranges may not. Some cells may not ultimately contain any data. So, to test it, I was simply starting at C16 and hitting the ENTER key. Your coding appears to require entry of data followed by the enter key to continue through the entire process.

As an example for what I am looking for, I may only input data in C16:C19. I would then hit the ENTER key and expect to go through to C30 sequentially until it delivered me to E16. In the E column, it is often the case to have no more than 4-5 entries. But I would then hit the ENTER key to go down through E30 until I ended up at L14.

This process would continue through each page in the worksheet by way of hitting the ENTER key. The pattern would therefore continue from any place I choose within the ranges established (for instance, I may need to start in E208) by simply going through the range hitting the ENTER key or by entering data and then hitting the ENTER key, but not requiring fresh data input and then hitting the ENTER key.

I apologize for any confusion.

John_w · Sep 15, 2022

From your OP I thought you were pressing the Enter key to enter values in the cells. The Worksheet_Change event is fired when you press the Enter key after typing in a cell value or pasting from the clipboard.

samsilverman said:
I may only input data in C16:C19. I would then hit the ENTER key

Exactly how are you inputting data in C16:C19?

samsilverman · Sep 15, 2022

John_w said:
From your OP I thought you were pressing the Enter key to enter values in the cells. The Worksheet_Change event is fired when you press the Enter key after typing in a cell value or pasting from the clipboard.

Exactly how are you inputting data in C16:C19?

So, when I need to input data into any one or more of the cells within the ranges, I would simply use the keyboard then hit enter. But if I only need to use cells C16:C19 on the first page, I would want to just keep hitting enter (or just hold it down) after my last data input into C19 until it arrived at E16 and then do whatever data input I need to do in that column using the enter key again to progress through each cell in that particular range (through E30). Then the process continues when I hit enter going to L14, etc, etc. When leaving B39, hitting enter would take it to C63 where the whole process continues. Here are a couple pics showing sample pages in the VENTILATION and CHEMICAL worksheets with different amounts of data entered throughout the established ranges.

VENTILATION PAGE 1, VENTILATION PAGE 37, CHEMICAL PAGE 3

John_w · Sep 15, 2022

You might need to use Worksheet_SelectionChange instead of or as well as Worksheet_Change, then. However I'm not sure how or if this would work.

Hopefully someone else can help you now.

FOLLOW PATTERN FOR DATA ENTRY

samsilverman

Board Regular

samsilverman

Board Regular

Excel Facts

John_w

MrExcel MVP

samsilverman

Board Regular

John_w

MrExcel MVP

samsilverman

Board Regular

John_w

MrExcel MVP

samsilverman

Board Regular

John_w

MrExcel MVP

samsilverman

Board Regular

Attachments

John_w

MrExcel MVP

Similar threads

Forum statistics

Share this page

FOLLOW PATTERN FOR DATA ENTRY

Board Regular

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Attachments

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock