Save Selection

cksm4 · #1 01-11-2011, 10:57 AM

I am new to VBA so this is going to sound beginner (sorry). How do I save a selection of text for later use in the macro?

I need to look for text, tab to the next cell, and copy the contents of that cell for later use.

Thanks for any help you all can provide!

Brock

macropod · #2 01-12-2011, 12:08 AM

Hi Brock,

Which application? Your description could apply equally to a Word table or an Excel worksheet.

cksm4 · #3 01-12-2011, 06:17 AM

Hey Paul,

Thanks again for replying. I worked on this last night and made a little progress. As you can see below I set the selected text to a variable:

Selection.Find.ClearFormatting
With Selection.Find
.Text = "Level 1"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory1 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Level 2"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory2 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Level 3"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory3 = Selection.Text
Selection.GoTo What:=wdGoToBookmark, Name:="IndexThis"
ActiveDocument.Indexes.MarkEntry Range:=Selection.Range, _
Entry:=vCatagory1 & ":" & vCatagory2 & ":" & vCatagory3, CrossReference:="", CrossReferenceAutoText:="", _
BookmarkName:="", Bold:=False, Italic:=False

This creates a three level index based on the values of the three fields. It works but I am back to the loop issue as this code needs to run for each document. My final document is a combination of mutiple forms (page breaks between) filled out by the user. I can run this code on each form as it is created, but I prefer to run it just once after the final combination is complete. I am struggling with the transition from the first search to the "Do While" and have not been able to get it to work.

Brock

macropod · #4 01-12-2011, 02:53 PM

Hi Brock,

Quote:

This creates a three level index based on the values of the three fields.

OK, but if you know the fields' bookmark names, or at least their relative position in the document, you can get the values without and Find/Replace code. For example:

Code:

Sub Demo()
Dim strTmp As String
With ActiveDocument
  strTmp = .FormFields(1).Result
  strTmp = strTmp & ":" & .FormFields(2).Result
  strTmp = strTmp & ":" & .FormFields(3).Result
  .Indexes.MarkEntry Range:=.Bookmarks("IndexThis").Range, _
    Entry:=strTmp, CrossReference:="", CrossReferenceAutoText:="", _
    BookmarkName:="", Bold:=False, Italic:=False
End With
End Sub

If you know the formfield bookmark names, you can replace the 1, 2 & 3 with those. This provides an added level of flexibility. Note too that I haven't had to select anything.

PS: When posting code, please use code tags.

cksm4 · #5 01-12-2011, 03:45 PM

Hey Paul,

These are rich text controls. Are there bookmark names?

10-4 on the code tags

macropod · #6 01-12-2011, 06:18 PM

Hi Brock,

Your reference in previous posts to fields and forms led me to believe you were working with formfields.

Nonetheless, the same principles apply:

Code:

Sub Demo()
Dim strTmp As String
With ActiveDocument
  strTmp = .ContentControls(1).Range.Text
  strTmp = strTmp & ":" & .ContentControls(2).Range.Text
  strTmp = strTmp & ":" & .ContentControls(3).Range.Text
  .Indexes.MarkEntry Range:=.Bookmarks("IndexThis").Range, _
    Entry:=strTmp, CrossReference:="", CrossReferenceAutoText:="", _
    BookmarkName:="", Bold:=False, Italic:=False
End With
End Sub

If you were to bookmark the controls, you could retrieve the text via the bookmarks:

Code:

Sub Demo()
Dim strTmp As String
With ActiveDocument
  strTmp = .Bookmarks("BkMk1").Range.ContentControls(1).Range.Text
  strTmp = strTmp & ":" & .Bookmarks("BkMk2").Range.ContentControls(1).Range.Text
  strTmp = strTmp & ":" & .Bookmarks("BkMk3").Range.ContentControls(1).Range.Text
  .Indexes.MarkEntry Range:=.Bookmarks("IndexThis").Range, _
    Entry:=strTmp, CrossReference:="", CrossReferenceAutoText:="", _
    BookmarkName:="", Bold:=False, Italic:=False
End With
End Sub

Note how the bookmarks change, but we're always referring to the first content control within the bookmarked range.

cksm4 · #7 01-13-2011, 05:50 AM

Thanks Paul... works great (much easier and faster)!

I am assuming that the name of the control (1, 2, 3... etc) is based on its order. Is this true? Is it possible to name a control differently?

Also, once I have merged all my documents together, is there a way to run this by section? This way the macro will perform this same task for each of the documents I merged.

Thanks again Paul... you help has been great!

Brock

macropod · #8 01-13-2011, 01:52 PM

Quote:

Originally Posted by cksm4

I am assuming that the name of the control (1, 2, 3... etc) is based on its order. Is this true? Is it possible to name a control differently?

I believe so.
Also, once I have merged all my documents together, is there a way to run this by section?[/quote]Simple. Try something along the lines of:

Code:

Sub Demo()
Dim strTmp As String, iSctn As Integer
iSctn = InputBox("Which Section to process?")
If iSctn = "" Then Exit Sub
With ActiveDocument.Sections(iSctn).Range
  strTmp = .ContentControls(1).Range.Text
  strTmp = strTmp & ":" & .ContentControls(2).Range.Text
  strTmp = strTmp & ":" & .ContentControls(3).Range.Text
  .Indexes.MarkEntry Range:=.Bookmarks("IndexThis").Range, _
    Entry:=strTmp, CrossReference:="", CrossReferenceAutoText:="", _
    BookmarkName:="", Bold:=False, Italic:=False
End With
End Sub

cksm4 · #9 01-13-2011, 02:33 PM

Hey Paul,

The bookmark will no longer work because as documents are combined, it just keeps getting replaced. I used the below code to go to the next row to input the index.

Code:

 
Dim strTmp As String, iSctn As Integer
iSctn = InputBox("Which Section to process?")
If iSctn = "" Then Exit Sub
With ActiveDocument.Sections(iSctn).Range
  strTmp = .ContentControls(2).Range.Text
  strTmp = strTmp & ":" & .ContentControls(4).Range.Text
  strTmp = strTmp & ":" & .ContentControls(3).Range.Text
  Selection.MoveRight Unit:=wdCell
  ActiveDocument.Indexes.MarkEntry Range:=Selection.Range, _
    Entry:=strTmp, CrossReference:="", CrossReferenceAutoText:="", _
    BookmarkName:="", Bold:=False, Italic:=False
End With

Would it also be possible to have the code run the whole document automatically as opposed to allowing the user to select sections?

macropod · #10 01-13-2011, 03:16 PM

Hi Brock,

You can only ever have one instance of a bookmark in a document.

With your 'IndexThis' bookmark, you would need to modify the code to preserve it and, when adding entries, not to simply overwrite what's already there. All possible but your reference to 'the next row' suggests you're using a table. In that case, you could fairly easily iterate through the table rows (even adding rows as needed) without using selections.

Quote:

Would it also be possible to have the code run the whole document automatically as opposed to allowing the user to select sections?

Yes - it's just a matter for using a For each ... Next loop to work through the Sections.

cksm4 · #11 01-13-2011, 05:30 PM

Do the content controls preserve their name as you combine documents? Or do they continue to add number?

I have decided to go back to the search method. I will be allowing users to use some older forms that have the same content but are formatted differently. The find method allows them to do this. The below code works, but is not looping though each table in my document. I know this is basic VBA... but I am unable to get this to loop the whole document.

Code:

 
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Catagory 1"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
vCatagory1 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Catagory 2:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory2 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Catagory 3"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory3 = Selection.Text
Selection.MoveRight Unit:=wdCell
ActiveDocument.Indexes.MarkEntry Range:=Selection.Range, _
Entry:=vCatagory1 & ":" & vCatagory2 & ":" & vCatagory3, CrossReference:="", CrossReferenceAutoText:="", _
BookmarkName:="", Bold:=False, Italic:=False

macropod · #12 01-14-2011, 03:24 AM

Hi Brock,

Quote:

Do the content controls preserve their name as you combine documents? Or do they continue to add number?

Sorry, I don't understand the question. In any event, I've never needed to use Content Controls for my own documents.

cksm4 · #13 01-14-2011, 09:29 AM

Hey Paul,

Lets say that there is ContentControl(1), ContentControl(2), ContentControl(3) on my form. I then combine two or more of these forms together. Does ContentControl(1), ContentControl(2), ContentControl(3) on the second form rename to ContentControl(4), ContentControl(5), ContentControl(6) and so on? Or do the controls retain their name?

I believe that command buttons keep adding numbers as no two can have the same name.

I do not know where to find the name of a content control so I cannot manually check this. I also tried writing the code to loop through each form but that also failed:/ so I cannot check it with code.

But now with more though about what works best… I want the users to also be able to use prior versions of the form. The old forms do not have content controls and the location in the table of the fields I am searching for are different.

Rich text content controls are new for me as well... I tried in the past to use form fields but the limited use of these fields was very preventative to users and they did not like it. I like the rich text ones because it still allows them to input tables, spell check (without the use of a macro), and generally use Word in a manner they are accustom to. Many of our users already have limited Word understanding and many users need the ability to document text using bullets, tables, etc.

On my prior post, can you direct me how to loop the code? Everything I have tried just duplicates the entries for the last form.

How’s your summer over there in the capital? My dad lived in Australia a long time ago, amongst many other continents, and it was the one place he always spoke about returning.

Thanks
Brock

macropod · #14 01-14-2011, 03:47 PM

Hi Brock,

Quote:

Lets say that there is ContentControl(1), ContentControl(2), ContentControl(3) on my form. I then combine two or more of these forms together. Does ContentControl(1), ContentControl(2), ContentControl(3) on the second form rename to ContentControl(4), ContentControl(5), ContentControl(6) and so on? Or do the controls retain their name?

If you insert the new material after the existing material, the former numbers will remain the same. Obviously, though, the new set can't also be numbered 1,2,3 etc - they'll be numbered 4,5,6 etc. The converse applies if you insert the new material before the existing material. Of course, if you're working on a Section basis, that won't matter, as they'll all be numbered 1,2,3 etc within their own Section.

Quote:

I do not know where to find the name of a content control so I cannot manually check this.

Unlike formfields, content controls don't have 'bookmark' names. In any event, as I said before, you can only have one instance of a givven bookmark name in a document. If you combine two documents, both of which use the same bookmarks, then half the bookmarks will disappear.

Quote:

On my prior post, can you direct me how to loop the code?

What are you trying to loop - the Sections or adding to the 'IndexThis' bookmark (which you can only have one of for the whole document)- or both? From what you've described so far, Find/Replace is not a good way to approach this IMHO.

cksm4 · #15 01-14-2011, 06:39 PM

Hey Paul,

Quote:

What are you trying to loop - the Sections or adding to the 'IndexThis' bookmark

Each document will always have the wording "Category 1" near the top of the document. Since I will be combining the documents into one document prior to running the below code, I decided not to use a bookmark to place the index. I just have the code go the next row after the last category is found and paste the index there. This next row is blank and can be used for this.

Code:

Selection.Find.ClearFormatting
With Selection.Find
.Text = "Category 1"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
vCatagory1 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Category 2:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory2 = Selection.Text
Selection.Collapse
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Category 3"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
vCatagory3 = Selection.Text
Selection.MoveRight Unit:=wdCell
ActiveDocument.Indexes.MarkEntryRange:=Selection.Range, _
Entry:=vCatagory1 & ":" & vCatagory2 & ":" & vCatagory3, CrossReference:="", CrossReferenceAutoText:="", _
BookmarkName:="", Bold:=False, Italic:=False

I would be nice to also limit the code to sections so that if somehow it found "Category 1" and not "Category 2" it does not go to the next section (another form) looking for "Category 2". Just me being cautious!

Quote:

From what you've described so far, Find/Replace is not a good way to approach this IMHO.

I can use code to find ContentControl(1) because I will be combining multiple of these forms into one document prior to running this code. It is my understanding then that after I do this, the code will only find one ContentControl(1)... the one on the last form. The ones on all the rest of the forms will rename.

Thanks again Paul!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Textbox updating from combobox selection	paxile2k	Word VBA	0	10-26-2010 02:30 PM
Document selection procedure	kennethc	Word	0	09-15-2010 02:56 PM
The modification is not allowed because selection is locked	aligahk06	Word	0	09-06-2010 06:28 AM
Automatic find replace after selection in dropdown	vsempoux	Word	0	10-28-2009 08:45 AM
Highlighted Selection on Action Settings	mos7sad	PowerPoint	0	10-12-2009 07:48 AM