I wrote a small VBA script to change the status of a task by clicking on the text.
I get the code to work on 1 cell bud I cant get it to work in the entire column
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.EnableEvents = False
With Target
If .Address = Range("U3").Address Then
Select Case .Value
Case "TO DO"
.Value = "DONE"
Case "DONE"
.Value = "ON HOLD"
Case "ON HOLD"
.Value = "TO DO"
End Select
End If
End With
Application.EnableEvents = True
End Sub
any idees on how i need to do this
ive tried
If .Address = Range("U3:U1000").Address Then
and
If .Address = Range("$U3").Address Then
and many other option on google bud i cant find one that works
1
Use the INTERSECT()
function to make sure that the code works only on your desired range.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("U3:U1000")) Is Nothing Then
With Target
Select Case .Value
Case "TO DO"
.Value = "DONE"
Case "DONE"
.Value = "ON HOLD"
Case "ON HOLD"
.Value = "TO DO"
End Select
End With
End If
End Sub
2
A Worksheet Before Double Click: Switch Cell Value
- A better way would be to use the
Worksheet_BeforeDoubleClick
(or theWorksheet_BeforeRightClick
) event to meet your requirements. - Don’t forget to out-comment the
Worksheet_SelectionChange
code before testing the code. - You can write the code in a separate sub and keep it in a standard module.
Sheet Module, e.g. Sheet1
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
StatusSwitcher Target, Cancel
End Sub
- Note that you can use the same single line in the
Worksheet_BeforeRightClick
event at the same time. Then decide which one to keep, or keep both.
Standard Module, e.g. Module1
e.g. renamed to modEvents
Sub StatusSwitcher(ByVal Target As Range, ByRef Cancel As Boolean)
' It is good practice to implement an error-handling routine
' when disabling events so they get reenabled no matter what.
On Error GoTo ClearError ' start error-handling routine
' Define constants.
Const PROC_TITLE As String = "Status Switcher"
Const TOP_CELL_ADDRESS As String = "U3"
' Reference the worksheet ('ws').
Dim ws As Worksheet: Set ws = Target.Worksheet
' Reference the target range ('trg').
Dim trg As Range:
With ws.Range(TOP_CELL_ADDRESS)
Set trg = .Resize(ws.Rows.Count - .Row + 1)
End With
' Exit if 'Target', a single cell, isn't part of the target range ('trg').
If Intersect(trg, Target) Is Nothing Then Exit Sub
' Disable events to prevent triggering the 'Worksheet_Change' event.
Application.EnableEvents = False
' Modify the target cell value using your logic...
With Target
Select Case CStr(.Value) ' 'CStr' prevent error if error in cell
Case "TO DO"
.Value = "DONE"
Case "DONE"
.Value = "ON HOLD"
'Case "ON HOLD"
' .Value = "TO DO"
' Choose the initial string and remove the corresponding case above.
Case Else
.Value = "TO DO"
End Select
End With
' Cancel (suppress) the normal behavior of double-click (right-click).
Cancel = True
ProcExit:
Application.EnableEvents = True ' ... whether an error occurred or not
Exit Sub
ClearError: ' continue error-handling routine
MsgBox "Run-time error[" & Err.Number & "]:" & vbLf & vbLf _
& Err.Description, vbCritical, PROC_TITLE
Resume ProcExit ' end error-handling routine
End Sub