Help creating a toggle switch cycle counter

I can understand why creating and coding many buttons would seem daunting.

You are lucky I'm having a quiet day and enjoy challenges.  Feedback and Likes will be appreciated.

My approach was to use a single ActiveX command button coded to be sensitive to the user's selection.  ActiveX controls are programmable while Form controls are not.

If the selection is outside the GaugeUse table or more than one row the button is disabled and an appropriate instruction is displayed.

If the selection is a single cell or row intersecting the table, the button caption changes depending on whether the gauge is In or Out.

I've tried to attach a file but if it hasn't worked, here's a screenshot:

The code behind it should be pasted into the worksheet code sheet which you can reach by right-clicking the sheet tab and selecting View code..

Option Explicit

Private Sub cbtnCheckInOut_Click()
'The button is only enabled if a single cell or row intersecting with the GaugeUse table
'Enabled or disabled is set within the Worksheet_SelectionChange event handler below

'Check if the selected Gauge is In or Out
If Cells(ActiveCell.Row, [GaugeUse[In / Out]].Column) = "In" Then
'It's In so mark it out, increment the Out count and change button caption
Cells(ActiveCell.Row, [GaugeUse[In / Out]].Column) = "Out"
Cells(ActiveCell.Row, [GaugeUse[Out count]].Column) = Cells(ActiveCell.Row, [GaugeUse[Out count]].Column) + 1
Me.cbtnCheckInOut.Caption = "Check In"
Else
'It's Out so mark it In and change button caption
Cells(ActiveCell.Row, [GaugeUse[In / Out]].Column) = "In"
Me.cbtnCheckInOut.Caption = "Check Out"
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Intersect returns a range object if Target (selection) is within the GaugeUse table.
'If the selection is outside the GaugeUse table Intersect will return Nothing.
'If more than one row is selected, the button is disabled.
'Note: [GaugeUse] is shorthand for Range("GaugeUse") or ListObjects("GaugeUse")
If Not Intersect(Target, [GaugeUse]) Is Nothing And Target.Rows.Count = 1 Then
Me.cbtnCheckInOut.Enabled = True
'Hide the Instruct range and toggle the button caption
[Instruct].Font.Color = [Instruct].Interior.Color
If Cells(Target.Row, [GaugeUse[In / Out]].Column) = "In" Then
Me.cbtnCheckInOut.Caption = "Check Out"
Else
Me.cbtnCheckInOut.Caption = "Check In"
End If
Else
Me.cbtnCheckInOut.Enabled = False
[Instruct].Font.ColorIndex = xlAutomatic
End If
'Check if more than one row in GaugeUse table is selected and change Instruct message accordingly.
If Not Intersect(Target, [GaugeUse]) Is Nothing And Target.Rows.Count > 1 Then
[Instruct] = "Select a single ID to enable the button."
Else
[Instruct] = "Select an ID to enable the button."
End If
End Sub

Hi Richard,

I've created a dummy example which is maybe similar to you looking for!

Please find it in the attachments.