Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Format cell for dates & e-mail addresses and have cursor return to start position

    I am attaching a form which I would like the cursor to start at Column B and with each strike of the return key it moves to the right until the last column of data then the cursor moves down one (1) row and to the left to column B, automatically.

    The second part of this worksheet would be to enter the dates in Column B and Column R by just entering the month, day and year (ie; 91715 to show as "9/17/2015").


    Thank you
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    NMN,

    You don't need anything! Just use the TAB key to move from field to field. When you Tab out of the last field just hit ENTER and it will automatically return to B(Row+1). HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    the cursor is not returning to the B column and down one row. How do I accomplish this?

    The second part of this worksheet would be to enter the dates in Column B and Column R by just entering the month, day and year (ie; 91715 to show as "9/17/2015"). Is this possible to accomplish?

    Is there a way to set up a input form sheet in excel without going through a MACRO?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    MNN,

    Try this
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    MNN how would you differentiate 11715 as 1/17/15 or 11/7/15??

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maudibe,

    Correct you are. It should be 01172015 to be converted to 1/17/2015 or 01/17/2015.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maudibe,

    The worksheet you sent works well, Thank you. Does this worksheet you sent contain a MACRO. If so, Is there any way to get the return without a MACRO?

    mnn

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    MNN,

    I forgot you were macrophobic. Maybe RG's trick might work for you at this thread:

    http://windowssecrets.com/forums/sho...ghlight=cursor
    Last edited by Maudibe; 2015-09-18 at 17:23.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have been called a lot of things but MACROPHOBIC is a first. I like Macros, but never learned VBA and do not know where to get a quick primer to get started, but I did try so I guess, its off for some sessions with the head Doc.

    Thanks for all your help and for the laugh.

    MNN

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    MNN,

    One solution to your second question regarding the date entry would be:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo errorhandler
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    If Not Intersect(Target, Range("B:B", "R:R")) Is Nothing Then
        If Len(Target) <> 6 Then GoTo errorhandler
        Mo = Left(Target, 2)
        Yr = Right(Target, 2)
        Dy = Mid(Target, 3, 2)
        Application.EnableEvents = False
        Target = DateSerial(Yr, Mo, Dy)
        Application.EnableEvents = True
    End If
    Exit Sub
    errorhandler:
    MsgBox "The format of the value entered must be mmddyy.  Please try again."
    Target.Select
    Target = ""
    Application.EnableEvents = True
    End Sub
    Placed in the Worksheet module, the code looks for a value inputted in columns B or R in the format of mmddyy. If not in that format, a message will inform the user. The values are converted to mm/dd/yy.

    Example:
    110315 = 11/3/2015
    030415 = 3/4/2015
    03415 = "The format of the value entered must be mmddyy. Please try again."
    30415 = "The format of the value entered must be mmddyy. Please try again."

    Note: These columns must be formatted as Text

    HTH,
    Maud
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •