Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2005

Tip: Looking for answers? Try searching our database.

Building a paragraph from a list in Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bobby - 28 Dec 2005 22:12 GMT
Hello All,

I am building a quoter tool for a client who does masonry work.  The
part that he hates the most is writing scope paragraphs on his quotes,
i.e. the scope of the project that he is quoting (ex: Remove remaining
old sheetrock and old tile.  Install new tub and shower valves in same
location.  A Chloraloy shower plan will be used as the water proof
membrane. etc..).  The scope of the project could include several
different elements such as these and they would not really ever be the
same for any two quotes.

Cool project, but I am at a loss as to help him with writing the scope.
I was sort of thinking that if I had a list of potential scope
elements (such as the three examples above), that I could create a list
with data validation, which would create a pull-down menu from which he
could choose different elements and build a scope paragraph.  I don't
know how I would string these elements together at the top of the quote
to create a complete paragraph, or if that is even possible.

Is there anyway to weld all of these elements together in order to
create a paragraph in a single cell?  I know it is kind of a wierd
question, but my client is a wonderful, kind and creative artisan, who
kinda hates the buisness end of things, so I am trying to create a
really cool tool for him to make quoting projects easier.  Any ideas
would be appreciated.
Otto Moehrbach - 28 Dec 2005 23:25 GMT
Bobby
   Here is something to get you started.  I assumed that the sheet that has
the Data Validation cell is named "DataSht".  Then I added another sheet and
named it TempSht.
Place the first of the following macros in the sheet module for the DataSht
sheet.  To do this, right-click on the sheet tab, select View Code, and
paste this macro into that module.  Return to your spreadsheet by clicking
the "X" in the top right corner of the module.
Paste the second macro in a regular module.  I elected to insert a button on
the TempSht sheet and I assigned the second module to that button.
Here is how it works.
You click on the down arrow of the Data Validation cell in the DataSht
sheet.
You click on one of the items.
You repeat the above 2 steps as many times as you want to select the items
you want.
The code will place each item you select into Column A of the TempSht
starting in A2 and going down.
After you have selected all the items you wish to select, go to the TempSht
sheet.
Click on the button.
The code of the second macro will concatenate all the selected items, with 2
spaces between them, and put the result in A1 and set A1 format to "Wrap".
The result is all the items in paragraph form.  You can then copy/paste A1
of the TempSht as you wish.
I'm sending you a small file that has these macros properly placed.  I'm
sending this to the address that comes up from your post.  If this is not a
valid address, send me a valid address.  My email address is
ottokmnop@comcast.net.  Remove the "nop" from this address.  HTH  Otto

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "A1" Then
       If Target.Value = "" Then Exit Sub
       Sheets("TempSht").Range("A50").End(xlUp).Offset(1) = Target.Value
   End If
End Sub

Sub WrapItems()
   Dim Rng As Range
   Dim i As Range
   Dim Wrapped As String
   Wrapped = ""
   Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
   For Each i In Rng
       Wrapped = Wrapped & i.Value & "  "
   Next i
   Wrapped = Left(Wrapped, Len(Wrapped) - 2)
   [A1] = Wrapped
   [A1].WrapText = True
   Range("A2", Range("A" & Rows.Count).End(xlUp)).ClearContents
   Sheets("DataSht").Range("A1").ClearContents
End Sub
> Hello All,
>
[quoted text clipped - 21 lines]
> really cool tool for him to make quoting projects easier.  Any ideas
> would be appreciated.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.