VBA Text Box, Automatic Line Break + Date

Valdrin

New Member
Joined
Dec 7, 2016
Messages
5
Hi Guys

I have a problem.

I have a sheet with some case data and use Column I to enter updates. I have a UserForm pops ups on when any cell under range (i2:i200) is clicked, the code is below. The contents of the cell is automatically copied into the text box. I then have a command button which when clicked copies the contents of the text box back into the active cell. I do have Multiline enabled on the text box.

Imagine the cell already contains the below text which would be copied on the text box.

12.05.16 - Contacted the customer and
advised appointment is booked.

In order to enter an update say on the 13.05.2016 I would have to hold CTRL and press enter to line break then manually enter the date and my update.

What I would like is for two automatic line breaks to be added from the last letter in the text box (in the above example it would be the bullet point) and automatically insert today's date in bold e.g. 13.05.16 -

The idea is that I just type in my update and click the button to insert text box value into cell. The code will hopefully make sense of it all.

Worksheet Code

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("i2:i200")) Is Nothing Then
    UserForm1.Show
    End If
End Sub

UserForm Code

Code:
Private Sub Userform_Activate()
TextBox1.MultiLine = True
TextBox1.Value = ActiveCell.Value
End Sub

Command Button
Code:
Private Sub cmdupdate_Click()
TextValue = TextBox1.Text
ActiveCell.Value = TextValue
Unload Me
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Valdrin

New Member
Joined
Dec 7, 2016
Messages
5
To make this even harder, I only want to line break if the Active Cell has a value already. If the Active Cell does not have a value then the date only is sufficient. The reason I want line breaks where there is a value in the cell is it will make the text look neat so the user can comfortably see the various updates and on what date they entered were entered.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,784
Members
430,568
Latest member
bortey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top