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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,917
Members
414,416
Latest member
Nobu

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