+ Reply to Thread
Results 1 to 7 of 7

No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

  1. #1
    Registered User
    Join Date
    02-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    I'm using the below macro to allow my data validation fields to handle multiple choices from the drop-down, which works. However, when I go to a cell where I can just key in data such as Phone Number, and I press enter, it does not move down like it normally would when you press enter. It just jumps back to the cell you just worked on.

    If you were to copy this code to a blank workbook. Unlock a few cells like A1 through A10. Then create a short range for your data validation to the side of column A. Create a data validation list dropdown in cell A1. Then try to key something random in the blank cell of A2 and press enter, it just bounces back to A2 instead of going down to cell A3...


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim rn As Range

    If Target.Count > 1 Then Exit Sub
    Sheets("Form").Unprotect Password:="123"
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" Then
    If xValue2 <> "" Then
    Target.Value = xValue1 & ", " & xValue2
    End If
    End If
    End If

    Set rn = Target
    rn.EntireRow.AutoFit
    rn.Select

    Sheets("Form").Protect Password:="123"
    Application.EnableEvents = True
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    Please Login or Register  to view this content.
    This code sets rn to the Target (the changed cell), then it re-selects rn . This happens for all changed cells regardless if it's a DV cell. It should be within the If Not Application.Intersect...End If code block.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    If I move the code to that section, the autofit doesn't work automatically. I have to go into the cell and press enter. Something else that needs to be fixed?

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    Thank you very much!!!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    You're welcome. Thinks for the feedback.

  7. #7
    Registered User
    Join Date
    02-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Re: No Cell Movement after Pressing Enter with this macro I'm using on a protected sheet

    Thank you very much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Insert new row by pressing enter (sheet code)
    By Taktiker in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-11-2013, 02:53 AM
  2. Replies: 1
    Last Post: 12-12-2012, 08:46 PM
  3. Replies: 0
    Last Post: 08-22-2012, 10:12 AM
  4. Trigger macro when entering text in a cell, without pressing enter
    By DGagnon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2012, 05:53 PM
  5. Run Macro by Pressing Enter Key
    By styre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2009, 03:49 PM
  6. execute macro after pressing enter
    By ledzepe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2008, 05:44 PM
  7. Limiting the Movement in a Protected Sheet in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 10-31-2007, 09:07 AM
  8. Customize cursor movement when pressing enter
    By User of the Word in forum Excel General
    Replies: 3
    Last Post: 05-07-2006, 10:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1