richard11153
New Member
- Joined
- Feb 1, 2017
- Messages
- 5
I have a workbook with VBA that was made years ago with Excel 2003. (see VBA below)
The sheets have many cells throughout that I can "Check off" with a click, to put an "X" in the cell.
(Each column has a hidden column next to it and when there is a "q" in it, the cell to the right will
put an "X" when clicked on)
I am now making an updated workbook with Excel 2016 and for some reason this VBA code
will not work. Can anyone shed some light on this, or is there a better VBA code type?
Thanks
----------------------------------------------
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]{Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "a1:bu1450" '<=== change to suit
If ActiveCell.Offset(0, -1).Value <> "q" Then
Exit Sub
End If
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "x"
Select Case .Value
Case "x": .Value = ""
Case Else: .Value = "x"
End Select
.Offset(0, -1).Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub}
[/FONT]
The sheets have many cells throughout that I can "Check off" with a click, to put an "X" in the cell.
(Each column has a hidden column next to it and when there is a "q" in it, the cell to the right will
put an "X" when clicked on)
I am now making an updated workbook with Excel 2016 and for some reason this VBA code
will not work. Can anyone shed some light on this, or is there a better VBA code type?
Thanks
----------------------------------------------
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]{Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "a1:bu1450" '<=== change to suit
If ActiveCell.Offset(0, -1).Value <> "q" Then
Exit Sub
End If
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "x"
Select Case .Value
Case "x": .Value = ""
Case Else: .Value = "x"
End Select
.Offset(0, -1).Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub}
[/FONT]