Results 1 to 7 of 7

Thread: Toggle Text

  1. #1
    dslocum
    Guest

    Toggle Text

    I have recently seen a spreadsheet with several cells in a column containing the text, "yes". When you clicked on the cell the text toggled to, "no". Clicking againg returns the text to, "yes", ect. After clicking on the cell the cursor always went to cell A1. Any ideas on how to do this.
    Thank you all, denny

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Text

    There are several ways to do this. The easiest is to position a button the same size as the cell directly over the cell. The Click Event code for the button would flip the button caption between "Yes" and "No" and select cell A1.

    You could also put code in the Worksheet Selection Change event procedure to do the same thing.
    Legare Coleman

  3. #3
    dslocum
    Guest

    Re: Toggle Text

    Thank you for your help. I will try this and see if I can make it work. I am out of the office now for a number of days but will get to this ASAP.

    denny

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Text

    Legare showed you one of the possibilities. I must say that I don't know of another possibility to fire an event with just one mouse click. "Double clicking" a cell, ok, then you can play with the sheetbeforedoubleclick event (then you need to doubleclick) or using the "return" or "enter" or other keyboard keys, then you can use the sheetchange event.
    The code below may show you some of the event possibilities you have in Excel:

    <pre>Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim q As Variant
    If Sh.Name = "Sheet1" Then
    If Target = Range("A1") Then
    q = Range("A1").Value
    Application.EnableEvents = False
    If q = "YES" Then
    q = "NO"
    ElseIf q = "NO" Then
    q = "YES"
    End If
    Range("A1").Value = q
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim q As Variant
    If Sh.Name = "Sheet1" Then
    If Target = Range("A1") Then
    q = Range("A1").Value
    Application.EnableEvents = False
    If q = "YES" Then
    q = "NO"
    ElseIf q = "NO" Then
    q = "YES"
    End If
    Range("A1").Value = q
    Application.EnableEvents = True
    End If
    End If
    End Sub
    </pre>


    If you enter the code in the ThisWorkbook module, and if you enter "YES" in cell A1, it will be toggled to "NO" due to the sheetchange event. If you doubleclick cell A1, "NO" will be toggled to "YES", waiting for you to press ENTER. When pressing enter, the sheetchange event is triggered again and "YES" will be toggled to "NO".
    To prevent one event to be fired due to changes induced by the code in the other event, the events are temporarily disabled, using Application.EnableEvents = False. Be sure you reset that, otherwise the eventhandler will stay disabled until you restart Excel.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Text

    Hans: If the code disables events and then moves the selection to another cell (A1 in the original description), then you could use the selection change event and look at the target to see if the selection is being moved to a cell of interest. This would allow a single click to work. However, it would also be triggered if you tabbed to the cell.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Text

    Legare,

    I am missing something here. The change event is triggered when a cell in a worksheet is actually changed. Clicking a cell does not change it. If I understand well, you mean that you are using the selectionchange event to see if you selected a cell, different than the previous selection. OK, but this can indeed also be achieved by using the arrow keys or tab keys. Then of course, you can try to detect which key was last pressed and decide that if it was not the tab or arrow keys that it must have been a mouseclick. Maybe there is a way to find out if the mouse was clicked in a cell on a worksheet, but I am not aware of this.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Text

    If you click in a cell that is not selected, the will trigger a selection change event with the target being the cell that was clicked. The Selection Change event code could then check to see if the target cell is one of the ones with Yes/No in it. If it is, then it would toggle the Yes/No, disable events, select some cell other than the target (again A1 in the original question), then enable events again. This would mean that the Yes/No cell would never be selected. The following code works for cells A5:A7.

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("A5:A7")) Is Nothing Then
    If Target.Value = "Yes" Then
    Target.Value = "No"
    Else
    Target.Value = "Yes"
    End If
    Application.EnableEvents = False
    Range("A1").Select
    Application.EnableEvents = True
    End If
    End Sub
    </pre>


    However, the event also is triggered if those cells are selected by the Tab, Arrow, Enter, or any other way.
    Legare Coleman

Posting Permissions

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