3

How do we create a VBA code such that we can toggle between 4 values for any given cell within one column, and that it would not click into any "Edit" mode after clicking the cell?

The 4 values are : Y, N, L and G.

The only code I could gather (shown below) is problematic as it enters into an edit mode when clicking, and it doesnt allow me to perform toggle cell function for an entire column using the same VBA code:enter image description here

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Row <> 1 Then Exit Sub

Select Case UCase(Target.Value)

    Case Is = "Y"

        Target.Value = "N"

    Case Is = "N"

        Target.Value = "L"

    Case Is = "L"
        Target.Value = "G"

    Case Is = "G"
        Target.Value = "Y"

End Select

End Sub

----- Thanks for the replies! Please see attached image. The idea is to be able to toggle the status of any items featured. The Status column is at Column L, and i need it to to toggled between Y-N-L-G, instead of using any dropdown list.

enter image description here

2
  • Try updating your cases like this Case "Y": Target.CurrentRegion.Columns(Target.Column).Replace "Y", "N"
    – paul bica
    Feb 28, 2018 at 3:34
  • Sorry I'm not very well versed with VBA coding. Able to show the complete display with the new formula positioned in?
    – J.C
    Mar 1, 2018 at 3:41

2 Answers 2

1

Try this format


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Const MIN_ROW = 4   'Column Header (Status) is on row 4
    Const COL_L = 12

    If Target.CountLarge = 1 Then           'If a single cell is selected
        If Target.Column = COL_L Then       'If selected cell is in col L
            If Target.Row > MIN_ROW Then    'If selected cell row is 5 or higher
                With Me
                    Dim lr As Long, arr As Variant, i As Long
                    lr = .Cells(Rows.Count, COL_L).End(xlUp).Row  'Last used row in col L
                    If lr > MIN_ROW Then
                        arr = .Range(.Cells(MIN_ROW + 1, COL_L), .Cells(lr, COL_L))
                        For i = 1 To UBound(arr)
                            Select Case UCase(arr(i, 1))
                                Case Is = "Y":  arr(i, 1) = "N"
                                Case Is = "N":  arr(i, 1) = "L"
                                Case Is = "L":  arr(i, 1) = "G"
                                Case Is = "G":  arr(i, 1) = "Y"
                            End Select
                        Next
                        .Range(.Cells(MIN_ROW + 1, COL_L), .Cells(lr, COL_L)) = arr
                    End If
                End With
            End If
        End If
    End If
End Sub
9
  • ----- Thanks for the replies! Please see attached image from the question section. The idea is to be able to toggle the status of any items featured. The Status column is at Column L, and i need it to to toggled between Y-N-L-G, instead of using any dropdown list.
    – J.C
    Mar 6, 2018 at 5:07
  • Your requirements are not very clear. Based on the image you uploaded and your initial code, you cannot simply toggle between 4 different values - you may want to cycle all of them at the same time, otherwise you'll end up with the issue visible in Scenario 2. Also, this event should be triggered based on what? when you click on the top cell of column L (L6), and you want to change all values based on the same top cell? Without the image I was under the impression that you want to click in ROW 1 of any column. Please provide more details and I'm sure we can find the best approach
    – paul bica
    Mar 7, 2018 at 20:13
  • 1
    Hi Paul, there should not be the presence of any other value such as from scenario 1. The event should only be triggered by a single click on the specific cell (Not column top) for it to change from "Y" to "N". If the existing value is "N", it should then change to "L", and if "L" is the existing value, it should change to "G" and the cycle repeats from "G" back to "Y" again in a Y>N>L>G manner.
    – J.C
    Mar 22, 2018 at 2:51
  • 1
    Please see attached latest picture. It should be clearer with more details now, hopefully :D
    – J.C
    Mar 22, 2018 at 3:12
  • I made the update. If it works please mark an answer as accepted - click on the check mark beside the answer to toggle it from greyed out to green - more details Here
    – paul bica
    Mar 22, 2018 at 3:58
-1

You can try this VBA code, it was tested by me before I've posted it here and it's working.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If ActiveCell = Range("A2") Then

    If ActiveCell.Value = "Y" Then
      ActiveCell.Value = "N"

        ElseIf ActiveCell.Value = "N" Then
          ActiveCell.Value = "L"

          ElseIf ActiveCell.Value = "L" Then
           ActiveCell.Value = "G"

      ElseIf ActiveCell.Value = "G" Then
          ActiveCell.Value = "Y"

       End If
End If

End Sub

NB: Write Y (in Capital) and finish with Enter. Then after whenever you place the Cell pointer or Click on Cell A2 value will change from Y to N to L to G to Y.

I do hope this help you.

4
  • Just write, what is wrong with this VBA code since meeting the OP by all means. Feb 28, 2018 at 7:25
  • Thank you for your editing. It works for a cell, but what if we need the toggle function for all the cells of say, Column L?
    – J.C
    Mar 1, 2018 at 3:32
  • Since the OP was for the Cell only so that I've written VBA code accordingly,, You show me how you want to toggle for the Column sequence of characters then I'll suggest you the code. Mar 1, 2018 at 6:22
  • ----- Thanks for the replies! Please see attached image from the question section. The idea is to be able to toggle the status of any items featured. The Status column is at Column L, and i need it to to toggled between Y-N-L-G, instead of using any dropdown list.
    – J.C
    Mar 6, 2018 at 5:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .