Run-time error when path parameter changed

[tinknocker]

I am, at best, a novice w/ VBA so please bear w/ me.
Have a workbook which displays a userform w/ multiple Command Buttons.
Clicking a button hides the userform and opens a subordinate workbook
containing data. Once the data is viewed the user has a choice of (2)
buttons in the subordinate workbook. "Exit All" closes all open
workbooks. "Continue" closes the workbook being viewed and activates &
shows the workbook w/ the userform allowing the user to make another
selection.

Further, the workbooks and code all reside on my PC. A shortcut icon
on the PC's of my (2) bosses accesses the userform workbook and
subsequently the workbooks w/ the data.

My initial code pointing to these workbooks was C:\........ Upon
adding the shortcuts to the other PCs I realized that I needed to
change the path to \\RG\....... (RG is my computer on the network).
That works for the other (2) PCs. But now if I run the code on my
machine when I click the "Continue" button in the subordinate
workbooks I get a run-time error 1004. "A document w/ the same name is
already open........".

Thanks,
Roger

 

[Office_Novice]

Try using an If Then... Statement and some code to identify your computer.
Somthing like this maybe:

Option Explicit

Sub Somthing()
Dim objShell As Object
Dim regActiveComputer As String
Dim ActiveComputer As String
Dim LocalPath As String
Dim NetworkPath As String

regActiveComputer =
"HKLM\SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName"
Set objShell = CreateObject("WScript.Shell")
ActiveComputer = objShell.RegRead(regActiveComputer)
LocalPath = "C:\Excel Workbooks\Book1.xls" 'Change this
NetworkPath = "\\RG\Excel Workbooks\Book1.xls" 'and this

If "YourComputerName" = ActiveComputer Then 'Change this to mach your
computer name
' i.e RG
Workbooks.Open (LocalPath)
Else
Workbooks.Open (NetworkPath)

Debug.Print ActiveComputer
End If
End Sub

 

[tinknocker]

Try using an If Then... Statement and some code to identify your computer.
Somthing like this maybe:

Option Explicit

Sub Somthing()
Dim objShell As Object
Dim regActiveComputer As String
Dim ActiveComputer As String
Dim LocalPath As String
Dim NetworkPath As String

regActiveComputer =
"HKLM\SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName"
Set objShell = CreateObject("WScript.Shell")
ActiveComputer = objShell.RegRead(regActiveComputer)
LocalPath = "C:\Excel Workbooks\Book1.xls" 'Change this
NetworkPath = "\\RG\Excel Workbooks\Book1.xls" 'and this

If "YourComputerName" = ActiveComputer Then 'Change this to mach your
computer name
' i.e RG
Workbooks.Open (LocalPath)
Else
Workbooks.Open (NetworkPath)

Debug.Print ActiveComputer
End If
End Sub

Thanks for the response, sorry for the delay in responding. Got very
busy in the office Friday afternoon and family from out of town all
weekend.

Should do the trick, now to find the time to implement.

Roger