Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??
--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Oh well, I am sure some of the users will look at it and think something
major has happened but they will just have to live with it.

Thanks again
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??
--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Suggest you open a new post:

How to Close a Toolbar from VBA


--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Oh well, I am sure some of the users will look at it and think something
major has happened but they will just have to live with it.

Thanks again
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??
--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Actually, the Recorder worked this time:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/30/2008 by James Ravenswood
'

'
Application.CommandBars("Web").Visible = False
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Oh well, I am sure some of the users will look at it and think something
major has happened but they will just have to live with it.

Thanks again
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??
--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

I was just about to say that I recorded a macro and gave that a try but I see
you have done the exact same.

Thanks
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Actually, the Recorder worked this time:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/30/2008 by James Ravenswood
'

'
Application.CommandBars("Web").Visible = False
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Oh well, I am sure some of the users will look at it and think something
major has happened but they will just have to live with it.

Thanks again
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??
--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Hi Leanne,

As an alternative to the suggestion of
Gary's Student, try:

'==========
Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim myFolder As String
Dim myDrive As String
Dim sStr As String
Const sFolder As String = _
"C:\My Documents\Pest Control Management System"
Const sFile As String = "Pest Control Reporting Tool.xls"

On Error Resume Next
Set WB = Workbooks(sFile)
On Error GoTo 0

If WB Is Nothing Then
myFolder = CurDir
myDrive = CurDir
Set WB = Workbooks.Open(Filename:=sFolder _
& Application.PathSeparator _
& sFile)
ChDrive sFolder
ChDir sFolder
End If

End Sub
'<<==========



---
Regards.
Norman


"Leanne M (Aussie)" wrote in
message ...
Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file
if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Hi Leanne,

Importantly, I omitted the switching line!

Replace:

End If

End Sub

with:

Else
WB.Activate
End If

End Sub


---
Regards.
Norman

Hi Leanne,

Correcting the path and folder assignments,
the code should have read:

'==========
Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim myFolder As String
Dim myDrive As String
Dim sStr As String
Const sFolder As String = _
"C:\My Documents\Pest Control Management System"
Const sFile As String = "Pest Control Reporting Tool.xls"

On Error Resume Next
Set WB = Workbooks(sFile)
On Error GoTo 0

If WB Is Nothing Then
myFolder = CurDir
myDrive = CurDir

ChDrive sFolder
ChDir sFolder
Set WB = Workbooks.Open( _
Filename:=sFolder _
& Application.PathSeparator _
& sFile)
ChDrive myFolder
ChDir myFolder
Else
WB.Activate
End If
End Sub
'<<==========


---
Regards.
Norman

Thanks Norman, it is always best to have alternatives.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Norman Jones" wrote:

Hi Leanne,

Correcting the path and folder assignments,
the code should have read:

'==========
Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim myFolder As String
Dim myDrive As String
Dim sStr As String
Const sFolder As String = _
"C:\My Documents\Pest Control Management System"
Const sFile As String = "Pest Control Reporting Tool.xls"

On Error Resume Next
Set WB = Workbooks(sFile)
On Error GoTo 0

If WB Is Nothing Then
myFolder = CurDir
myDrive = CurDir

ChDrive sFolder
ChDir sFolder
Set WB = Workbooks.Open( _
Filename:=sFolder _
& Application.PathSeparator _
& sFile)
ChDrive myFolder
ChDir myFolder
Else
WB.Activate
End If
End Sub
'<<==========


---
Regards.
Norman