Toggle suffix value of selected cell or cells

[frisbeenut]

I am in need of a macro that will add the suffix "(est.)" to the string cell value in the selected cell or cells. And if the cell string value already has the suffix, then remove it. It is my intention to assign this to a command button in a worksheet.
Thanks

 

[frisbeenut]

Sub ToggleEstSuffix()
Dim str1 As String
str1 = ActiveCell.Value
If Not IsEmpty(str1) Then
If str1 Like "* (est.)" Then
ActiveCell.Value = Left(str1, Len(str1) - 7)
Else
ActiveCell.Value = str1 & " (est.)"
End If
End If
End Sub

 

[Stevie]

Hi frisbeenut,
try this:

Sub frisbeenut()
    Dim checkStr As String
    Dim cell As Range
    Application.ScreenUpdating = False
    checkStr = "(est.)"
    For Each cell In Selection
        If Len(cell.Text) > 0 Then
            If Right(cell.Text, 6) = checkStr Then
                cell = Left(cell.Text, Len(cell.Text) - 6)
            Else:
                cell = cell.Text & checkStr
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub

If this was helpful, please click 'Like!' in the bottom right.

Stevie ^.^

 

[frisbeenut]

Thanks a lot, it is nice to have it so it works on a range.
However, I needed to change the following three lines to fully suit my needs:

checkStr = " (est.)"

If Right(cell.Text, 7) = checkStr Then

cell = Left(cell.Text, Len(cell.Text) - 7)

 

[frisbeenut]

Hi frisbeenut,
try this:

Sub frisbeenut()
    Dim checkStr As String
    Dim cell As Range
    Application.ScreenUpdating = False
    checkStr = "(est.)"
    For Each cell In Selection
        If Len(cell.Text) > 0 Then
            If Right(cell.Text, 6) = checkStr Then
                cell = Left(cell.Text, Len(cell.Text) - 6)
            Else:
                cell = cell.Text & checkStr
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub

If this was helpful, please click 'Like!' in the bottom right.

Stevie ^.^

If a cell is set to wrap text, and the width of the column is close to the limit, and adding the " (est.)" to the cell value causes the cell to wrap, when I toggle it twice (meaning back to the original cell value without the suffix), the row height gets changed. Is there a way to prevent the row height from changing? or perhaps somehow using something similar to Selection.Rows.AutoFit function?

Thanks

 

[Stevie]

Hi Frisbeenut,
try this:

Sub frisbeenut()
    Dim checkStr As String
    Dim cell As Range
    Application.ScreenUpdating = False
    checkStr = " (est.)"
    For Each cell In Selection
        If Len(cell.Text) > 0 Then
            If Right(cell.Text, 7) = checkStr Then
                cell = Left(cell.Text, Len(cell.Text) - 7)
            Else:
                cell = cell.Text & checkStr
            End If
        End If
    Next
    Selection.Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

If this was helpful, please click 'Like!' in the bottom right.

Stevie ^.^