Help with Excel macro 1

[Scott24x7]

Hi,
I'm looking for a macro that will allow me to change the value of a cell by clicking on it. For instance, I have cells C3:H3 that are Yes/No, but I want to put an X in it to show "check" or blank if not. Probably need this for a few rows too. Is this possible?
Thanks

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Gruuuu]

Put this in the worksheet scope:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim targetcell As Range
For Each targetcell In target
If Not Application.Intersect(targetcell, Range("C3:H3")) Is Nothing Then
    If IsEmpty(targetcell.Value) Then
        targetcell.Value = "X"
    Else
        targetcell.Value = Empty
    End If
End If
Next targetcell
End Sub

caveat: This only works if the selection changes. So if you click in one of those cells twice, it will not toggle. Also you can drag a selection out and this will capture all of the appropriate cells in your selection.

If you have any questions, please ask! And let us know how it works for you!

 

[Scott24x7]

Gruuuu
Thanks, this looks good, but when I put the code in the Worksheet scope, with SelectionChange as the method, when I go to the sheet, and click one of those cells, nothing happens. Any idea?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    If Not Application.Intersect(Target, Range("C3:H3")) Is Nothing Then
        If Target.Value = "X" Then
            Target.ClearContents
        Else
            Target.Value = "X"
        End If
        Target.Offset(-1).Select
    End If

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
This is interesting, and kind of works, but I've now applied it to a bigger range (with more rows) so my range is J8300. When I click in one of the cells in a row higher than 8, it changes all the blocks above it to the value of the cell I click on. Any way to fix this?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Oh, and it moves the focus of the active cell to row 7 of whatever column it is on. Which has the effect of jumping you back to the top of the page, even when you click lower down.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

The reason for moving the selection is that if the user wants to RE-SELECT the same cell, they have to click off and click on.

I arbitrarily chose the previous row, but you could choose to select some other cell, like A1 or choose to select nothing at all.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        If Not Application.Intersect(Target, Range("C3:H3")) Is Nothing Then
        If Target.Value = "X" Then
            Target.ClearContents
        Else
            Target.Value = "X"
        End If[b]
        Target.Offset(-1).Select[/b]
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Yeah I understand that. But with that item there, it is changing large volumes of cells. I don't know if Excel "travels" through all of them to get back, but it changes the value of the cells above it in a column to the opposite of what they were. I like the idea a lot. Better for users, but I have to find something that will work without replacing so many values.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[PHV]

I'd use the Application.EnableEvents property

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

" But with that item there, it is changing large volumes of cells."

Patient: "Doc, it hurts my head when I hit it with a hammer. What should I do?"

Doctor: "Stop hitting yourself in the head with a hammer!"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Sorry, it was late for me.
What I mean is, the line: Target.Offset(-1).Select
is causing the active sell to be selected as Starting range for the column (like J8), it lands in J7 after I click any cell in the J column in the range I have specified. (I'm using J8300 now). So any click in a column results in all cells above where I click to be set to the opposite of what they were. So it's resetting everything from above that point. If I take that line out, it doesn't reposition, but then it doesn't solve the "click same cell twice" issue that you were trying to solve with that line of code.
Is there a way to fix this? (In this case, using Excel 2010 with Macro-Enabled file type).
-S

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

As I previously posted, along with BOLDING the related statement...

I arbitrarily chose the previous row, but you could choose to select some other cell, like A1 or choose to select nothing at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
I have it set now to choose nothing, but that doesn't allow for "reclick" in the same cell. Is there a way to do that? Using A1 has a different effect, and constantly moves the focus up to that cell, which is also problematic, in that if I'm at row 200, and column O, it puts the focus all the way back up to A1.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Here are your choices.

1. Select nothing else. User must click off OUTSIDE the hot area.

2. Select a cell OUTSIDE the hot area.

3. Use Application.EnableEvents to toggle events OFF, select a near cell INSIDE the hot area, Use Application.EnableEvents to toggle events ON. HOWEVER, if the usere wants to toggle the selected cell, User must click off OUTSIDE the hot area.

Pick yer poison.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Got it. Thanks. Option one seems to be acceptable to the users, so I'll go with that. And the star is from me.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

This will enable a click DIRECTLY on the same cell.

First FREEZE row 1

HIDE row 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    If Not Application.Intersect(Target, Range("J8:P300")) Is Nothing Then
        If Target.Value = "X" Then
            Target.ClearContents
        Else
            Target.Value = "X"
        End If
        Union(Cells(1, Target.Column), Target).Select
    End If

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}