I have created a spreadsheet (Excel 2000) that allows for user input (with
drop downs and option buttons).

My concern is that a user could save this sheet to their desktop, rather
than downloading the sheet from a centralized Lotus Notes database. If the
user does this, they won't have the most current version of the sheet.

Would a userform provide the capability to stop a user from saving the sheet
and missing out on updates? NOTE in the spreadsheet, I have already disabled
the Save and Save As functions with VBA code, but if they don't enable the
Macros they can still save it to their C drive.

I also played with the idea of saving it as an Add-In, but the spreadsheet
didn't transfer over correctly.

I would appreciate any help in resolving these challenges.

Thanks,

Brian

Hi,

If you have controls on a sheet, I can't imagine why a user would try to copy it to their hard drive. It would be the same if you
had a userform. I guess the thing you are trying to avoid at this point, since you have disabled the save capability, is users
dragging the sheets to a different workbook or copying the workbook. You could do 2 things that I believe would help: 1st, you could
hide the sheet tabs and protect the sheet (and the workbook). This will keep folks from dragging individual sheets away. Also, you
could add code that checks to see where it is saved, and if it is in a "C" drive, then kick up a messagebox warning the user what he
is missing. You could go further and make the code self destruct the copied file when it is opened, perhaps after the user checks
"OK" on the messagebox. That should do it. Of course, a determined user who knows VBA well and also knows how to hack past the poor
password security in Excel will always be able to defeat your efforts. Additionally, a smart user could turn off macros before
opening, then open the VBA editor and delete the self destruct (and any other) code he/she wants to. I would suggest adding another
password to the VBA project, just to make things a little more of a hassle to tinker with.

HTH
--
RMC,CPA


"Brian C" wrote in message ...
I have created a spreadsheet (Excel 2000) that allows for user input (with
drop downs and option buttons).

My concern is that a user could save this sheet to their desktop, rather
than downloading the sheet from a centralized Lotus Notes database. If the
user does this, they won't have the most current version of the sheet.

Would a userform provide the capability to stop a user from saving the sheet
and missing out on updates? NOTE in the spreadsheet, I have already disabled
the Save and Save As functions with VBA code, but if they don't enable the
Macros they can still save it to their C drive.

I also played with the idea of saving it as an Add-In, but the spreadsheet
didn't transfer over correctly.

I would appreciate any help in resolving these challenges.

Thanks,

Brian

"R. Choate" wrote:

Hi,

If you have controls on a sheet, I can't imagine why a user would try to copy it to their hard drive. It would be the same if you
had a userform. I guess the thing you are trying to avoid at this point, since you have disabled the save capability, is users
dragging the sheets to a different workbook or copying the workbook. You could do 2 things that I believe would help: 1st, you could
hide the sheet tabs and protect the sheet (and the workbook). This will keep folks from dragging individual sheets away. Also, you
could add code that checks to see where it is saved, and if it is in a "C" drive, then kick up a messagebox warning the user what he
is missing. You could go further and make the code self destruct the copied file when it is opened, perhaps after the user checks
"OK" on the messagebox. That should do it. Of course, a determined user who knows VBA well and also knows how to hack past the poor
password security in Excel will always be able to defeat your efforts. Additionally, a smart user could turn off macros before
opening, then open the VBA editor and delete the self destruct (and any other) code he/she wants to. I would suggest adding another
password to the VBA project, just to make things a little more of a hassle to tinker with.

HTH
--
RMC,CPA


"Brian C" wrote in message ...
I have created a spreadsheet (Excel 2000) that allows for user input (with
drop downs and option buttons).

My concern is that a user could save this sheet to their desktop, rather
than downloading the sheet from a centralized Lotus Notes database. If the
user does this, they won't have the most current version of the sheet.

Would a userform provide the capability to stop a user from saving the sheet
and missing out on updates? NOTE in the spreadsheet, I have already disabled
the Save and Save As functions with VBA code, but if they don't enable the
Macros they can still save it to their C drive.

I also played with the idea of saving it as an Add-In, but the spreadsheet
didn't transfer over correctly.

I would appreciate any help in resolving these challenges.

Thanks,

Brian

Hi HTH,

Thanks for the hints. These are great ideas. I've already protected and
hidden the sheets, but the problem is if the macros are not enabled, then
they can save it to their c drive and then enable the macros from their c
drive. Could you help me with the coding to:
Run a macro that deletes the sheets if it detects that the spreadsheet was
loaded from the C or D drive rather than downloaded from Lotus Notes

I won't be able to tell the user not to save to the c drive as you
suggested, because the only way they can save it is if the macros are
disabled, and then the message box wouldn't work either.

Thanks for your help!

Brian C

Brian,

Your comment: "I won't be able to tell the user not to save to the c drive as you suggested, because the only way they can save it
is if the macros are disabled, and then the message box wouldn't work either." isn't exactly correct. All a user has to do is use
windows explorer and make a copy of the file while the file is closed, as long as the user knows where the Excel file is located.
That information is also very easy to find without code. A simple formula will yield the path and name.

Of course, the user would eventually have to turn on the macros in order to use any of the dropdowns or other controls. It is at
this point that the self-destruct code would fire. This is, again, assuming the user is not very knowledgeable about VBA. Otherwise,
the user will likely be able to defeat whatever you put in place. Just the same, using lots of passwords, especially putting a
password on the VBA code, would be a worthwhile step.

The self destruct code can delete all but one sheet in the file. There are several ways to do it, but here is a simple version that
does not try to loop through sheets and delete them as you go. Although you cannot delete the last sheet, you can add some more code
to clear everything on the sheet and leave it blank. That will just about render it useless. This code checks to see what the file
path is, and if it begins with drive "C", then it will delete a sheet (I only used a 2-sheet file). You can add the looping code I
discussed for additional sheet deletion, and add further code to clear off the remaining sheet.

Sub WhereAmI()

Dim MyDrive As String
Dim MyPath As String

Application.DisplayAlerts = False

MyPath = Application.Path

MyDrive = Left(MyPath, 1)

If MyDrive = "C" Then
Sheets("sheet2").Delete
End If

Application.DisplayAlerts = True

End Sub

HTH

--
RMC,CPA


"Brian C" wrote in message ...


"R. Choate" wrote:

Hi,

If you have controls on a sheet, I can't imagine why a user would try to copy it to their hard drive. It would be the same if you
had a userform. I guess the thing you are trying to avoid at this point, since you have disabled the save capability, is users
dragging the sheets to a different workbook or copying the workbook. You could do 2 things that I believe would help: 1st, you
could
hide the sheet tabs and protect the sheet (and the workbook). This will keep folks from dragging individual sheets away. Also, you
could add code that checks to see where it is saved, and if it is in a "C" drive, then kick up a messagebox warning the user what
he
is missing. You could go further and make the code self destruct the copied file when it is opened, perhaps after the user checks
"OK" on the messagebox. That should do it. Of course, a determined user who knows VBA well and also knows how to hack past the
poor
password security in Excel will always be able to defeat your efforts. Additionally, a smart user could turn off macros before
opening, then open the VBA editor and delete the self destruct (and any other) code he/she wants to. I would suggest adding
another
password to the VBA project, just to make things a little more of a hassle to tinker with.

HTH
--
RMC,CPA


"Brian C" wrote in message ...
I have created a spreadsheet (Excel 2000) that allows for user input (with
drop downs and option buttons).

My concern is that a user could save this sheet to their desktop, rather
than downloading the sheet from a centralized Lotus Notes database. If the
user does this, they won't have the most current version of the sheet.

Would a userform provide the capability to stop a user from saving the sheet
and missing out on updates? NOTE in the spreadsheet, I have already disabled
the Save and Save As functions with VBA code, but if they don't enable the
Macros they can still save it to their C drive.

I also played with the idea of saving it as an Add-In, but the spreadsheet
didn't transfer over correctly.

I would appreciate any help in resolving these challenges.

Thanks,

Brian

Hi HTH,

Thanks for the hints. These are great ideas. I've already protected and
hidden the sheets, but the problem is if the macros are not enabled, then
they can save it to their c drive and then enable the macros from their c
drive. Could you help me with the coding to:
Run a macro that deletes the sheets if it detects that the spreadsheet was
loaded from the C or D drive rather than downloaded from Lotus Notes

I won't be able to tell the user not to save to the c drive as you
suggested, because the only way they can save it is if the macros are
disabled, and then the message box wouldn't work either.

Thanks for your help!

Brian C

If you have a userform that should save as and add-in

"Brian C" wrote:

I have created a spreadsheet (Excel 2000) that allows for user input (with
drop downs and option buttons).

My concern is that a user could save this sheet to their desktop, rather
than downloading the sheet from a centralized Lotus Notes database. If the
user does this, they won't have the most current version of the sheet.

Would a userform provide the capability to stop a user from saving the sheet
and missing out on updates? NOTE in the spreadsheet, I have already disabled
the Save and Save As functions with VBA code, but if they don't enable the
Macros they can still save it to their C drive.

I also played with the idea of saving it as an Add-In, but the spreadsheet
didn't transfer over correctly.

I would appreciate any help in resolving these challenges.

Thanks,

Brian