Activate Excel Userform from MS Word

[MacroEcon1337]

I want to activate an Excel UserForm, from Word. Ideally the user would click on a word bookmark and a file dialog would appear, asking the user to select the target Excel file. Then, the macro would reference the Target Excel file and open a UserForm.

Note 1: The target excel file will always be open when the user activates it.

Note 2: The activation event on the word side would be clicking a certain bookmark.

Any ideas? I did solve this once but deleted the file and now I can't seem to create it again.

 

[Worf]

Hi

o Do you still need help with this?
o The Word code below shows how to determine if a certain bookmark was clicked.

' regular module


Function OneBM(rng As Range) As Boolean
OneBM = False
If rng.InRange(ActiveDocument.Bookmarks("page21").Range) Then OneBM = True
End Function


'*******************************************

' ThisDocument module
Dim ev As New Class1


Private Sub Document_Open()
Set ev.app = Word.Application
End Sub
'*******************************

' class module named Class1


Public WithEvents app As Word.Application
Private Sub app_windowselectionchange(ByVal sel As Selection)
MsgBox OneBM(sel.Range)
End Sub
'*************************************************************

 

[MacroEcon1337]

Worf, thanks for the response. I am seeking something slightly different...What I am looking for is VBA Code to insert, on the MS Word document side, which references an Excel-Based Userform.


Sequence
1. User opens Word Document X, and clicks on a bookmark located inside Word Document X.
2. The "click" event for this bookmark causes a Userform (Located in an Excel-File Module) to be activated.

Note 1: if it is the first time the user has clicked a Word Bookmark, a File Dialog box appears prompting the user to specify a Target Excel File. Word "remembers" this.
Note 2: if the name/location/path for the Target Excel file changes (ie the "remembered" file path is no longer valid), the prompt re-appears and asks the user to re-specify the Excel file.

I understand this is tricky and was not necessarily expecting a full solution. But any ideas are greatly appreciated,

MC

Hi

o Do you still need help with this?
o The Word code below shows how to determine if a certain bookmark was clicked.

' regular module


Function OneBM(rng As Range) As Boolean
OneBM = False
If rng.InRange(ActiveDocument.Bookmarks("page21").Range) Then OneBM = True
End Function


'*******************************************

' ThisDocument module
Dim ev As New Class1


Private Sub Document_Open()
Set ev.app = Word.Application
End Sub
'*******************************

' class module named Class1


Public WithEvents app As Word.Application
Private Sub app_windowselectionchange(ByVal sel As Selection)
MsgBox OneBM(sel.Range)
End Sub
'*************************************************************

 

[Worf]

o What bookmark type do you have? See explanation below.
o I am thinking of placing the workbook path as the bookmark displayed text, as Word needs to remember the information. Is this a problem?
o Happy New Year!

[h=2]Types of Bookmarks[/h]The most important thing you need to know when working with bookmarks in Word is that there are two [FONT=Times New Roman, Times-Roman]“types[FONT=Times New Roman, Times-Roman]”[/FONT] of bookmarks – [FONT=Times New Roman, Times-Roman]“[/FONT]placeholder[FONT=Times New Roman, Times-Roman]”[/FONT] bookmarks and [FONT=Times New Roman, Times-Roman]“[/FONT]enclosing[FONT=Times New Roman, Times-Roman]”[/FONT]bookmarks.[/FONT]
Before we proceed, and whenever you work with bookmarks, you should turn on display of bookmarks by going to Tools | Options | View and selecting [FONT=Times New Roman, Times-Roman]“Bookmarks[FONT=Times New Roman, Times-Roman]”[/FONT]. This makes it easier to see what's actually happening.[/FONT]
(1) Placeholder Bookmarks
If you click somewhere in the document and insert a bookmark it will look like a beam I– this is a [FONT=Times New Roman, Times-Roman]“placeholder[FONT=Times New Roman, Times-Roman]”[/FONT] bookmark.[/FONT]
(2) Enclosing Bookmarks
Now, if you select some text and insert a bookmark it will look like the selected text is enclosed in square brackets ie: [selected text] – this is an [FONT=Times New Roman, Times-Roman]“enclosing[FONT=Times New Roman, Times-Roman]”[/FONT] bookmark.[/FONT]

 

[MacroEcon1337]

Worf, i am away travelling a few days. I will post detailed answer to your followup questions asap. Thank you, apologies for slow response to my own question, and happy NY!

MC

o What bookmark type do you have? See explanation below.
o I am thinking of placing the workbook path as the bookmark displayed text, as Word needs to remember the information. Is this a problem?
o Happy New Year!

Types of Bookmarks

The most important thing you need to know when working with bookmarks in Word is that there are two “types” of bookmarks – “placeholder” bookmarks and “enclosing”bookmarks.
Before we proceed, and whenever you work with bookmarks, you should turn on display of bookmarks by going to Tools | Options | View and selecting “Bookmarks”. This makes it easier to see what's actually happening.
(1) Placeholder Bookmarks
If you click somewhere in the document and insert a bookmark it will look like a beam I– this is a “placeholder” bookmark.
(2) Enclosing Bookmarks
Now, if you select some text and insert a bookmark it will look like the selected text is enclosed in square brackets ie: [selected text] – this is an “enclosing” bookmark.