Hi,

I'm trying to figure out how to write VBA code to identify the drive letter
that a spreadsheet (in Excel 2000) was loaded from, and then to delete a
worksheet if it was loaded from drive C or D.

The idea is to prevent users from saving a copy of the spreadsheet to their
local drive. I also want to create a text message that tells them they must
go to the central drive to use this sheet.

I'd appreciate any help.

Thanks,

Brian

sDrive = left(thisworkbook.Path,1)
if ucase(sDrive) = "C" or ucase(sDrive) = "D" then


of course if macros are disabled, this code won't run.
--

Regards,
Tom Ogilvy


"Brian C" wrote in message
...
Hi,

I'm trying to figure out how to write VBA code to identify the drive
letter
that a spreadsheet (in Excel 2000) was loaded from, and then to delete a
worksheet if it was loaded from drive C or D.

The idea is to prevent users from saving a copy of the spreadsheet to
their
local drive. I also want to create a text message that tells them they
must
go to the central drive to use this sheet.

I'd appreciate any help.

Thanks,

Brian

ActiveWorkbook.Path will tell you what you want to know. Users could just
disable macros to get around your plan though.

--
Jim
"Brian C" wrote in message
...
| Hi,
|
| I'm trying to figure out how to write VBA code to identify the drive
letter
| that a spreadsheet (in Excel 2000) was loaded from, and then to delete a
| worksheet if it was loaded from drive C or D.
|
| The idea is to prevent users from saving a copy of the spreadsheet to
their
| local drive. I also want to create a text message that tells them they
must
| go to the central drive to use this sheet.
|
| I'd appreciate any help.
|
| Thanks,
|
| Brian

You might also discourage the practice by preventing them performing a
SaveAs in the first place.

If you by place something like the below in the ThisWorkbook module for
example.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then
MsgBox "No!"
Cancel = True
End If
End Sub

Note, this is assuming you're tackling the user who naively tries to
save elsewhere. If someone is determined they can disable macros (as Jim
Rech noted) or of course copy the file using explorer.

HTH,
Gareth


Brian C wrote:
Hi,

I'm trying to figure out how to write VBA code to identify the drive letter
that a spreadsheet (in Excel 2000) was loaded from, and then to delete a
worksheet if it was loaded from drive C or D.

The idea is to prevent users from saving a copy of the spreadsheet to their
local drive. I also want to create a text message that tells them they must
go to the central drive to use this sheet.

I'd appreciate any help.

Thanks,

Brian

Thanks Guys!
I actually already disabled the save/save as, and made it so that the
sheets are veryhidden and tells the user that the workbook can't be used if
macros are disabled. So, the last piece is to prevent someone from disabling
the macros as they launch the sheet, and then saving it on their c drive. If
they do that and load from C, it will give message "Must Enable Macros" and
the other sheets will be veryhidden.

If they enable macros, then I need this macro to detect the launch drive,
and if it's C or D, then delete the "Input" and "Calculations" sheets so they
can't use. Then create a message box that tells them they have to launch it
from the posted location. I've been working on the code, but can't get it
quite right. I'm pretty new to VBA.

Brian,

To the best of my knowledge if a user does not enable macros they can do
just about anything they want (depending on their skill level).

The closest I can come to preventing this is to make an add-in with all the
code. Than have the user launch the workbook from the add-in. I have also
heard of using DLL's but am not familiar with that approach.

Bottom line is that Excel is not really tamper proof.

But when you can control the workbook with code - you can pretty much do
just about anything.

In code
MsgBox Application.ActiveWorkbook.FullName

returns the path and file name of the workbook

DriveLetter = Left(Application.ActiveWorkbook.FullName,1)

As you are aware - leave a sheet with the message and hiding all the other
sheets on Workbook Close is the best you can achieve. Than on Workbook Open
you can hide the first and show the others. These codes belong in the
ThisWorkbook module. But only work if macros are enabled.

You can even set up code to delete sheets.

Post back if you need more specific code and other info.
--
steveB

Remove "AYN" from email to respond
"Brian C" wrote in message
...
Thanks Guys!
I actually already disabled the save/save as, and made it so that the
sheets are veryhidden and tells the user that the workbook can't be used
if
macros are disabled. So, the last piece is to prevent someone from
disabling
the macros as they launch the sheet, and then saving it on their c drive.
If
they do that and load from C, it will give message "Must Enable Macros"
and
the other sheets will be veryhidden.

If they enable macros, then I need this macro to detect the launch drive,
and if it's C or D, then delete the "Input" and "Calculations" sheets so
they
can't use. Then create a message box that tells them they have to launch
it
from the posted location. I've been working on the code, but can't get it
quite right. I'm pretty new to VBA.

Hi Steve,

Thanks for your help. You're right that an experienced user could overcome
whatever I do, but then I would hope that if they're that knowledgeable they
will appreciate the fact that it is designed to avoid them using old
information. We'll see.

Here's what I'm trying to get to work. I want to unprotect 2 sheets, and
then delete them if they were loaded from the C or D drive. I think I have
the drive ID part down and the Unprotect down, but I can't get the delete to
work.

The code I'm using for that part is:
sDrive = Left(ThisWorkbook.Path, 1)
If UCase(sDrive) = "C" Or UCase(sDrive) = "D" Then
Application.DisplayAlerts = False
Sheets(5).Visible = True
Sheets(1).Unprotect
Sheets(2).Unprotect
Sheets(1).Delete
Sheets(2).Delete
MsgBox ("This Application Must be Launched from the RETAIL TOOLBOX")
End If
Application.DisplayAlerts = True

Brian,

Glad to be of any help...

You fell into an Excel Trap - when you delete rows, columns, sheets etc -
deleting # 1 makes # 2 a new # 1 (if you understand that - you have it
made). So you work from the highest number to the lowest number. Delete #
2 and than delete # 1.

To make it even easier - use a loop

Dim sh as Integer

For sh = 2 to 1 step -1
With Sheets(sh)
.visible
.select ' not sure if you need this, but I sometimes get an
error if I don't use it.
.unprotect
.delete
End With
Next

It's nice to believe this
Thanks for your help. You're right that an experienced user could
overcome
whatever I do, but then I would hope that if they're that knowledgeable
they
will appreciate the fact that it is designed to avoid them using old
information. We'll see.
but there are a lot of people out their "Without Respect".

And it really isn't necessary to delete the sheets (unless that is what you
want).

You can do the check, flash a message to the user, set a pause, and than
auto-close the workbook.

ThisWorkbook.Close False

All you can really do is protect the Good User from making unintentional
errors. That's all that Excel protection really accomplishes.

And adding a password to the VBA protects the code (this one is harder to
break). Go to the VB Editor and go to properties - there is a tab to set a
password (note that it doesn't take effect until the workbook is saved and
closed).

happy Exceling...

--
steveB

Remove "AYN" from email to respond
"Brian C" wrote in message
...
Hi Steve,

Thanks for your help. You're right that an experienced user could
overcome
whatever I do, but then I would hope that if they're that knowledgeable
they
will appreciate the fact that it is designed to avoid them using old
information. We'll see.

Here's what I'm trying to get to work. I want to unprotect 2 sheets, and
then delete them if they were loaded from the C or D drive. I think I
have
the drive ID part down and the Unprotect down, but I can't get the delete
to
work.

The code I'm using for that part is:
sDrive = Left(ThisWorkbook.Path, 1)
If UCase(sDrive) = "C" Or UCase(sDrive) = "D" Then
Application.DisplayAlerts = False
Sheets(5).Visible = True
Sheets(1).Unprotect
Sheets(2).Unprotect
Sheets(1).Delete
Sheets(2).Delete
MsgBox ("This Application Must be Launched from the RETAIL
TOOLBOX")
End If
Application.DisplayAlerts = True