Thanks Peter, that looks like an approach I will follow up on. I like the
..AutoSize...I didn't know about that :-) Looks like I'll be doing some more
work tomorrow.
The range is two columns wide but the number of rows can vary. It's a list
of variables the user implemented in a SAP report (which was exported as
Excel format). The variable list is placed inside a Comment for the user's
reference in the metrics workbook as a "nice to have" feature. We needed to
keep all info contained within the one metrics sheet without cluttering it
up. The folks who use the metrics workbook loved the idea because it's very
easily and quickly accessed and so what the customer wants...
Thanks again,
Toby
"Peter T" wrote:
> I haven't really followed all this but it seems a bit arbitary to place
> contents from an unknown range size into a comment. Anyway, here's a
> different approach to play with.
>
> Sub test()
> Dim s As String
> Dim dObj As DataObject ' need to temporarily add a Userform
> Dim cmt As Comment
>
> Set rng = Range("A1:C10") ' change to some current region
> rng.Value = "ABC" ' just for testing
>
> Set dObj = New DataObject
> rng.Copy
>
> dObj.GetFromClipboard
> s = dObj.GetText
>
> s = Replace(s, vbCr, "")
> s = Replace(s, vbTab, " | ")
>
> Range("E1").Comment.Delete
> Set cmt = Range("E1").AddComment(s)
> cmt.Shape.TextFrame.AutoSize = True
> End Sub
>
> Regards,
> Peter T
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:ObG%(E-Mail Removed)...
> > I not really an "expert" on this sort thing, but the approach you used is
> > probably how I would have tackled the problem... I am not aware of any
> > simpler method. Perhaps one of the other regular contributors may know of
> > such a simpler method and will post it later on.
> >
> > Rick
> >
> >
> > "Air_Cooled_Nut" <(E-Mail Removed)> wrote in message
> > news:4779BBA9-0C9F-45C8-B459-(E-Mail Removed)...
> > > Here's the code I'm using:
> > > --------- Code Start ---------
> > > ...
> > > 'Found the cell, now get the entire region of text
> > > vVari = ActiveCell.CurrentRegion
> > > SearchBook.Close
> > > 'Now build the string into a single variable
> > > For counter = 1 To UBound(vVari, 1)
> > > sTemp = sTemp & vVari(counter, 1) & ": " & vVari(counter, 2) & vbLf
> > > Next counter
> > > GetSAPVariables = sTemp
> > > ...
> > > --------- Code End ---------
> > >
> > > The values in the 2D range are text. Here is a small sample of what
> sTemp
> > > looks like:
> > > D-TARGET WOS: 12
> > > E-TARGET WOS: 14
> > > Future Sales Forecast(No.of Weeks): 8
> > > Month After Future Month Sales Forecast(8 wks default): 18/2007 -
> 25/2007
> > >
> > > Using vbCRLF leaves a special character at the end of each sentence (a
> > > square) which is why I'm just using the LineFeed command.
> > >
> > > Then I build the comment:
> > > --------- Code Start ---------
> > > ...
> > > With Sheets(SheetName).Range(COMMENT_LOCATION)
> > > .AddComment 'Adds the comment to the specific location
> > > With .Comment
> > > .Text Text:=CommentText 'Populate the comment...
> > > .Shape.ScaleWidth 3.65, msoFalse, msoScaleFromBottomRight
> > > '...and size it
> > > .Shape.ScaleHeight 7.89, msoFalse, msoScaleFromTopLeft
> > > End With
> > > End With
> > > ...
> > > --------- Code End ---------
> > >
> > > Does this help? I thought there may be a simpler way but the above code
> > > puts the text into the Comment almost like how it looks in the
> [original]
> > > sheet range (I added the colon to separate the two values).
> > >
> > > Toby
> > >
> > > "Rick Rothstein (MVP - VB)" wrote:
> > >
> > >> You might want to expand and clarify your question a little bit. The
> > >> CurrentRegion is (probably) made up of several cells in a 2-D
> > >> arrangement...
> > >> How did you want the text from each cell combined so it can be stored
> in
> > >> a
> > >> "variable"? What do you want linking the individual pieces of text from
> > >> each
> > >> cell (tabs, commas, spaces, something else for the columns, vbCrLf for
> > >> the
> > >> rows)? Or did you perhaps mean you want to store the text in a 2-D
> String
> > >> array?
> > >>
> > >> Rick
> > >>
> > >>
> > >> "Air_Cooled_Nut" <(E-Mail Removed)> wrote in
> > >> message
> > >> news:40157ED2-F5B8-4136-94FC-(E-Mail Removed)...
> > >> > How do I copy the CurrentRegion into a variable? The CurrentRegion
> is
> > >> > text
> > >> > and that's all I want to put into the variable (text, no formatting).
> > >> > TIA
> > >> > :-)
> > >>
> > >>
> >
>
>
>
|
Similar Threads
