Sleeper: couple of Problems

[TriMacro]

First - I am trying to "idiot" proof my workbook so that it prevents individuals from copying over the existing workbook. (I want them to always have the original available to them). I've semi-accomplished this by adding a "Save As" button but that still doesn't prevent someone from hitting File:Save and overwrting the original document. This workbook is not available on a network and will reside on individual computers (for an association). How can I prevent this from happening?

Second - Same workbook, I have used drop down-boxes to list certain items but when I protect the sheet, it won't allow me to change the selections in the drop down boxes. I've tried to remove the protection in the formatting of the individual boxes but it doesn't help.

Third - What can I do to clear all of the cells and reset the drop-down boxes to a value of "1" after saving? (I will give them the option of reseting after utilizing the "Save As" button and creating a separate "Clear' button)

Lastly and most embarrassing - I was toying around in the beginning and added a calendar object that I can't get rid of and whenever the file opens, it opens to that object which is nowhere near the rest of the information. How do I get rid of it (no toolbar for it) and/or how do I utilize it's values if I keep it?

[Zack Barresse]

Hi,

Can you upload a zipped version of the file?

[Steiner]

About the first: You could set a write password for the original document to prevent accidental saving, but your user would have to live with the dialog asking them to enter the password or open the file protected. But to me this seems to be the safest way.

Other way: create the original as template and let the user only create new files basing on it.

About the 2nd problem: Have you remove the protection for the output cell? No need to remove it from the list range (the values that get into your list), but you must unprotect the cell where the result should go.

[TriMacro]

No, I haven't unprotected the destination cells - I was listing the information into column A and then drawing it back out using formulas and with column A hidden, I forgot to unlock it. It never dawned on me to create it as a template only - duh
!

I'm just getting back into Excel after 4 years of not using it. Thanks for the heads up!

Now, if someone could help me with getting rid of the calendar object I would be in great shape.

[Jacob Hilderbrand]

The Calendar Object is on the worksheet? Open the Control Toolbox, Press the button for Design Mode (It is a picture of a triangle, ruler, and pencil). Click on the object and press Delete.

[TriMacro]

As I mentioned - it's been a while. Wow!

I've figured out the coding to use the calendar object value so I'm adapting my workbook to use it.

Finally, can anyone tell me how me how to reset all of my Drop Downs to their initial position of '1' or xlfirst(?) and how do I reset all Check Boxes to a value of 'False' when clearing the worksheet.

[Jacob Hilderbrand]

Try this macro. I set the List Boxes to no selection. If you want the first item automatically selected then change the ListIndex to 0.

Option Explicit
 
Sub ResetControls()
Dim i As Long
For i = 1 To Sheet1.OLEObjects.Count
Select Case TypeName(Sheet1.OLEObjects(i).Object)
Case Is = "CheckBox"
Sheet1.OLEObjects(i).Object.Value = False
Case Is = "ListBox"
Sheet1.OLEObjects(i).Object.ListIndex = -1
End Select
Next
End Sub

[TriMacro]

I don't know what it did but it didn't reset the check Boxes or the Drop Down boxes and I tried several variables of the code. I guess I'm hopeless.

[Jacob Hilderbrand]

Can you attach the workbook?