Restoring cursor/insertion point position

[asifbaig]

I'm a bit new to VBA. I've desgined a couple of macros that do text manipulation and that has the insertion point flying all over the document. And after the macro, I have to relocate it. That is not a problem with small documents but on long book reports or manuals, this can be fairly annoying.

I've been trying to find a solution by storing the insertion point location in a variable, performing the rest of macro then recalling it. Unfortunately, theory is as far as I have gotten. Playing around with range and selection methods didn't yield the desired results so I'm definitely not doing something right.

Can someone please tell me how to save the insertion point location into a variable (or maybe object?) and then restore it later?


(I searched the forum for something like this but came up empty so apologies if this has already been answered before.)

[lucas]

Insert a bookmark. From an example provided by Gerry(fumie)

http://www.vbaexpress.com/kb/getarticle.php?kb_id=63

[asifbaig]

Thank you. Looking at this example, I realize I probably wouldn't have been able to figure it out on my own even if I had all night. :-) Never even thought of using bookmarks.

[fumei]

"I've desgined a couple of macros that do text manipulation and that has the insertion point flying all over the document."

I would also like to point that most likely you can do anything you are doing now without the insertion point flying all over the document.

If it is, then you are using Selection. In 99% of cases, using Selection is both not needed and a inefficient use of resources. In 99% of cases, using Range (rather than Selection) is faster, and does not do anything on-screen.

[macropod]

Hi Gerry,

Of course, even without using Selection, changing text before the insertion point and/or reformatting the document could see the insertion point disappear from the screen.

asifbaig: You can store the current insertion point via code like:
Dim MyRange as Range
Set MyRange = Selection.Range
Then, when your code's finished whatever it needs to do, restore the insertion point via code like:
MyRange.Select
Set MyRange = Nothing

You can also avoid the screen flicker (and accelerate the code's execution) with:
Application.Screenupdating = False
at the start of your code, followed by:
Application.Screenupdating = True
at the end

[fumei]

Hi Paul,

"Of course, even without using Selection, changing text before the insertion point and/or reformatting the document could see the insertion point disappear from the screen."

Yes, I was too fast with the " does not do anything on-screen". Of course using Range affects the on-screen...eventually. My point was that performing actions - say doing stuff on an area pages away from where the cursor is - does not require moving the cursor to that area. Hence:

"I would also like to point that most likely you can do anything you are doing now without the insertion point flying all over the document."

[asifbaig]

Thanks guys, especially macropod for that code. It's a bit more simplified than the bookmark thing and fits my bill exactly.

And I'm using the macros to replace "placeholder" texts in a document with the appropriate values that have been acquired, by a form, for example. Won't selection be a better technique for this instead of range? If not, how would one go about using range to replace text?

[fumei]

If by placeholder, you mean bookmarks, then absolutely not. Selection is not better. Why would you think so?

Take a look at the demo atatched. First, look at the document. There is some text, and then a bunch of blank pages, and then some more text on page 8.

OK?

Now bring the cursor (Selection) back to the start of the document. There is text on page 8, right?

Now click "Show Userform" on the top toolbar.

Put some text into textbox1.
Tab.
Put some text into textbox2.
Tab.
Put some text into textbox3.
Tab (to commandbutton "Replace")
Press Enter.


VOILA!

The text is replaced at the locations I set, INCLUDING the location on page 8...all without moving the cursor. This is using Range. Nothing is "flying all over the document".

[asifbaig]

WOW! I never knew bookmarks could point to a range. When I said placeholder text, I literally meant text like <patient name>. And I'd have to configure my document to go to that exact word (by paragraph, line and word references) and then replace the text. Your method makes it as easy as replacing the text in a text box! Thanks a lot! :-)

[fumei]

"WOW! I never knew bookmarks could point to a range."

Bookmarks are ranges. That is their whole being. It is what they are for. One could say it is their purpose (but that makes it sound a wee bit too mystical...just kidding). Being a range. That is what defines a bookmark AS a bookmark.

[asifbaig]

I'll be sure to revere the bookmarks properly from now on. ;-)

Slightly unrelated question. In that document that you attached, there was a new tab on the Ribbon named "Add-in" with the Show userform button. It would be a very convenient alternative to browsing through the macro list. How did you make that button?

[lucas]

Gerry doesn't use 2007 so he did it like this:

create custom menu item (http://slucas.virtualave.net/Wink/CustomMenuItem.htm)


It's more difficult in 2007 I think and you will have to read or post questions about it in the Office 2007 Ribbon UI Forum (http://www.vbaexpress.com/forum/forumdisplay.php?f=96)

[asifbaig]

Yeah. I was wondering the same since the document he uploaded was a .doc file. I'm totally in love with the ribbon interface but adding new stuff to it requires way too much xml from where I'm standing right now. 'Tis a shame when simple things are made more complicated just for the sake of change.

[fumei]

" I'm totally in love with the ribbon interface"

And there you, a great example of how different people are.

Me? I absolutely dispise the ribbon. Just hate it. I refuse to use 2007 precisely because of the ribbon.

[macropod]

I absolutely dispise the ribbon. Just hate it. I refuse to use 2007 precisely because of the ribbon.I guess you won't be upgrading to Office 2010 anytime soon, then ...

[fumei]

Not a bloody chance. I will be retiring soon enough, and when I do, and no longer using the Internet at all, just doing my own documents for my own use...I will be switching 100% to OpenOffice. I have been using Word since it was first released, but this appears to be the end of the road for me.

[asifbaig]

But the ribbon is soooooo pretty... :-P

A small question. I tried that bookmark.range.text = blahblah technique and I noticed that after replacing the text, the bookmark disappears. This means that I can't reuse that bookmark should I want to replace text again within the same document or to modify it some other way.

I did manage to get a workaround to preserving the bookmark by using bookmark.range.select, then replacing the selection text and redefining the bookmark. However, I'm wondering if there's an easier way that I'm overlooking by which the bookmark is not lost once its text has been altered.

[lucas]

You can use Gerry's funtion and call it each time and re use the bookmarks:


Option Explicit
Sub FillABookmark(strBM_Name As String, strBM_Text As String)
With Selection
.GoTo what:=wdGoToBookmark, Name:=strBM_Name
.Collapse Direction:=wdCollapseEnd
ActiveDocument.Bookmarks(strBM_Name).Range.Text = strBM_Text
.MoveEnd unit:=wdCharacter, Count:=Len(strBM_Text)
ActiveDocument.Bookmarks.Add Name:=strBM_Name, Range:=Selection.Range
.Collapse Direction:=wdCollapseEnd
End With
End Sub

Sub TestIt()
Dim sText As String
sText = InputBox("Enter some text.")
Call FillABookmark("Test", sText)
End Sub



In the sub TestIt, the bookmark that is recalled each time you run it is Test.

sText is the string that is put in the bookmark and if you don't want to use the inputbox you can just put your sting there and comment the inputbox line out. Like this:

Sub TestIt()
Dim sText As String
'sText = InputBox("Enter some text.")
Call FillABookmark("Test", "Your Text")
End Sub

See attached example

[macropod]

Here's the code I use:

Sub UpdateBookmark (BmkNm as string, NewTxt as string)
Dim BmkRng as Range
With ActiveDocument
If.Bookmarks.Exists(BmkNm) Then
Set BmkRng =.Bookmarks(BmkNm).Range
BmkRng.Text = NewTxt
.Bookmarks.Add BmkNm, BmkRng
End if
End With
Set BmkRng = Nothing
End Sub
As you'll see, no Selection.

[fumei]

If.Bookmarks.Exists(BmkNm) Then

Always a good idea to test...

[asifbaig]

Thanks Macropod. That should really hit the spot. I can't believe I forgot again how bookmarks are destined to be RANGES. ;-)