Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

protection help 1

Status
Not open for further replies.
smiley0q0

smiley0q0

Technical User
Feb 27, 2001
356
US
ok, at my old job there was a guy that made an excel spreadsheet that when it was protected you could not even click on the protected cells, i would like to do the same. do i need to do it in macro form or can i have the sheet automatically open with this feature. the sheets that he made you had to click the "enable macros" button or else it wouldn't work. is there anyway to make it work even if they click disable macro's?

thanks,
Smiley (-:
 
Sort by date Sort by votes
About locking cells:

1. Default "property" is locked. Format-Cells-Protection.
2. To activate locked property, you must protect worksheet.

so:

By default, protecting the worksheet locks all cells.

but in reality:

1. If you want SOME cells locked, hit ctrl-a, remove lock, and then set locking only on desired cells. Protect worksheet.
2. If you want only some cells UNlocked, select those cells and unlock them. Protect the worksheet.

YOU DO NOT NEED A MACRO TO LOCK CELLS OR PROTECT WORKSHEETS.

He probably did it that way for some other purpose.

techsupportgirl@home.com
Brainbench MVP for Microsoft Word at
 
Upvote 0 Downvote
yea i know how to lock and unlock them, but he had it so when they were locked you couldn't even click on them, when you tried, it just went over to the next unlocked cell. even with the arrow keys, they just skipped over the locked cells. i have a spreadsheet that has this if you would like to see it.
 
Upvote 0 Downvote
Okay. I see. I recall this, but cannot find the code. Something about a Worksheet change on click of (range of almost all cells) that automatically does a go-to to specified cell. Suppose your entries are expenses that need to go in C5:C10

So your on-click range would be A1:IV4,A5:B65536,D5:IV65536,C11:C65536

something like that. The coders should come in and tell you the code you'd need to do it. I'll be watching. If they don't come through soon, I'll see if I can get the code again.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word at
 
Upvote 0 Downvote
i don't think so... this is all the code he has in his macro, unless he's got some hidden thing i don't know about.


Private Sub CmdClear_Click()

Call Clear

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
EnableSelection = xlUnlockedCells
End Sub

Sub Clear()

Application.ScreenUpdating = False
Dim Cell As Range
Set x = Range("E13:BA52")
For Each Cell In Range("E13:BA52")
Cell.Activate
If Cell.Locked Then
Else
ActiveCell.ClearContents
End If
Next
Application.ScreenUpdating = True

End Sub


i don't need the clear thing, i was just wondering if there was a way to do this even if some hit the disable macro button...
 
Upvote 0 Downvote
I "paid" for this one, smiley. But that's okay. Let's see what you think:

Ok, this is the trick:

1. Password-protect your workbook so that noone can open it.
2. Create a new workbook "openme.xls" that has an auto-open / workbook_open macro that opens the password-protected
workbook and then unloads itself.

Now, nobody can open the workbook directly. They must open it by allowing the auto-open macro of "openme.xls"
to run.

Get the idea?


Now, prior to this, he suggested creating it as an addin, which doesn't give macro message, and putting file in XLSTART, ditto. Let me know if you want the exact reply I got from him--but it wasn't much more than that.

techsupportgirl@home.com
Brainbench MVP for Microsoft Word at
 
Upvote 0 Downvote
yea i think i get the idea... hold on let me see if i can get it to work... brb
 
Upvote 0 Downvote
sorry, i got called into an unexpected meeting..., yep it works... thank you very much for your help... sorry you had to pay for it.

no, i know you are not a blonde, i've seen and learned a lot from your other posts... LOL

Smiley :-D
 
Upvote 0 Downvote
Oh. My mind was on a different tack entirely. There is the word "dream" is thy name ya know ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
304
SkipVought
S
Tropometer
  • Locked
  • Question
Help with dates
Replies
1
Views
196
SkipVought
S
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
175
TomSalvato
TomSalvato
cneill
  • Locked
  • Question
Hi, I need some help with Pre an
Replies
7
Views
176
SkipVought
S
zinderellie
  • Locked
  • Question
Excel 365- Formula Help - When A1=X and B1=Y, Return C1
Replies
8
Views
204
dgillz
dgillz

Part and Inventory Search

Sponsor

Back
Top