Thread: Jump from bottom of column to top of next column

Hey,

I have attached the spreadsheet this question is concerning.

Basically, is there any way to jump from joint 20 (bottom of column 1) to joint 21 (top of column 2) just by hitting 'enter' after typing information into the joint 20 input area?

Thank you for your time,

Glenver

Hi there,

The following code will do what you require, but please note that this code is tightly bound to the existing layout of your worksheet - i.e. it assumes that:

the first column of joint numbers is Column B,
the last row of joint numbers is Row 26,
there are 20 joint numbers per column,
there are two columns (e.g. Columns C and D) between each column of joint numbers.

The code will cope with extra sets of columns (in the same format) added on the right of the worksheet if you need to cater for more than 700 joint numbers.

The code must be inserted in the VBA module of the TALLY worksheet (right-click on the TALLY tab and select View Code):

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Len(Target.Value) <> 0 And Target.Row = 26 And _
              (Target.Column + 1) Mod 3 = 0 Then
            Target.Offset(-19, 3).Select
        End If
    End If

End Sub

The "jump to next column" occurs when a value is entered in the last cell of a column, but not when a value is cleared.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Ok - insert the following code into a standard VBA code module:

Option Explicit


Private Sub InsertY()

    Dim rTopCell    As Range
    Dim i           As Integer

    Set rTopCell = ActiveSheet.Shapes(Application.Caller).TopLeftCell

    For i = 1 To 20
        rTopCell.Offset(i, 0).Value = "Y"
    Next i

End Sub

Now - here comes the clever bit! Create a series of buttons in the cells C7, F7, I7 etc. Ideally each of these buttons should fit exactly over its associated cell, but the important thing is that the top left-hand corner of the button should not extend beyond the cell in which it created, because the line

ActiveSheet.Shapes(Application.Caller).TopLeftCell

identifies the cell which contains the top left-hand corner of the button.

Each of these buttons should call the "InsertY" subroutine.

When a button is clicked, the code identifies the cell containing the button in question and then inserts a "Y" in each of the 20 cells immediately underneath that cell.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Done, I apologize.

Works great, I had to delete the "private" from the "private sub" bit you had so that I could assign the macro to the buttons (unless I should put it back in and do something different). I also deleted the option explicit because I don't know what it is and it works without it (anyone want to teach me?)

Other than that though, it works great, this will be a nice tool to have for work and I have a feeling that plenty of people will find it useful too. I may conjure up some new things that I want to add and be back though....

Hi again,

Many thanks for your feedback, I'm glad that I was able to help.

Declaring a routine as "Private" means that it is available only to routines within the same VBA code module as itself. It also means that it will not be visible in the list displayed by using Tools >> Macro >> Macros. This is why you couldn't see it listed when you tried to assign it to the various control buttons.

Let me know if you need any additional features added to the workbook.

Regards,

Greg M