Format cell text differently

Formula, for joe.blogs in cell A1:

Formula:

=PROPER(TRIM(SUBSTITUTE(A1,"."," ")))

Regards, TMS

Well, with a formula, you could have:

Formula:

=PROPER(TRIM(SUBSTITUTE(A1,"."," "))) & TEXT(NOW()," at dd/mm/yy hh:mm:ss")

But that would change every time you recalculated. So you'd have to copy it and paste special values. Probably best to use a VBA worksheet change event handler ... but do you want VBA?

Regards, TMS

Ok, tomorrow, if I have any time. Otherwise I'll get back on Monday.

Would be helpful to have a sample workbook with the button and code (and some typical data)

Regards, TMS

Please upload a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Maybe you can incorporate this into your existing code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim cell As Range

If Intersect(Target, Range("A1").EntireColumn) Is Nothing Then Exit Sub

For Each cell In Target
    With cell.Offset(0, 1)
        If .Value = "" And cell.Value <> "" Then
            .Value = awf.Proper( _
                        awf.Substitute( _
                            cell.Value, ".", " ")) & _
                     Format(Now, " at dd/mm/yyy hh:mm")
        End If
    End With
Next 'cell

End Sub

Regards, TMS

Option Private Module
Option Explicit

Sub Button1_Click()
'
' Button1_Click Macro
'
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
GetUserName
With Range("K32")
    .Value = lpUserName
    .Offset(0, 1).Value = _
        awf.Proper( _
            awf.Substitute( _
                .Value, ".", " ")) & _
        Format(Now, " at dd/mm/yyy hh:mm")
    With .Offset(0, 0).Resize(1, 2)
        .Font.Color = vbWhite
        .Interior.Color = vbWhite
    End With
End With
End Sub

Sub Button2_Click()
'
' Button2_Click Macro
'
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
GetUserName
With Range("K34")
    .Value = lpUserName
    .Offset(0, 1).Value = _
        awf.Proper( _
            awf.Substitute( _
                .Value, ".", " ")) & _
        Format(Now, " at dd/mm/yyy hh:mm")
    With .Offset(0, 0).Resize(1, 2)
        .Font.Color = vbWhite
        .Interior.Color = vbWhite
    End With
End With
End Sub

Sub Button3_Click()
'
' Button3_Click Macro
'
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
GetUserName
With Range("K36")
    .Value = lpUserName
    .Offset(0, 1).Value = _
        awf.Proper( _
            awf.Substitute( _
                .Value, ".", " ")) & _
        Format(Now, " at dd/mm/yyy hh:mm")
    With .Offset(0, 0).Resize(1, 2)
        .Font.Color = vbWhite
        .Interior.Color = vbWhite
    End With
End With
End Sub

Sub Button4_Click()
'
' Button4_Click Macro
'

' maybe a generic option?

Dim awf As WorksheetFunction: Set awf = WorksheetFunction
GetUserName
With Range("K" & ActiveCell.Row)
    ' user name
    .Value = lpUserName
    ' proper user name
    .Offset(0, 1).Value = _
        awf.Proper( _
            awf.Substitute( _
                .Value, ".", " ")) & _
        Format(Now, " at dd/mm/yyy hh:mm")
    ' hide the output
    With .Offset(0, 0).Resize(1, 2)
        .Font.Color = vbWhite
        .Interior.Color = vbWhite
    End With
End With
End Sub

Please see updated sample workbook.


Regards, TMS

With regard to your other query, it should really be a separate thread ... different question altogether.

However, you could use LEFT, MID, and RIGHT to extract the parts of the code and concatenate them into a helper column. Then you copy those values and Paste Special | Values over the original. Then delete the helper column.

Regards, TMS

I just modified your existing code to put the formatted name in the next cell to where your buttons currently output the login ID. You have three buttons for three approvers. I haven't changed that.

Button 4 was just to demonstrate a more generic approach ... but you would have to select the correct row.

how can I lock the time to each individual button?

I don't understand the question.

What do you want to happen?

Heh, heh ... that's just the original formula in column I that uses NOW(). Always a problem

The code actually puts the login ID in column K and the formatted and time stamped version in column L. However, both cells are formatted with white font and interior colour ... I had assumed that was what you wanted/needed as the sheet is formatted that way. If you want them visible, you could make the font vbBlack.

Neater code:

Option Private Module
Option Explicit

Sub Button1_Click()

Common_Button Range("K32")

End Sub

Sub Button2_Click()

Common_Button Range("K34")

End Sub

Sub Button3_Click()

Common_Button Range("K36")

End Sub

Sub Button4_Click()

' maybe a generic option?

Common_Button Range("K" & ActiveCell.Row)

End Sub

Sub Common_Button(ByRef rng As Range)
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
GetUserName
With rng
    ' user name
    .Value = lpUserName
    ' proper user name
    .Offset(0, 1).Value = _
        awf.Proper( _
            awf.Substitute( _
                .Value, ".", " ")) & _
        Format(Now, " at dd/mm/yyy hh:mm:ss")
    ' hide the output
    With .Offset(0, 0).Resize(1, 2)
        .Font.Color = vbWhite
        .Interior.Color = vbWhite
    End With
End With

End Sub