0

I've written a macro that allows the user to select a group of cells and a comment. The macro then concatenates the comment into the cells.

However, for some comments, I am getting the error: "Text is not valid"

Sub copy_comment_to_range()
    Dim comment_range As Range, cell As Range
    Dim desired_comment As String
    
    On Error GoTo ESub
    Set comment_range = Application.InputBox(prompt:="Please select the range you would like to add a comment.", Title:="Select Range", Type:=8)
    
    
        
    desired_comment = Application.InputBox(prompt:="Please select the cell you would like copied into the range.", Title:="Select Comment", Type:=2)
    
    If desired_comment = "False" Then Exit Sub ' Exits sub if user did not provide comment
    
    If comment_range.Cells.Count = 1 Then ' Prevents weird error when only 1 cell is selected
    
        comment_range.Value = CStr(comment_range.Value) + " " + desired_comment
        
    Else
    
        For Each cell In comment_range.SpecialCells(xlCellTypeVisible) ' This only selects the visable cells
            cell.Value = CStr(cell.Value) + " " + desired_comment
        Next cell
        
    End If

    Exit Sub
ESub:
    MsgBox "An error has occured selecting the Range."
End Sub

This comment works: "Please add this comment."

This comment doesn't work: "Please note that this assignment is a work in progress. (System Requiremetns 1123) There needs to be more data about the scope of the request and othe various things that are very important to the company. Now I will name some of my favourite things. Cats. Oranges. Those little half straw wrappers that some resturants leave on your straw when you order a drink."

I've changed the desired_comment to from String to Variant and nothing changes. It seems to be a size limit. Strings are supposed to be able to hold 2 Billion characters so I'm confused.

Edit:

Thank you for the comments. In my use case, I am having the user select a cell, so I decided to use range for both my cells. My new code is this:

Option Explicit

Sub copy_comment_to_range()
    Dim comment_range As Range, cell As Range
    Dim desired_comment As Range
    
    On Error GoTo ESub
    Set comment_range = Application.InputBox(prompt:="Please select the range you would like to add a comment.", Title:="Select Range", Type:=8)
    
    Set desired_comment = Application.InputBox(prompt:="Please select the cell you would like copied into the range.", Title:="Select Comment", Type:=8)
    
    
    If comment_range.Cells.Count = 1 Then ' Prevents weird error when only 1 cell is selected
    
        comment_range.Value = CStr(comment_range.Value) & " " & desired_comment.Value
        
    Else
    
        For Each cell In comment_range.SpecialCells(xlCellTypeVisible) ' This only selects the visable cells
            cell.Value = CStr(cell.Value) + " " + desired_comment.Value
        Next cell
        
    End If

    Exit Sub
ESub:
    MsgBox "An error has occured selecting the your cells."
End Sub
zimide82
  • 57
  • 5
  • 3
    https://stackoverflow.com/questions/2969516/overcome-vba-inputbox-character-limit This might be your issue – Raymond Wu Nov 18 '21 at 15:02
  • 2
    Why not change the `Type` of `desired_comment` to a `Range`, then use its `.Value`. Then you won't run into a character limit. – BigBen Nov 18 '21 at 15:05
  • 1
    Are you wanting to copy the value of a selected cell or are you asking the user to type in the comment? if you are asking them to type in the comment text then the type should be 2, if you are asking them to select a cell to copy the value from then it should be 8 on your `desired_comment = Application.InputBox(prompt:="Please select the cell you would like copied into the range.", Title:="Select Comment", Type:=2)` line of code – Glenn G Nov 18 '21 at 15:06
  • 1
    Also, don't use `+` to concatenate, use `&`. – BigBen Nov 18 '21 at 15:06
  • 1
    And I would `Dim comment_range As Variant` and after inputbox check like `If VarType(comment_range) = vbBoolean And comment_range = False Then Exit Sub` so it does not throw an exception if someone presses cancel. Same with `desired_comment`. – Pᴇʜ Nov 18 '21 at 15:07

0 Answers0