Comments Question

[CaseyBhoy]

Hi All

Firstly, I'm new to all of this, so please bear with me!

I'm currently working with an excel document that is filled in by an end user, and exported into an access db.

Each sheet of the document contains a series of questions, and attached to the answer box of each question is a comment.

The purpose of the comment is to define the question number (1,2,3,4,5 etc.)

The problem lies when inserting an additional question into the middle of the list, as the questions need to be renumbered.

So for example, if I have 10 questions, and insert one after Q3, I then need to renumber Q4 to Q5, Q5 to Q6 and so on.

Is there any way I can edit the comments to increase sequentially using a Macro, as opposed to editing each one individually? It becomes a bit tedious when there are 100's of questions!

I hope someone can help!

Thanks

CaseyBhoy

 

[GlennUK]

Can I ask why you are inserting a new answer box? If you are inserting a new question, can you not insert cells that move the answer box ... you should then only need to add a new answer box at the end of the list.

 

[Gary McMaster]

I believe the sample below may do what you want. Please try on a back-up copy of your work.

Hope it helps.

Gary

Public Sub Test()

Dim oComment As Comment
Dim iCount As Integer

For Each oComment In ActiveSheet.Comments
    iCount = iCount + 1
    oComment.Shape.TextFrame.Characters.Text = "Question " & iCount
Next oComment

End Sub

 

[energman58]

You can add or change comments programatically using syntax that looks like this:

On Error resume Next
Range("A25").AddComment
Range("A25").Comment.Visible = False
Range("A25").Comment.Text Text:=Range("d3").Value & Chr(10) & "your text here"

So you could run through each cell with an answer box in it and add or update a sequence number for it but I am not aware of any way of going through all the comments and updating them with new text like you could with say drawing objects

 

[CaseyBhoy]

Thanks Guys, that has helped a lot!

Getting more interesting - What if the comments were created using a form?

For example:

Each comment contains 3 pieces of info

txtQuestion.Value & txtHeader.Value & txtOrder.Value

These are entered into each individual cell comment using a form containing the three input boxes.

Is there a way to increment txtOrder.Value by 1 each time as you progress through a range - but leave the other values the same?

Any thoughts would help me immensely!

Thanks

Scott

 

[Gary McMaster]

Is there a way to increment txtOrder.Value by 1 each time as you progress through a range - but leave the other values the same?

Can you show several samples of this comment text? It will depend on how the various fragments are delimited and how consistent the delimiting is.

Gary

 

[CaseyBhoy]

Gary

Each cell is populated with the following, entered using a form:


strCellCommentContents = txtQuestion.Value & "|" & txtHeader.Value & "|" & txtOrder.Value & "|" & txtColumn.Value & "|" & ValidationType & "|" & Me.chkSummary.Value & "|"

RangeCurrentCell.Comment.Text Text:=strCellCommentContents

Does this help?

Thanks

Scott

 

[GlennUK]

I still say that my suggestion makes the most sense so far ... don't move the comments when inserting, and just add a new one at the end.

 

[Gary McMaster]

Here's an example that uses the VBA "Split" function to break your string at the "pipe" symbol, make a substitution for the 3rd element and then piece it back together. There are numerous other string functions that could also work depending on the length and structure of the individual fragments.

Gary

Public Sub Test()

Dim oComment As Comment
Dim iCount As Integer
Dim sNewText As String
Dim iSplit As Integer
Dim vSplit As Variant

For Each oComment In ActiveSheet.Comments

    iCount = iCount + 1
    vSplit = Split(oComment.Text, "|")
    vSplit(2) = "Question " & iCount 'Replace 3rd array element with new text
    sNewText = ""
    
    For iSplit = LBound(vSplit) To UBound(vSplit) - 1
        sNewText = sNewText & vSplit(iSplit) & "|"
    Next iSplit
    
    oComment.Shape.TextFrame.Characters.Text = sNewText
    
Next oComment

End Sub

 

[CaseyBhoy]

Gary

That is absolutely brilliant, thanks very much!

Scott