Prev: arrays
Next: Critique a function to convert time, please
From: Mik on 29 Apr 2010 18:11
How do you copy the contents of a range of cells to a single comment
(indicator)?

I have a range of cells ("n12:n14"), and want to copy the contents
into a single comment indicator within the activecell.

anybody help?

Thanks
Mik
From: Mik on 29 Apr 2010 18:48
On 29 Apr, 23:11, Mik <mhol...(a)safetysystemsuk.com> wrote:
> How do you copy the contents of a range of cells to a single comment
> (indicator)?
>
> I have a range of cells ("n12:n14"), and want to copy the contents
> into a single comment indicator within the activecell.
>
> anybody help?
>
> Thanks
> Mik

I Have an addition / further thought....

I wish to add the selected text from a 3 column listbox (userform) to
the active cell as a comment (with indicator).

Can anybody advise how this is done using vba?

Mik

From: Dave Peterson on 29 Apr 2010 19:39
I created a small userform with a listbox and two commandbuttons on it (ok and
cancel).

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim myStr As String

If Me.ListBox1.ListIndex < 0 Then
Exit Sub 'nothing chosen yet
End If

With Me.ListBox1
myStr = .List(.ListIndex, 0) _
& "--" & .List(.ListIndex, 1) _
& "--" & .List(.ListIndex, 2)
End With

With ActiveCell
If .Comment Is Nothing Then
'no existing comment
Else
.Comment.Delete
End If
.AddComment Text:=myStr
End With

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "30,30,30"
'add some test data
For iCtr = 1 To 3
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = "B" & iCtr
.List(.ListCount - 1, 2) = "C" & iCtr
Next iCtr
End With

End Sub


Mik wrote:
>
> On 29 Apr, 23:11, Mik <mhol...(a)safetysystemsuk.com> wrote:
> > How do you copy the contents of a range of cells to a single comment
> > (indicator)?
> >
> > I have a range of cells ("n12:n14"), and want to copy the contents
> > into a single comment indicator within the activecell.
> >
> > anybody help?
> >
> > Thanks
> > Mik
>
> I Have an addition / further thought....
>
> I wish to add the selected text from a 3 column listbox (userform) to
> the active cell as a comment (with indicator).
>
> Can anybody advise how this is done using vba?
>
> Mik

--

Dave Peterson
 | 
Pages: 1
Prev: arrays
Next: Critique a function to convert time, please