PDA

View Full Version : [SOLVED] Simple Macro Help



kwesmc
12-09-2017, 01:47 PM
Hi,
I need help with a simple macro I wrote. What I am trying to accomplish is to make sure any sheet a user copies to the workbook with any random worksheet name, it stays protected to make sure the code jumps any protected cell to the unprotected cells for input. Below is the code
Thanks in advance,
Ken Mc



Private Sub Workbook_Open()
Dim WB As Workbook
Set WB = ThisWorkbook
WB.Protect
WB.EnableSelection = xlUnlockedCells
End Sub

SamT
12-09-2017, 01:58 PM
What I am trying to accomplish is to make sure any sheet a user copies to the workbook with any random worksheet name, it stays protected to make sure the code [Unknown Word] any protected cell to the unprotected cells for input

kwesmc
12-09-2017, 02:10 PM
Sam,
Did you mean to reply to my post? I didn't see any text.
Thanks,
Ken Mc

SamT
12-09-2017, 04:27 PM
No text at all? That's strange. I used the Quote Tags to paste something from your post.

See Below:

You said: "What I am trying to accomplish is to make sure any sheet a user copies to the workbook with any random worksheet name, it stays protected to make sure the code [Unknown Word] any protected cell to the unprotected cells for input"

kwesmc
12-09-2017, 05:44 PM
Well, I have no idea why your code didn't come through. I didn't understand what you were saying there, but thanks for trying.
Ken Mc

SamT
12-09-2017, 07:54 PM
All I have said so far, in so many words, is "I don't understand what you want."

"Jump" is a nonsense word in the context of VBA. I'm not guessing what it means.

Paul_Hossler
12-10-2017, 10:30 AM
if you're trying to protect all sheets, then something like this

NB: this will only protect sheets that are in the workbook when it's opened, not if the user adds some interactively





Option Explicit

Private Sub Workbook_Open()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
WS.Protect
WS.EnableSelection = xlUnlockedCells
Next

End Sub

kwesmc
12-10-2017, 02:32 PM
Thanks Paul. That did the trick. I will mark solved.
I also appreciate you not being so judgmental like some on this forum. I will add to your reputation.
Regards,
Ken Mc

SamT
12-10-2017, 04:27 PM
Paul,

Good guess.

Ken,

I bow to your judgement.