0

I posted a question recently on how to format the first part of text from a input box and append it to existing text in a cell.

So for example if I have a cell with... this

23/08/2013: Hi there how are you today

24/08/2013: Customer is feeling good today

and I double click the cell, I get an input box to enter a comment. I take the comment.. add todays date to it in the VBA code and then append and format it to the existing code using this

If Target.Column = NOTES_COL Then 'Add a note

    lngPos = Len(Target.Text)

    strNote = InputBox(Prompt:="Enter Note", _
      Title:="Notes", Default:="")


     If (Len(Trim(strNote)) > 0) Then
         If Target.Value = "" Then
           Target.Font.Bold = False
           newVal = Date & ": " & strNote
         Else
           newVal = Chr(10) & Date & ": " & strNote
         End If
         Target.Characters(Start:=lngPos + 1).Text = newVal
         Target.Characters(Start:=lngPos + 1, Length:=11).Font.Bold = True
     End If
End If

So basically this takes a comment... adds a date to the comment, and a new line and then appends it to the existing characters and formats the date Bold.

This all works fine until I go over 255 Characters, which the poster that helped me in the last solution warned about, but I thought it was trying to insert one comment > 255 and not the entire cell lenght.

How do I get around this... as I can add mulitple comments into a cell

regards Mick

Doiremik
  • 265
  • 1
  • 6
  • 21
  • 1
    Why not use a userform with a multiline textbox? – Siddharth Rout Sep 19 '13 at 12:45
  • hi Sid... can you see the comment below.. its not the input box as the comments are only 100 chars but its when I append them to the current text using Target.Characters(Start:=lngPos + 1).Text = newVal it truncates at 255 – Doiremik Sep 19 '13 at 13:16

1 Answers1

0

A similar issue was discussed on SO here. According to that thread, the issue is that the InputBox method only allows a max of 255 characters. I've verified this myself.

That thread also suggested using a userform. That is the best option, especially considering entering over 255 characters in a one-line inputbox is probably setting up for frustration. How will you review what you've already typed, for example?

There are a few less polished options I can think of:

Have users enter comments directly in another cell - for example, select (and I hate using select!) a different cell, and let the user put whatever notes they desire there. Then run code to append that cell's value to your target cell.

Something even more crazy would be to call an outside program, such as notepad (or the Mac counterpart), save, and import that information in to append to the target cell.

All of these options are just work-arounds for the InputBox limit. Maybe there are some better alternatives someone more experienced can think of?

Update
Also, there seems to be an issue with using .Characters to add to anything over 255 characters long. I suspect this is because the .Characters method (not property) is set up to work on a textframe, not a cell value. A textframe is actually an object that works with shapes. It's interesting you're using it here on a range to edit its value - cool trick, courtesy of another question you've asked.

A solution would be to replace the cell value. You can then use .Characters as an object, to format portions of the cell value, as I think you've already tried to do in your other question.

Community
  • 1
  • 1
Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • what about entering a longer than 255 string in a hidden cell? –  Sep 19 '13 at 12:49
  • thanks Aaron, however the issue isnt the lenght of the text box input as most of the comments are around 100 chars. The problem is, when I try and concatanate these to the current text in the cell it just gets truncated in the cell. I am using the Target.Characters(Start:=lngPos + 1).Text = newVal to add the input string to the current text in the cell – Doiremik Sep 19 '13 at 13:13
  • So for example... using the code above and this 100 char string... and a double click event... add the string to the text box and in it goes with the date being formated bold. Do it a second time and all is good. The third time... nothing goes in?? string := This is a line with 100 characters in it ... sdfjkkjlsdf lksdfj sldfjj lsdfj lsdfj lskd jfslfj END – Doiremik Sep 19 '13 at 13:22
  • Also I can edit the cell and add characters so this tells me its not a restriction on the cell but on the code.. using .Characters Text – Doiremik Sep 19 '13 at 13:25
  • hi Aaron, yes the answer you suggest is one that I am putting of in that I add the new comment and date to the existing cell text and set that as the cell value. Then find the (CLRF) in the text and format the next 11 chars as bold... but I was holding out for something slicker... Its mad that I can edit the text and make parts of it bold but the .Characters doesnt handdle it...ahhh – Doiremik Sep 19 '13 at 15:23
  • Yes, excel's not really set up to do this easily. I had another very crazy thought: you could have input stored as a word document, within excel. Word offers more options for formatting text. See [here](http://stackoverflow.com/questions/6390109/parse-a-word-document-into-an-excel-file) for more on this. – Aaron Thomas Sep 19 '13 at 15:37