FOLLOW PATTERN FOR DATA ENTRY

S

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
176
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!!
 
Sort by date Sort by votes
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.
Click to expand...
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
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Put my code, obviously without the Private Sub ... and End Sub, between the End If and Application.EnableEvents = True.
 
Upvote 0
John_w said:
Put my code, obviously without the Private Sub ... and End Sub, between the End If and Application.EnableEvents = True.
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
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.
general module.JPG



worksheet module.JPG
 
Upvote 0
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.
Click to expand...
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.
 
Upvote 0
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.
Click to expand...
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.
 
Upvote 0
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
Click to expand...
Exactly how are you inputting data in C16:C19?
 
Upvote 0
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?
Click to expand...
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
 

Attachments

  • VENTILATION PAGE 1.JPG
    VENTILATION PAGE 1.JPG
    81.5 KB · Views: 4
  • VENTILATION PAGE 37.JPG
    VENTILATION PAGE 37.JPG
    67 KB · Views: 4
  • CHEMICAL PAGE 3.JPG
    CHEMICAL PAGE 3.JPG
    73.8 KB · Views: 4
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

franklin_m
  • Question Question
VBA Search row for instances of pattern in two adjacent cells
Replies
3
Views
447
franklin_m
franklin_m
T
  • Solved
Move cursor to a specific cell after data has been entered
Replies
4
Views
411
Peter_SSs
Peter_SSs
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
891
Gajendran Yadhav
G
D
  • Question Question
VBA Control Of Page Breaks
Replies
2
Views
2K
DannyDont
D
A
  • Question Question
How do I lock a worksheet and stop data entry after a certain date and time?
Replies
1
Views
560
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,833
Messages
6,181,240
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top