VBA: After copying a cell's comment (within a table) how to move backto that cell

[LordHog]

Hello,

I am trying create a macro that auto increments the first column
(e.g., step numbers) and maintain any comments within that cell. I
have a macro that will auto increment and copy the cell's comment, but
once I insert the comment I can't move to the next well properly as it
cursor is not within that cell where the comment was inserted. I have
tried various methods, but can't get anything to work well. Below is
the VBA macro that performs this task. It is not pretty, but sort of
works:

Sub InsertStepNumbersEx()

Dim stepCount As Integer
Dim startStep As String
Dim aCom As Comment
Dim comStr As String
Dim oRng As Range
Dim aSel As Selection

startStep = InputBox(Prompt:="Enter the starting step number", _
Title:="Starting Step Number", Default:="0")

stepCount = CInt(startStep)
While Selection.Information(wdWithInTable) = True

' Select the current cell comment
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Set aCom = Selection.Comments(1)
aStr = aCom.Range.Text

' Insert the Step Numner
Set aSel = Selection
Selection.TypeText Text:=stepCount & "."

' Select the current cell and add the comment
If Not IsEmpty(aStr) Then
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Set oRng = Selection.Range
Selection.Comments.Add Range:=oRng
Selection.TypeText aStr
End If

Selection = aSel
Selection.MoveDown Unit:=wdLine, Count:=1
stepCount = stepCount + 1

Wend

End Sub


Any help on getting the cursor to move back to the cell's original
position is greatly appreciated.

Mark

 

[Pesach Shelnitz]

Hi,

From what you wrote and the code that you supplied, it seems to me that all
you want to do is to add step numbers to the text in the cells in the first
column and not to do anything to the comments in those cells. I'll admit that
I may have not correctly understood your request, but if I did correctly
understand you, the following modified version of your macro should do what
you want.

Sub InsertStepNumbersEx()
Dim stepCount As Integer
Dim startStep As String
Dim comStr As String

startStep = InputBox(Prompt:="Enter the starting step number", _
Title:="Starting Step Number", Default:="0")

stepCount = CInt(startStep)
For i = 1 To Selection.Tables(1).Rows.Count
With Selection.Tables(1).Rows(i).Cells(1)
' Insert the Step Number
.Range.InsertBefore Text:=stepCount & ". "
End With
stepCount = stepCount + 1
Next
End Sub

If you want to insert the step numbers into a different location and/or you
want to change or add comments in the table, please write back and explain
what you would like.

 

[LordHog]

Hi,

From what you wrote and the code that you supplied, it seems to me that all
you want to do is to add step numbers to the text in the cells in the first
column and not to do anything to the comments in those cells. I'll admit that
I may have not correctly understood your request, but if I did correctly
understand you, the following modified version of your macro should do what
you want.

Sub InsertStepNumbersEx()
  Dim stepCount As Integer
  Dim startStep As String
  Dim comStr As String

  startStep = InputBox(Prompt:="Enter the starting step number", _
          Title:="Starting Step Number", Default:="0")

  stepCount = CInt(startStep)
  For i = 1 To Selection.Tables(1).Rows.Count
      With Selection.Tables(1).Rows(i).Cells(1)
          ' Insert the Step Number
          .Range.InsertBefore Text:=stepCount & ". "
      End With
      stepCount = stepCount + 1
  Next
End Sub

If you want to insert the step numbers into a different location and/or you
want to change or add comments in the table, please write back and explain
what you would like.

Pesach,

Thanks for you feedback, but unfortunately suggested solution
doesn't work quite right. My original little macro was similar, but
used conditional loop of:

While Selection.Information(wdWithInTable) = True

Perhaps it might be easier if I explained what I am trying to do as
a list:

1) Iterate through a table and insert (replace if existing text exist
in the cell) step numbers
2) Many cells have comments associated with the step numbers (these
are test procedures) and I need to preserve the comments
3) The comments need to be applied to the inserted step number and
does not need to follow the step number (just the row number)

The suggested code works fine if I don't need to preserve the
comments. The problem I seem to be having is once I restore the
comment the cursor is no longer in the table so when the code attempts
to move to the next cell (in either code base) it will crash since the
cursor does not reside in the table any more.

Using your initial code base, this is what I came up with though
still doesn't work correctly:


Sub InsertStepNumbersEx3()

Dim stepCount As Integer
Dim startStep As String
Dim aCom As Comment
Dim oRng As Range
Dim aSel As Selection

startStep = InputBox(Prompt:="Enter the starting step number", _
Title:="Starting Step Number", Default:="0")

stepCount = CInt(startStep)

For i = 2 To Selection.Tables(1).Rows.Count
With Selection.Tables(1).Rows(i).Cells(1)

' Select the current cell then store the comment to be restored
' later
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
aStr = Selection.Comments(1).Range.Text

' Insert the Step Number
Selection.TypeText Text:=stepCount & "."

' Select the current cell and add the comment
If Not IsEmpty(aStr) Then
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Set oRng = Selection.Range
Selection.Comments.Add Range:=oRng
Selection.TypeText aStr
End If

End With
stepCount = stepCount + 1
Next

End Sub


I will continue to work on it on my end and if I find a solution I
will post it. If anyone know how to resolve this issue I would
greatly appreciate it. Thanks!

Mark

 

[Doug Robbins - Word MVP]

In place of

Selection.Comments.Add Range:=oRng
Selection.TypeText aStr

Use
ActiveDocument.Comments.Add oRng, aStr


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[LordHog]

In place of

        Selection.Comments.Add Range:=oRng
        Selection.TypeText aStr

Use
    ActiveDocument.Comments.Add oRng, aStr

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Doug,

Using you suggestion and modifying some of my buggy code I came up
with the following which seems to work well enough.

Sub InsertStepNumbersEx()

Dim stepCount As Integer
Dim startStep As String
Dim aCom As Comment
Dim aStr As String
Dim oRng As Range
Dim aSel As Selection

startStep = InputBox(Prompt:="Enter the starting step number", _
Title:="Starting Step Number", Default:="0")

stepCount = CInt(startStep)
While Selection.Information(wdWithInTable) = True

' Select the current cell comment
Selection.Cells(1).Select
If Selection.Comments.Count = 1 Then
aStr = Selection.Comments(1).Range.Text
Else
aStr = vbNullString
End If

' Insert the Step Numner
Set aSel = Selection
Selection.TypeText Text:=stepCount & "."

' Select the current cell and add the comment
If (aStr <> vbNullString) Then
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Set oRng = Selection.Range
ActiveDocument.Comments.Add oRng, aStr
End If

Selection.MoveDown Unit:=wdLine, Count:=1
stepCount = stepCount + 1

Wend

End Sub

Interesting side note, I tried using the For-Loop method suggested by
Pesach, but the cursor wouldn't move to the next row. I am not sure
why, probably a bug in my end. Any how, I am happy that all is
working and thanks to Pesach and Doug for there help.

Mark