vba

[jono19872006]

I have found the code below on the internet and currently use it in one of my sheets, essentially when you click in one of the cells in the range it puts a true value in that cell and then if you click again a false – like a tick box except this can be set up quickly for a long range when required.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Im not great with code and was wondering if anyone could help me to do any of the following two things, first how would I modify this code so that instead of a true/false value being entered into the cell a string of text of my choice was?<o></o>
<o> </o>
And secondly and less importantly the .Offset(0, 1).Select part of the code means that when you click on the cell and the value changes the curses then finishes on the adjacent cell, if I take this out then obviously it stays on the cell I have clicked on, however if I then click on the cell again the value doesn’t change until I click into a different cell and back again, eg if I take out the offset code then I click in C1 which changes to true, I click it again and nothing happens however if I click into D1 then back to C1 it changes to FALSE<o></o>
<o></o>
<o> </o>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)<o></o>
With Target<o></o>
If (Not Application.Intersect(.Cells, Range("C1:C10")) Is Nothing) _<o></o>
And (.Cells.Count = 1) Then<o></o>
Application.EnableEvents = False<o></o>
.Value = Not (CStr(Target.Value) = "True")<o></o>
.Offset(0, 1).Select<o></o>
Application.EnableEvents = True<o></o>
End If<o></o>
End With<o></o>
End Sub<o></o>
<o> </o>
<o> </o>
Thanks Jon<o></o>

 

[Firefly2012]

Hi Jon

i think the following would be better for the True/False activity of your first code (remove your first code completely from the code module and use the below):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C1:C10")) Is Nothing Then
    Target.Value = Not CBool(Target.Value)
    Cancel = True
End If
End Sub

This way you just need to double click on the cell to change from True to False and back again.

For the string of your choice point, what would happen if the cell already contained this string? Would you want the cell cleared or what?

 

[GTO]

FWIW, maybe use the double-click event as Firefly suggested, and give it two possible outcomes.

Option Explicit
    
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With Target
        If (Not Application.Intersect(.Cells, Range("C1:C10")) Is Nothing) And (.Cells.Count = 1) Then
            Application.EnableEvents = False
            
            Select Case .Value
            Case "String 1"
                .Value = "String 2"
            Case Else
                .Value = "String 1"
            End Select
            
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[jono19872006]

thanks instead of the double click what line can i use for a single left hand mouse click?

i have found how to do it with a right click as per Worksheet_beforerightclick but not sure how to do it with a left click?

 

[Firefly2012]

There isn't one for single left-click (you will have to use Selection_Change in which case you will continue to have the problem of needing to select another cell first if you want to reapply the change to the currently active cell).

 

[jono19872006]

If i use the code suggested, how can i update this code that i can use the left mouse button ie Worksheet_selectionchange but incorporate
.Offset(0, 1).Select into the code so that it doesnt remain on the cell afte rthe change


also i have got the code for how i want the text to be edited,

With Target.Font<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
.Name = "Marlett"<o></o>
'.FontStyle = "Bold" - if want bold in<o></o>
.Size = 12<o></o>

at which point can i incorporate this in?

Option Explicit<o></o>
<o></o>
Private Sub Worksheet_beforerightclick(ByVal Target As Range, Cancel As Boolean)<o></o>
Cancel = True<o></o>
With Target<o></o>
If (Not Application.Intersect(.Cells, Range("C1:C10")) Is Nothing) And (.Cells.Count = 1) Then<o></o>
Application.EnableEvents = False<o></o>
<o></o>
Select Case .Value<o></o>
Case "b" 'string 1<o></o>
.Value = "" 'string 2<o></o>
Case Else<o></o>
.Value = "b" 'string 1<o></o>
End Select<o></o>
<o></o>
Application.EnableEvents = True<o></o>
End If<o></o>
End With<o></o>
End Sub<o></o>