Controlling "control buttons"

[jontait]

Hi,

I've a few control buttons within my excel spreadsheet that users ca
click on to open up files. (the buttons are "help" buttons explainin
what different terms mean within the spreadsheet)

The code I'm using (very kindly supplied by someone in this forum) is:

Private Sub CommandButton1_Click()
ActiveWorkbook.FollowHyperlink _
Address:="C:\test.doc", _
NewWindow:=True
End Sub

However, there will be instances when users press many of the button
within one session, if all the documents the user is hyperlinking t
are Word, then I end up with lots of Word windows open - one for eac
button pressed

Is there any way to modify the code to only open a new window if ther
isn't one already there? I.e. if its a PDF document and there's alread
been a PDF document opened earlier then there's no need to open Acroba
Reader again?

I hope this makes sense, and as always THANK YOU in anticipation o
your replies.

Jo

 

[Dave Peterson]

I don't know how to do it with Adobe, but with word, you could do something
like:

Option Explicit
Sub CommandButton1_Click()

Dim oWord As Object
Dim myWordDocument As String
Dim testStr As String

myWordDocument = "C:\DATA\Word\EXCEL HELP.doc"

testStr = ""
On Error Resume Next
testStr = Dir(myWordDocument)
On Error GoTo 0
If testStr = "" Then
MsgBox myWordDocument & " doesn't exist"
Exit Sub
End If

On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = CreateObject("Word.Application")
End If

oWord.Visible = True
oWord.Documents.Open myWordDocument
AppActivate "Microsoft Word"

End Sub

In fact, if you have lots of these buttons, you'll have lots of code that looks
exactly alike (except for the document name).

You could separate that portion into a subroutine in a General module:

Option Explicit
Sub LoadUpWord(myFileName As String)

Dim oWord As Object
Dim myWordDocument As String
Dim testStr As String

myWordDocument = myFileName

testStr = ""
On Error Resume Next
testStr = Dir(myWordDocument)
On Error GoTo 0
If testStr = "" Then
MsgBox myWordDocument & " doesn't exist"
Exit Sub
End If

On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = CreateObject("Word.Application")
End If

oWord.Visible = True
oWord.Documents.Open myWordDocument
AppActivate "Microsoft Word"

End Sub

and your commandbutton1 code could change to something like:

Option Explicit
Private Sub CommandButton1_Click()
Call LoadUpWord(myFileName:="C:\DATA\Word\EXCEL HELP.doc")
End Sub