Macro?

M

Mjohnson

I am not sure what function I need to use, but what I am looking for is
this.... I have a spreadsheet that lists all of my tasks. As soon as a task
is complete, I want to be able to click in the next column, same row, and
have an X populated in that field. I don't want to have to type the X. As
soon as I click the cell with the mouse I want the X to appear, and if I
click the same cell again, I want the X to be removed. Is there an easy way
to set this up? I was looking to see if there was any type of toggle button
feature that would do this and was unsuccessful.

Thanks!
 
B

Bob Phillips

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, -1).Value <> "" Then
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Mjohnson

I followed the steps given below. I pasted the code into the worksheet event
code and saved, and nothing happens when I click in column B. I would expect
that when I click in Cell B1 or B2, etc. that an X would appear, but nothing
happens. Do you think it is possible that I left something out?

Thanks so much!
 
B

Bob Phillips

Only if the task is complete, which is data in the corresponding A row.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Duke Carey

Bob's solution requires 1) there be an entry in column of the same row as the
cell you've selected in column B, and 2) that the cell selection change.
Thus, if cell A1 contains a text and is selected and you click in B1, you
should see the selection change and an X appear. To remove it, yo have to
click out of B1 (probably best to click out of column B) and click back into
B1.

An alternative would be to change the first line of Bob's code to

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

This requires that you DOUBLE-CLICK the cell where you want the X to appear.
 
M

Mjohnson

Thanks so much! That worked great. One more thing. If I wanted to modify
the code and lets say I have multiple columns of tasks and in the following
column that is where I wanted the X. Example Column A, C, E, and G will all
have tasks and columns B, D, and F need to have the X put in there. I tried
to modify the code and add in the ranges, but I get a compile when I try to
do this.
 
B

Bob Phillips

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B,D:D,E:F,H:H"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, -1).Value <> "" Then
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
End If
.Offset(0, -1).Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top