none
How to Stay in Current Cell RRS feed

  • Question

  • i have the following code that checks the value entered into Column M (13), and a message box is displayed when the value is not a permissible values.  my problem is i need to not navigate away from the active cell, however, if the user has clicked on another cell after entering their value, the cursor still moves away from the active cell after the message box is displayed.

    How do i keep the cursor in the active cell, and not navigate to the user's selected cell.

    Sub DidCellsChange()
      If ActiveCell.Column = 13 Then
        If Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "PASS" And _
          Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "INC" And _
          Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "INC-OFOI" And _
          Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "FAIL" And _
          Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "N/A" Then
          vActiveRow = ActiveCell.Row
          MsgBox "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI"
          Worksheets("Data").Range("M" & vActiveRow).Select  ' this select does not work if user has selected another cell
        End If
      End If
    Exit Sub

    Wednesday, February 10, 2016 3:46 PM

Answers

  • Hi Don,

    You need to right click the 'Data' sheet in the VBA Project window and select 'View Code'

    Then paste the following code.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sCell as Range If Target.Column = 13 Then sCell = Target.Address If Not Target.Value = "PASS" And _ Not Target.Value = "INC" And _ Not Target.Value = "INC-OFOI" And _ Not Target.Value = "FAIL" And _ Not Target.Value = "N/A" Then MsgBox "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI" Range(sCell).Activate End If End If End Sub

    Let me know how you get on.

    Cheers

    Brad


    • Edited by _Brad_C_ Thursday, February 11, 2016 2:33 PM
    • Marked as answer by BlackTruck Thursday, February 11, 2016 3:39 PM
    Thursday, February 11, 2016 2:29 PM

All replies

  • Instead of using VBA, I'd use Data Validation:

    In Excel, select column M.

    On the Data tab of the ribbon, click Data Validation.

    Select List from the Allow dropdown

    In the Source box, enter   PASS,FAIL,N/A,INC,INC-OFOI

    Activate the Error Alert tab.

    In the Error Message box, enter    Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI

    Click OK.

    When the user selects a cell in column M, a dropdown arrow will appear. The dropdown list will contain the permissible values. If the user enters a value that is not in the list, the error message will appear and the user must either cancel the edit or choose a valid entry. Otherwise it won't be possible to exit the cell.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 10, 2016 4:05 PM
  • Hans

    not sure i can use Data Validation as the rows are not fixed and are programatically inserted by other macros from time to time, so its not possible to enter the data validation logic in the rows in advance.

    thanks though

    Don

    Wednesday, February 10, 2016 4:11 PM
  • Hi,

    Try...

    Sub DidCellsChange()

    Dim cCell as Range

        cCell = ActiveCell.Address
       If ActiveCell.Column = 13 Then
         If Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "PASS" And _
           Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "INC" And _
           Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "INC-OFOI" And _
           Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "FAIL" And _
           Not UCase(Worksheets("Data").Range("M" & ActiveCell.Row).Value) = "N/A" Then
           vActiveRow = ActiveCell.Row
           MsgBox "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI"
           Range(cCell).Activate
           Worksheets("Data").Range("M" & vActiveRow).Select  ' this select does not work if user has selected another cell
         End If
       End If
     End Sub


    • Edited by _Brad_C_ Wednesday, February 10, 2016 4:28 PM
    Wednesday, February 10, 2016 4:27 PM
  • Brad

    the Range(cCell).Activate command does not stop the cursor from moving into the user's selected cell.

    thanks

    Don

    Wednesday, February 10, 2016 4:46 PM
  • Only lightly tested but have a go with this

    Private mRngReselect As Range
    Private mbExit As Boolean
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bFlag As Boolean
    Dim c As Range
        For Each c In Target.Cells
            If c.Column = 13 Then
                If VarType(c.Value) = vbString Then
                    Select Case UCase$(c.Text)
                    Case "PASS", "INC", "INC-OFOI", "FAIL", "N/A"
                        If Not UCase$(c.Text) = c.Text Then c = UCase(c.Text)
                    Case Else
                        bFlag = True
                    End Select
                Else
                    bFlag = True
                End If
                If bFlag Then Exit For
            End If
        Next
        If bFlag Then
            Set mRngReselect = Target
            mbExit = True
            MsgBox "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI"
        Else
            Set mRngReselect = Nothing
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If mbExit Then
            mbExit = False
        ElseIf Not mRngReselect Is Nothing Then
            mRngReselect.Activate
        Else
            Set mRngReselect = Nothing
        End If
    End Sub
    The code goes in the relevant Worksheet module, behind the Data sheet I assume. It's not quite right with multi cell selections, maybe you can adapt as needs. 

    Wednesday, February 10, 2016 6:45 PM
    Moderator
  • Peter

    several points

    - my need is for only one row at a time, so no looping is needed
    - i think your code is using the .Activate command, which has not worked when tried
    - i've tried Sub Worksheet_SelectionChange and it does not fire
    - i've tried Sub Worksheet_Change and it does not fire
    - Application.EnableEvents = True in my spreadsheet

    what does it take to fire the selectionchange code?

    thanks, Don

    Wednesday, February 10, 2016 7:54 PM
  • Are you sure the code in the relevant Worksheet module. You mention EnableEvents, ensure these really have been re-enabled.

    Put a break in the change event and change a cell in col-M. If the event doesn't fire look again at the above. 

    Wednesday, February 10, 2016 9:55 PM
    Moderator
  • Hi Don,

    You need to right click the 'Data' sheet in the VBA Project window and select 'View Code'

    Then paste the following code.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sCell as Range If Target.Column = 13 Then sCell = Target.Address If Not Target.Value = "PASS" And _ Not Target.Value = "INC" And _ Not Target.Value = "INC-OFOI" And _ Not Target.Value = "FAIL" And _ Not Target.Value = "N/A" Then MsgBox "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI" Range(sCell).Activate End If End If End Sub

    Let me know how you get on.

    Cheers

    Brad


    • Edited by _Brad_C_ Thursday, February 11, 2016 2:33 PM
    • Marked as answer by BlackTruck Thursday, February 11, 2016 3:39 PM
    Thursday, February 11, 2016 2:29 PM
  • Brad

    here's the code that worked, thanks for your help.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 13 Then
           On Error GoTo Skip  ' need this when user tries to erase multiple cells
           If Not UCase(Target.Value) = "PASS" And _
                Not UCase(Target.Value) = "INC" And _
                Not UCase(Target.Value) = "INC-OFOI" And _
                Not UCase(Target.Value) = "FAIL" And _
                Not Trim(Target.Value) = "" And _
                Not UCase(Target.Value) = "N/A" Then
                MsgBox Target.Value & "Permissible values only include PASS, FAIL, N/A, INC or INC-OFOI"
                Worksheets("Data").Range("M" & Target.Row).Select
            End If
        End If
    Skip:
    End Sub

    Thursday, February 11, 2016 3:42 PM