Building a paragraph from a list in Excel

[Bobby]

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]

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
(e-mail address removed). 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