Tick macro

[tonywatsonhelp]

Hi Everyone,
I have the macro below which I use to treat my own tick boxes in cells "a" = tick

It works great except you have to click away before it will change again.
I was woundering if there was a bettter way i could do this?

Basickly I want a macro that gives me an "a" in the cell i click on if its empty and "" if it has an "a"

Heres my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Selected Target
 
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Selected Target
End Sub
Private Sub Selected(ByVal Target As Range)
  
If Not Intersect(Target, Range("AL52:AL300")) Is Nothing _
        And Target.Count = 1 Then
        If Target.Value = "" Then
        Target = "a"
        Else
        Target = ""
End If
End If
  
End Sub

 

[Norie]

Re: Help with a problem with this tick macro

Does this work any better?

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

    If Not Intersect(Target, Range("AL52:AL300")) Is Nothing _
        And Target.Count = 1 Then
        Cancel = True
        If Target.Value = "" Then
            Target = "a"
        Else
            Target = ""
        End If
    End If

End Sub

 

[Snakehips]

Re: Help with a problem with this tick macro

twh, If you get fed up with double clicking annoying you then, perhaps this could annoy you instead....

[COLOR=inherit]

[/COLOR]Private Sub Selected(ByVal Target As Range)  
If Not Intersect(Target, Range("AL52:AL300")) Is Nothing _
        And Target.Count = 1 Then
        If Target.Value = "" Then
        Target = "a"
        Else
        Target = ""
End If


Application.EnableEvents = False
Target.Offset(0, 1).Select
Application.EnableEvents = True
End If
End Sub

If column AM is empty and you can hide it then you will just get a black line to the right of the cell just edited.

 

[tonywatsonhelp]

Re: Help with a problem with this tick macro

I think its ahuge improvement thank you Snakehips

 

[tonywatsonhelp]

Thanks to Snakehips great idea I realsed, I have the first ten rows hidden anyway so i just change "offset" for Range("AL5").select
its perfect! thank very much would never of thought of this I a million years