Cell selection toggle effect.

[Herakles]

I'm developing a booking system where a cell value represents no booking or a booking and its value and colour is set as appropriate.

These cells are arranged in a grid with people represented in rows and times of the day represented in columns.

I'd like to be able use the selection of a cell as a toggle.

In the Worksheet_SelectionChange event I register and process the selection of a cell (book or unbook) and then select
another cell in an obsolete area of the worksheet.

This enables me to select the cell again to reverse the booking.

It works fine but there may be a better way.

I've used Application.ScreenUpdating to try and suppress screen flickering but it does not work.
It is the screen flickering that I'd like to avoid.

Does anybody have any ideas as to how I can accomplish this toggle effect in a cell?

Thanks

 

[mikerickson]

You might find that double-clicking is a better interface. If you put this the the sheet's code module, double clicking a cell in B2:G10 will toggle that cell between booked and unbooked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Range("B2:G10")) Is Nothing Then
        Cancel = True
        If Target.Value = "Booked" Then
            Target.Value = "unbooked"
        Else
            Target.Value = "Booked"
        End If
    End If
End Sub

 

[Herakles]

You might find that double-clicking is a better interface. If you put this the the sheet's code module, double clicking a cell in B2:G10 will toggle that cell between booked and unbooked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Range("B2:G10")) Is Nothing Then
        Cancel = True
        If Target.Value = "Booked" Then
            Target.Value = "unbooked"
        Else
            Target.Value = "Booked"
        End If
    End If
End Sub

Thanks Mike

I've not used Worksheet_BeforeDoubleClick before and it looks like it is the way forward.

Much appreciated considering that Davis CA Local Time is 02:03.

 

[Herakles]

You might find that double-clicking is a better interface. If you put this the the sheet's code module, double clicking a cell in B2:G10 will toggle that cell between booked and unbooked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Range("B2:G10")) Is Nothing Then
        Cancel = True
        If Target.Value = "Booked" Then
            Target.Value = "unbooked"
        Else
            Target.Value = "Booked"
        End If
    End If
End Sub

Works a treat Mike.

I'm booking volunteers in to help at vaccination centres.