Best Option to Control/Limit/Guide Text Entry into Cell (VBA only option?)

[olddirtybaird]

Hi,


This is my first post...I love this site.


In regards to my question, I have a spreadsheet that has cells setup for user text/comment entry. For example, Cell T10 has text entered by Bob:


"Project on track. Next update will be provided 28MAR13-BH22MAR13"


The purpose of this cell is to capture commentary and updates from users regarding their tasks or projects, however, due to different users and their level detail (human factor), it has become difficult to quickly understand which comment is the most recent...for example imagine this:


"Project on track. Waiting on approved budget. Delayed by 1 month. Project is good so far"


So the problem becomes how do I start to encourage via Excel sheet setup or code that users should always place an initial and data stamp prior to their most recent comment and also make it the first item to read in the cell...ideally something like this:


"*SRB 22MAR13 - Project on track* Waiting on approved budget. Delayed by 1 month. Project is good so far"


I'm going to go ahead and assume that the best and probably the easiest method to do something like this is to create a userform/text box that activates by event when they double-click that cell or perhaps a command button that initiates the code. Based on this logic I'm thinking that with the userform text box I could receive their text updates and then automatically have code afterwards that places an initial and time stamp after complete and puts it at the first line in the cell...


Also, a key item to note is that I can not consider fonts or text formatting, hence, my example includes only text characters (*) to highlight updates because I have other workbooks linked to these cells looking at those comments. I.e. Boss/Project Mgmt office opens his global spreadsheet. And within this spreadsheet he can see these text updates from this other workbook. (FYI, I'm not a big fan of having "shared" workbooks"..tons of crashes with multiple users...)


Sorry for the long explanation, but I couldn't figure out a more concise way to truly describe my vision of the functionality and how it relates to this issue


That being said, does anyone have any suggestions or do they think this userform code is the best way to ensure all comments follow such a consistent and clean format for legibility?


I greatly appreciate your advice and help! Love this forum!


Thank you,

Stephen

 

[DaveTurton]

Use the below:

[pre]

Sub Comments()

Dim Comment As String
Dim Date_Stamp As String
Dim User_Code As String

User_Code = InputBox("Please Enter User Code", "User Code")
Comment = InputBox("Please Enter Comment", "Comment")
Date_Stamp = DateValue(Now)

ActiveCell.Value = User_Code & " - " & Date_Stamp & " " & Comment

End Sub

[/pre]

Put a button on the sheet and assign the macro to it.


It will put the comment etc in the active cell

 

[olddirtybaird]

Hi Dave, Thanks for the quick reply. I think I'll give that a shot. One question though, was there any reason for the 2 input boxes? I should be good with just one.


What I plan on doing now is concatentating their old comments that exist in the cell(s). For example, whatever they entered in last week for the project updates in cell T10. And then having the input box gather their new comment/updates to place at the beginning.


So something like this probably:


'

Comment = InputBox("Please Enter Your Update", "Update")

Date_Stamp = DateValue(Now)


ActiveCell.Value = " - " & Date_Stamp & " " & ActiveCell.Value


Thanks again!

 

[DaveTurton]

I was including two forcing them to put their user code in and ensuring it was in the right format. But whichever works best for your needs

 

[olddirtybaird]

Oh! Sorry. That is useful and makes complete sense. I misunderstood "user code", which represents the user initials now and that's great.


Thanks again!

 

[shrivallabha]

Users may not want to scroll up to click insert button. You can also try a worksheet based event.

Right click on Sheet tab and choose "View Code". Paste this code:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Comment As String
Dim Date_Stamp As String
Dim User_Code As String
Dim vbResult As VbMsgBoxResult

If Target.Column = 10 Then 'It will give prompt on Column J

vbResult = MsgBox("Do you want to insert comment?", vbYesNo, "COMMENT")

If vbResult = vbNo Then Exit Sub

User_Code = InputBox("Please Enter User Code", "User Code")
Comment = InputBox("Please Enter Comment", "Comment")
Date_Stamp = DateValue(Now)

Target.Value = User_Code & " - " & Date_Stamp & " " & Comment

End If

End Sub

[/pre]

 

[olddirtybaird]

Hi Shrivallabha,


Thanks for the reply! I was thinking the same thing and went with a double-click event. I really like the code you mentioned asking/confirming whether they want to insert a comment - Cheers!


Here's what I'm currently using, but I need to add that option you mentioned and also some on error proofing

'

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Dim Comment As String

Dim Date_Stamp As String

Dim Initials As String


If Not Intersect(Target, Range("T10:T309")) Is Nothing Then


Comment = InputBox("Please Enter Comment (no initials or dating required)", "Comment")

Initials = InputBox("Please Enter Your Initials", "Initials")

Date_Stamp = DateValue(Now)


ActiveCell.Value = "*" & Initials & "-" & Date_Stamp & " " & Comment & "* " & ActiveCell.Value

ActiveCell.Select


End If

End Sub'

 

[shrivallabha]

Target refers to the cell on which you have clicked so it is the same as Activecell. So I normally use the Target word in case worksheet_based events.


Instead of asking user to enter his initials you can use something like Application.Username and skip the initials inputbox altogether.


What sort of error handling do you want to add? You haven't specified anything in that regard.

[pre]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Comment As String
Dim Date_Stamp As String
Dim Initials As String
Dim vbResult As VbMsgBoxResult

If Not Intersect(Target, Range("T10:T309")) Is Nothing Then

Cancel = True

vbResult = MsgBox("Do you want to insert comment?", vbYesNo, "COMMENT")
If vbResult = vbNo Then Exit Sub

Comment = InputBox("Please Enter Comment (no initials or dating required)", "Comment")
Initials = InputBox("Please Enter Your Initials", "Initials")
Date_Stamp = DateValue(Now)

Target.Value = "*" & Initials & "-" & Date_Stamp & " " & Comment & "* " & Target.Value

End If

End Sub

[/pre]