Run macro on passw protected TAB

[ndendrinos]

Hello to all and Happy New Year
Read all kinds of ways to unprotect a sheet that's protected, run the macro and reprotect the sheet.
None work here as it is the tab that's protected (need this so that no one can see the sheet let alone edit it)
Is there a way ?
Thank you

[XLGibbs]

In the code window, you can set the visible property of the sheet to xlVeryHidden. Which would make it inaccessible from the main workbook at all times.

Also, you can just set Sheets("Sheet2").Visible = False at any time to make in hidden (but can be unhid, whereas xlVeryHidden as a property in the Code window's property list of that sheet will not show up)

[lucas]

I don't think you need to unprotect it if you don't use a selection:

Sub Macro1()
Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Range("A1")
End Sub

Hope I understand the problem.......

[XLGibbs]

Lucas, if the sheet is protected, It will still error out. The protection applies even to code changes as I have found...unless I did something wrong when I was working on one that had to protect/unprotect

Most often, if I need to use some type of protection on sheets, I just set up two little subroutines that unprotect/protect sheets and call those from the modules that need to make changes...

[lucas]

Hi Pete,
I could definatly be off the mark here but I just downloaded the example and the macro he was using required a select.....please download it and try my code to see if I'm out of school on this.

[XLGibbs]

True, in this case the sheet is not technically protected which is why it works. It is simply prompting the user for a password when the sheet2 becomes activated or selected.

In this case, modifying the code to :
Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")


Would work fine, and not prompt for the password when the macro fires.

I was referring to Sheet Protection using Sheets("Sheet2").Protect "password"

In this case, all he is doing is requiring a password to change sheets, but there are many ways to keep the sheet from being viewed.

If the sheet had actual protection, instead of just macro induced password input box...it would need to be unprotected before making changes.

[XLGibbs]

Hello to all and Happy New Year
Read all kinds of ways to unprotect a sheet that's protected, run the macro and reprotect the sheet.
None work here as it is the tab that's protected (need this so that no one can see the sheet let alone edit it)
Is there a way ?
Thank you

See Lucas's response here...your code uses Sheet(x).Select etc...when it can be simplified.

Your sheet is not "really" protected, since the prompt for password is issued in the Sheet Selection Change code..

Lucas's response should solve the issue I believe

[ndendrinos]

I have to stop doing this. I post a simple tset file and end up wasting your valuable time. Sorry.
Here is the real code I try to run and I'm not sure Lucas's solution will work here.
As for the remark that sheet2 is "protected" I understand what you say XLGibbs But doing it this way has the benefit of not letting the user see the sheet unless the password is entered.
In this code : Sheets("753m Schedule") = Sheet1 and
Sheets("Weekly Schedule Records") =Sheet2

Sub ToRecords()

Application.ScreenUpdating = False



Range("A2:O23").Select
Selection.Copy

Sheets("Weekly Schedule Records").Select

Range("A65536").End(xlUp).Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Range("A2,D:D,F:F,H:H,J:J,L:L").Select
Range("L1").Activate
Selection.ColumnWidth = 3
Columns("A:A").ColumnWidth = 11.43
Columns("A:A").ColumnWidth = 16.29
Columns("O:O").ColumnWidth = 30.43
[A1].Select

Sheets("753m Schedule").Select
[A1].Select



Application.ScreenUpdating = True
End Sub

[ndendrinos]

Here is the revised code based on upur concept that works with the initial attachment but does not work with the true code... maybe I'm almost there?
Sub ToRecords()

Application.ScreenUpdating = False

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0) = Sheets("753m Schedule").Range("A2:O23")

Range("A2,D:D,F:F,H:H,J:J,L:L").Select
Range("L1").Activate
Selection.ColumnWidth = 3
Columns("A:A").ColumnWidth = 11.43
Columns("A:A").ColumnWidth = 16.29
Columns("O:O").ColumnWidth = 30.43
[A1].Select

Sheets("753m Schedule").Select
[A1].Select



Application.ScreenUpdating = True
End Sub

[XLGibbs]

It's all good...

YOu need to resize the destination (the left side) as below...to match the dimensions of the source:

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15).Value _
= Sheets("753m Schedule").Range("A2:O23").Value

[XLGibbs]

Sub ToRecords()

Application.ScreenUpdating = False

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15).Value _
= Sheets("753m Schedule").Range("A2:O23").Value

Range("A2,D:D,F:F,H:H,J:J,L:L").ColumnWidth = 3
Columns("A:A").ColumnWidth = 16.29
Columns("O:O").ColumnWidth = 30.43

Sheets("753m Schedule").[A1].Activate


Application.ScreenUpdating = True
End Sub

It looks like some unecessary .selects were in that code as well...also you format the column Width of A:A twice, so see the above and see if that works (includes the above resize adjustment)

[ndendrinos]

Thank you XLGibbs, The code works but I lose the formats from the source sheet ... is there a simple edit to your code that wouls solve this short of redoing all the formats by macro (at the end od the code)

[XLGibbs]

Just remove the .Value from the line that I edited and it should preserve the formatting as well.

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15) _
= Sheets("753m Schedule").Range("A2:O23")

[ndendrinos]

Thank you again,
Had to do a small change to get it going from:

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15) _
= Sheets("753m Schedule").Range("A2:O23") to

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15) _
= Sheets("753m Schedule").Range("A2:O23").Value


But I still loose the formats from the originating sheet ...there's no equivalent to "pastespecial/all" that can be introduced here then?

[XLGibbs]

Thank you again,
Had to do a small change to get it going from:

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15) _
= Sheets("753m Schedule").Range("A2:O23") to

Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15) _
= Sheets("753m Schedule").Range("A2:O23").Value


But I still loose the formats from the originating sheet ...there's no equivalent to "pastespecial/all" that can be introduced here then?

This will do it: (Or you could "pre-Format" the 753m Sheet manually and do nothing)
<Code removed by XLGibbs for gross negligence in it's content ! LOL

Or another alternative which is a little fancier:


Dim rngFrom as Range, rngTo as Range

Set rngFrom = Sheets("753m Schedule").Range("A2:023")
Set rngTO = Sheets("Weekly Schedule Records").Range("A65536").End(xlup).Offset(2,0).Resize(22,15)

rngFrom.Copy rngTO


Application.CutCopyMode = False

[ndendrinos]

XLGibbs.
While you were working on a solution I was doing the manual formatting you suggest.
Tried both your last suggestions and have difficulties with them.
I will continue to work on it and post the results.
Until then, thank you again

[XLGibbs]

Oops...the first of the last two suggestions won't work right...so don't do that one..

[ndendrinos]

OK I'll work on the second one then until I get it going and will post back soon this week.
Got to watch a movie with my wife or she'll KILL me.
Regards,
Nick

[lucas]

Hello Nick and Pete,
Sorry I missed the last part of this thread but it looks like Pete has you on the right track...New years dinner called me away.

[ndendrinos]

Hello Pete and Lucas
Failed again.... got it going by protecting the macro itself with a passw but still the user can overcome this easily by unhiding the sheet ... I really need to do it the way I started here. I tried this last suggestion by Pete and get:
Run Time error 1004 / Application defined or object defined error ... first time for me so not much I can do here ... will Google and see. Anyhow this is where I'm at now and the line:
Set rngFrom = Sheets("753m Schedule").Range("A2:023") is yellowed.
PS: the formats are crucial here as I use colours to trim the result and prepare the data for my pivot table and other.

Sub ToRecords()

Application.ScreenUpdating = False

Dim rngFrom As Range, rngTo As Range

Set rngFrom = Sheets("753m Schedule").Range("A2:023")
Set rngTo = Sheets("Weekly Schedule Records").Range("A65536").End(xlUp).Offset(2, 0).Resize(22, 15)

rngFrom.Copy rngTo


Application.CutCopyMode = False

Range("A2,D:D,F:F,H:H,J:J,L:L").ColumnWidth = 3
Columns("A:A").ColumnWidth = 16.29
Columns("O:O").ColumnWidth = 30.43
With ActiveSheet
Rows("2:22").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(2, 0).Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End With






Sheets("753m Schedule").[A1].Activate


Application.ScreenUpdating = True
End Sub

[XLGibbs]

It looks like the range in the line that errored as Zero23 instead of O23

You can see the difference here

023 'zero
O23 'letter


As far as the protection, even with the password macro, the users could find a way to unhide the sheet.

Inside the macro that hides them you can potentially disable those menu items...I can't recall the exact code to disable the toolbar items, but I think I can find it...

Have to go to work, will check back after.

[lucas]

Here is an alternative. Try this in your ThisWorkbook Module. User can only view Sheet1:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Worksheets(1).Activate
End Sub