Excel Add Text in Cell with already existing text

[grohm]

Hi All

I want to have a button/function, which, when clicked, will add a certain text to the already existing text in that cell. this is what i have:
[VBA]Sub EngagementsMCS_Button1_Click()

ActiveCell = "" & ("On HOLD!")


End Sub[/VBA]

but it deletes the existing text in the cell unfortunately....

any help?

regards

grohm

[Bob Phillips]

[vba]

Sub EngagementsMCS_Button1_Click()

With ActiveCell

.Value = .Value & "On HOLD!"
End With

End Sub
[/vba]

[grohm]

[vba]

Sub EngagementsMCS_Button1_Click()

With ActiveCell

.Value = .Value & "On HOLD!"
End With

End Sub
[/vba]

hmmm, still doesn't work. same outcome as above :-(

[Bob Phillips]

Nonsense, if the activecell has a value, this appends to it.

[grohm]

Nonsense, if the activecell has a value, this appends to it.

ah yes, you were right. sorry for that. is there a opposite to & ?

i mean, i want to delete that same value from the cell that i added, so i could completely reverse the process from the first button. possible?

many thx!

[Ago]

you mean if the value is "blabla on hold" you want a button to make it "blabla"?

i think you would have to split the cellvalue and check each part.

[vba]
txt = ActiveCell.Value
x = Split(txt, " ")
j = 0

For i = 0 To UBound(x)
If x(i) = "On" And x(i + 1) = "HOLD" Then
while j < i
ActiveCell.Value = ActiveCell.Value & " " & x(j)
j = j + 1
Wend
End If
Next i

[/vba]

think this should work

[Ago]

wait!
i just noticed it will add a space in the begining of each cell and osme other problems :-)
this should delete the problem with the space.

[vba]
Sub test()
txt = ActiveCell.Value
x = Split(txt, " ")
j = 0

For i = 0 To UBound(x)
If x(i) = "On" And x(i + 1) = "HOLD" Then
ActiveCell.Value = ""
While j < i
If j = 0 Then
ActiveCell.Value = x(j)
Else
ActiveCell.Value = ActiveCell.Value & " " & x(j)
End If
j = j + 1

Wend
i = UBound(x)
End If
Next i
End Sub




[/vba]

[Bob Phillips]

[vba]

With ActiveCell

.Value = Replace(.Value, "On Hold", "")
End With
[/vba]