Change the text when a cell in column D is clicked, but only if there is any text of columm A in the same row

[Hardware Man]

The title pretty much says it all. I have work sheet that gets populated with data via VBA. Column A will have some of the cells filled with text, column D will initially be blank (except for a heading in row 1).

When the user clicks on a cell in column D, I'd like the text: "Add to Order" to Appear, and have the cell be highlighted in green, but only if there is some text in Column A of the same row. If the user clicks again in the same cell, the text "Add to Order" would be deleted, as well as the green color.

If the cell could toggle on and off without having to select a different cell, that would be an added bonus.

Thanks in advance to all the experts that are willing to help!

 

[lrobbo314]

Something like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
        If Target.Value = "" Then
            If Len(Range("A" & Target.Row)) > 0 Then
                Target.Value = "Add to Order"
                Target.Interior.Color = vbGreen
            End If
        Else
            Target.Value = ""
            Target.Interior.ColorIndex = 0
        End If
    End If
End Sub

 

[lrobbo314]

If the cell could toggle on and off without having to select a different cell, that would be an added bonus.

Thanks in advance to all the experts that are willing to help!

Not sure if that is possible, but you could have the user double click to fire the code if they are staying in a cell without clicking off to a different cell first.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Worksheet_SelectionChange Target
Cancel = True
End Sub

rivate Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
        If Target.Value = "" Then
            If Len(Range("A" & Target.Row)) > 0 Then
                Target.Value = "Add to Order"
                Target.Interior.Color = vbGreen
            End If
        Else
            Target.Value = ""
            Target.Interior.ColorIndex = 0
        End If
    End If
End Sub

 

[Hardware Man]

Worked like a charm!

Thanks!!!!

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[Hardware Man]

Is it possible to get one small tweak to this code?

Right now if I were to window the cells where the highlighting occurs, I get a "type mismatch" error.

Is there a work-around for this?

 

[Rick Rothstein]

Something like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
        If Target.Value = "" Then
            If Len(Range("A" & Target.Row)) > 0 Then
                Target.Value = "Add to Order"
                Target.Interior.Color = vbGreen
            End If
        Else
            Target.Value = ""
            Target.Interior.ColorIndex = 0
        End If
    End If
End Sub

I assume you know my penchant for compact code by now? Given that, your above code can be compacted down to this...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
    Target.Value = Mid("Add to Order", Len(Target.Value) + 1)
    Target.Interior.Color = vbGreen - Target.Interior.Color
  End If
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Is it possible to get one small tweak to this code?

Right now if I were to window the cells where the highlighting occurs, I get a "type mismatch" error.

Is there a work-around for this?

Assuming you want to toggle each cell in Column D within your multi-cell selection, and using the compact version of lrobbo314's that I posted in Message #7 as a basis, see if this does what you want...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
    For Each Cell In Intersect(Range("D2:D" & Rows.Count), Target)
      Cell.Value = Mid("Add to Order", Len(Cell.Value) + 1)
      Cell.Interior.Color = vbGreen - Cell.Interior.Color
    Next
  End If
End Sub[/td]
[/tr]
[/table]

 

[Hardware Man]

<tbody>
</tbody>

Rick, that was one seriously elegant piece of code.

Thanks!