I have an Excel workbook on a netwrok drive that has macros which create
record numbers and copy each record and its data to a specific worksheet in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct file
like sheets(G:\\Workbook "records").select If the user were not in the
correct network drive version of the workbook, then the macro could not find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

Workbooks("MyWorkbook.xls").worksheets("records"). select

HTH
--
AP

"frendabrenda1" a écrit dans le
message de ...
I have an Excel workbook on a netwrok drive that has macros which create
record numbers and copy each record and its data to a specific worksheet
in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct
file
like sheets(G:\\Workbook "records").select If the user were not in the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

"frendabrenda1" wrote in message
...

If the user were not in the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Just make sure you program the exception handling, rather than just let an
"error happen". Know what I mean?

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub

"frendabrenda1" a écrit dans le
message de ...
I have an Excel workbook on a netwrok drive that has macros which create
record numbers and copy each record and its data to a specific worksheet
in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct
file
like sheets(G:\\Workbook "records").select If the user were not in the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

"Ardus Petus" wrote in message
...
Doug is right...

Well, that's it for me, until next year. :-)

I am not sure this answers my real problem. If a user has copied the
original file to their hard drive, then both the correct network file and the
copied hard drive file are called "MyWorkbook.xls". I am trying to find a
way that an error would occur if the user were not on the network drive
version of "MyWorkbook.xls"

Any thougths?

Thank you so much for thinking about this.

Have a GREAT day.

brenda

"Ardus Petus" wrote:

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub

"frendabrenda1" a écrit dans le
message de ...
I have an Excel workbook on a netwrok drive that has macros which create
record numbers and copy each record and its data to a specific worksheet
in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct
file
like sheets(G:\\Workbook "records").select If the user were not in the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

Brenda, are users supposed to store anything at all on their local drives?
There are a number of ways to address this issue, some from within Excel,
and some via some Windows tricks.


"frendabrenda1" wrote in message
...
I am not sure this answers my real problem. If a user has copied the
original file to their hard drive, then both the correct network file and
the
copied hard drive file are called "MyWorkbook.xls". I am trying to find a
way that an error would occur if the user were not on the network drive
version of "MyWorkbook.xls"

Any thougths?

Thank you so much for thinking about this.

Have a GREAT day.

brenda

"Ardus Petus" wrote:

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub

"frendabrenda1" a écrit dans le
message de ...
I have an Excel workbook on a netwrok drive that has macros which
create
record numbers and copy each record and its data to a specific
worksheet
in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct
file
like sheets(G:\\Workbook "records").select If the user were not in
the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed
to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

No, there is nothing to be stored on the user's hrd drive.

Thanks again!

brenda

"Doug Kanter" wrote:

Brenda, are users supposed to store anything at all on their local drives?
There are a number of ways to address this issue, some from within Excel,
and some via some Windows tricks.


"frendabrenda1" wrote in message
...
I am not sure this answers my real problem. If a user has copied the
original file to their hard drive, then both the correct network file and
the
copied hard drive file are called "MyWorkbook.xls". I am trying to find a
way that an error would occur if the user were not on the network drive
version of "MyWorkbook.xls"

Any thougths?

Thank you so much for thinking about this.

Have a GREAT day.

brenda

"Ardus Petus" wrote:

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub

"frendabrenda1" a écrit dans le
message de ...
I have an Excel workbook on a netwrok drive that has macros which
create
record numbers and copy each record and its data to a specific
worksheet
in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct
file
like sheets(G:\\Workbook "records").select If the user were not in
the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed
to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda

In that case, you'd be better off having your network administrator prevent
this from happening via mapping and other tricks (most of which I'm not
familiar with). It was done at my home office. When users see their "My
Documents" folders, they're actually in designated folders on the server.

"frendabrenda1" wrote in message
...
No, there is nothing to be stored on the user's hrd drive.

Thanks again!

brenda

"Doug Kanter" wrote:

Brenda, are users supposed to store anything at all on their local
drives?
There are a number of ways to address this issue, some from within Excel,
and some via some Windows tricks.


"frendabrenda1" wrote in
message
...
I am not sure this answers my real problem. If a user has copied the
original file to their hard drive, then both the correct network file
and
the
copied hard drive file are called "MyWorkbook.xls". I am trying to
find a
way that an error would occur if the user were not on the network drive
version of "MyWorkbook.xls"

Any thougths?

Thank you so much for thinking about this.

Have a GREAT day.

brenda

"Ardus Petus" wrote:

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub

"frendabrenda1" a écrit dans
le
message de ...
I have an Excel workbook on a netwrok drive that has macros which
create
record numbers and copy each record and its data to a specific
worksheet
in
the file. I am having problems with users copying the file or
creating
shortcuts and ending up with duplicate copies of the file which
pretty
much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the
correct
file
like sheets(G:\\Workbook "records").select If the user were not in
the
correct network drive version of the workbook, then the macro could
not
find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to
stop
copying of the file in any way or set up a file that can be
distributed
to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda