You are here:

Excel/Macro that makes sentences

Advertisement


Question
QUESTION: Hi Tom,
I have created an excel file containing some words, small sentences, single characters such as full point, comma, semi colon, etc  in cells, because I want to make sentences out of them.
I want to make those cells as button. [Insert Rectangle auto shape and make it 100% transparent]
As I keep pressing on buttons, in the same order text should be connected in some other cell. (so that desired sentence is made with less efforts and less typing.)
Can you please tell me what macro should be written in those cells?
Thanks

ANSWER: Parantapkumar,

This is my guess at what you want:

I will assume you are using the old forms button from excel (not an activeX commandbutton)

sub buttonA1_Click()
Dim r as Range
Set r = Range("A1")
ActiveCell.Value = ActiveCell.Value &  r.value
End Sub


so as I understand it you would have your data say in separate cells going down column A (as an example) and next to each cell in column B would be a button.   The above code would be linked to the button in cell B1.    You would have separate modules for each button (there is a way to have only one macro and link it to all the buttons.  

Sub Button_Click()
Dim s as string, btn as button, r as Range
s = Application.Caller
btn = activesheet.buttons(s)
set r = btn.TopLeftCell.offset(0,-1)
activeCell.value = activecell.value & r.value
End Sub


The macros will append whatever is in the specified cell (r in the code above) to the activecell.  So select a cell in say F9 and start clicking buttons to build your sentence.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

Make Sentences with clicks - Allexperts
Make Sentences with cl  
QUESTION: Hi Tom,

Thanks for reply. What you have suggested is also a good thing and will be helpful somewhere else but,

As I mentioned, instead of button (for control) I want to make excel cell itself a button by Inserting Rectangle auto shape into the cell and then make it 100% transparent and then assign macro so the cell will become a button.
So now when I will click on that button, the content which is there into the same cell should be copied into predetermined cell.

What exactly I want to do is attached here with.
I know how to generate the column F viz. "Buttons Clicked"; but want to make sentences shown in Column J.

Please see the attached image.

I want to make something like this.

Thanks

ANSWER:
so there is no real change to my answer. In general

sub buttonMY_Click()
Dim r as Range
Set r = Range("B2")
ActiveCell.Value = ActiveCell.Value &  r.value
End Sub

The name of the routine can be anything.  Whether I name it buttonMY  or just MY or Dog, if I assign it to the rectangle over the cell containing the word my (or any other rectangle), then it should work.  Obviously you would need to change the range "B2" in each macro you create to refer to the cell to get the text from.  


If you don't use the activecell, then you would need to come up with some other way to designate the destination cell.

You indicated that you would have punctuation in separate cells.  I don't know how you want to handle spaced, but you would need to account for them either in your macro or in having a rectangle over a cell containing a space and clicking it when you need a space.

If you want to send me the file you showed an image of, I can demonstrate. twogilvy@msn.com

Putting transparent rectangles over the cells is something you would do manually.

The more general macro could be adapted as well if the rectangles are within the cell boundary.  

Summary:  Transparent rectangle or button over the cell or to the right of the cell really doesn't make much difference in the general approach and the original code offered could be easily adapted.   If you don't use the active cell then it is unclear how you would want to designate where the results should be assembled.  

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom,

Thanks so much. It does what I wanted to do.

The code you sent me is copied at the bottom. One is for Words and characters by which I can make sentences and the other is for "new line".


Yes, you rightly said that I forgot to put "is" in those buttons. (in fact, I am going to add many other characters and words including " " -Space.)

1. Your rightly pointed out in earlier reply- "I don't know how you want to handle spaced, but you would need to account for them either in your macro or in having a rectangle over a cell containing a space and clicking it when you need a space. "

You have incorporated the spaced problem into the macro itself, it is really nice and thanks for that, but I want to handle it by clicking. I am going to put one more rectangle  for " " a space. So if I click on space for 5 times, then the same should be written.

2. As I said earlier, I want the content to be copied into predetermined cell and not the selected cell. (Because I am going to do more work on those results as well. viz. Vlookup that sentence or word then extract True or False related to that.) Yes, it was unclear where to assembled then let me say J1. Which I can change from VBA Screen right?
but keeping the further task (vlookup) in mind, If you think It is going to be more complicated, then I think it's fine to work from selected cell rather than predetermined cell.

Thanks.


-------
The Code You Sent:
--------
For Rectangle Buttons of Words and special characters:-
---------

Sub button_Click()
Dim s As String, r1 As Range, s1 As String
Dim r As Range, rec As Rectangle
s = Application.Caller
Set rec = ActiveSheet.Rectangles(s)
Set r = rec.TopLeftCell
Select Case r.Value
 Case ".", "?"
    s1 = vbNullString
 Case Else
    s1 = " "
End Select
ActiveCell.Value = Application.Trim(ActiveCell.Value) & s1 & r.Value
End Sub
-------------------

---------------
For New Line:-
---------------

Sub button_NewLine()
ActiveCell.Offset(1, 0).Select
End Sub
---------------

Answer

If you want to specify a specific cell, Change ActiveCell to wherever you want to write your data.

I out the space code:

---------

Sub button_Click()
Dim s As String, r1 As Range, s1 As String
Dim r As Range, rec As Rectangle
s = Application.Caller
Set rec = ActiveSheet.Rectangles(s)
Set r = rec.TopLeftCell
ActiveCell.Value = ActiveCell.Value & r.Value
End Sub

so for J1 specifically as you state

Sub button_Click()
Dim s As String, r1 As Range, s1 As String
Dim r As Range, rec As Rectangle
s = Application.Caller
Set rec = ActiveSheet.Rectangles(s)
Set r = rec.TopLeftCell
Range("J1").value = Range(J1").Value & r.Value
End Sub

New line would be meaningless in the above situation but for completenesss

Sub button_NewLine()
Range("J2").Select
End Sub

so yes, you can change the specific cell by  editing the code in the VBE

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.