How do I unprotect/with password to run marco and protect
 

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

How do I unprotect/with password to run marco and protect
 

Try something like

ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="whatever"
' sort
ThisWorkbook.Worksheets("Sheet1").Protect Password:="whatever"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"J_Will" wrote in message
...
I have a locked sheet with columns unlocked for users to
populate. I have it
protected with a password so users can only update certain
cells. I wrote a
macro to sort data when entries are completed by clicking a
button. How can
I unprotect it with password, run macro, and protect it with
password after
sort is complete

How do I unprotect/with password to run marco and protect
 

option explicit
sub yourmacro()
activesheet.unprotect password:="topsecret"
'your sort code here
activesheet.protect password:="topsecret"
end sub



J_Will wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson

How do I unprotect/with password to run marco and protect
 

Try adapting these to meet your needs:

Private Sub Workbook_Open()
' Perform this when workbook opened.
' Macro recorded 06/15/00 by Lou Mullen
Worksheets("Recall").Select
Application.ActiveWorkbook.Protect Password:="YourCode"
range("A1").Select
ActiveSheet.Unprotect
Selection.Sort Key1:=range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect
Application.CommandBars("Recall Macros").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Perform steps on exit Macros
'Macro recorded 06/15/00 by Lou Mullen
Application.EnableCancelKey = xlDisabled
If Application.CommandBars("Recall Macros").Enabled = True Then
If Application.CommandBars("Recall Macros").Visible = True Then
Application.CommandBars("Recall Macros").Visible = False
End If
Application.ActiveWorkbook.Unprotect Password:="YourCode"
Application.CommandBars("Recall Macros").Delete
Else
Application.CommandBars("Recall Macros").Enabled = False
Application.ActiveWorkbook.Unprotect Password:=YourCode"
End If
End Sub

HTH Lou

"J_Will" wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

How do I unprotect/with password to run marco and protect
 

Password = "sandy crotch"

Can you put it into my macro, below, so I can cut and paste? So it
unprotecteds the sheet (sandy crotch) and password protects it (sandy crotch)

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


"Dave Peterson" wrote:

option explicit
sub yourmacro()
activesheet.unprotect password:="topsecret"
'your sort code here
activesheet.protect password:="topsecret"
end sub



J_Will wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson

How do I unprotect/with password to run marco and protect
 

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect password:="sandy crotch"
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending, _
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, password:="sandy crotch"
End Sub

J_Will wrote:

Password = "sandy crotch"

Can you put it into my macro, below, so I can cut and paste? So it
unprotecteds the sheet (sandy crotch) and password protects it (sandy crotch)

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

"Dave Peterson" wrote:

option explicit
sub yourmacro()
activesheet.unprotect password:="topsecret"
'your sort code here
activesheet.protect password:="topsecret"
end sub



J_Will wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson


--

Dave Peterson

How do I unprotect/with password to run marco and protect
 

I received a "syntax error". The last line (ActiveSheet.Protect. . .) was
highlighted blue.



"Dave Peterson" wrote:

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect password:="sandy crotch"
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending, _
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, password:="sandy crotch"
End Sub

J_Will wrote:

Password = "sandy crotch"

Can you put it into my macro, below, so I can cut and paste? So it
unprotecteds the sheet (sandy crotch) and password protects it (sandy crotch)

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

"Dave Peterson" wrote:

option explicit
sub yourmacro()
activesheet.unprotect password:="topsecret"
'your sort code here
activesheet.protect password:="topsecret"
end sub



J_Will wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson


--

Dave Peterson

How do I unprotect/with password to run marco and protect
 

Didn't work. Don't give up on me. I'll hold out until tonigh. If I can't
figure it out, I'll send with out my button.

"Rookie 1st class" wrote:

Try adapting these to meet your needs:

Private Sub Workbook_Open()
' Perform this when workbook opened.
' Macro recorded 06/15/00 by Lou Mullen
Worksheets("Recall").Select
Application.ActiveWorkbook.Protect Password:="YourCode"
range("A1").Select
ActiveSheet.Unprotect
Selection.Sort Key1:=range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect
Application.CommandBars("Recall Macros").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Perform steps on exit Macros
'Macro recorded 06/15/00 by Lou Mullen
Application.EnableCancelKey = xlDisabled
If Application.CommandBars("Recall Macros").Enabled = True Then
If Application.CommandBars("Recall Macros").Visible = True Then
Application.CommandBars("Recall Macros").Visible = False
End If
Application.ActiveWorkbook.Unprotect Password:="YourCode"
Application.CommandBars("Recall Macros").Delete
Else
Application.CommandBars("Recall Macros").Enabled = False
Application.ActiveWorkbook.Unprotect Password:=YourCode"
End If
End Sub

HTH Lou

"J_Will" wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

How do I unprotect/with password to run marco and protect
 

Oaky, I fixed the Syntax error - I THINK IT WORKED!!!!!!

"Dave Peterson" wrote:

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect password:="sandy crotch"
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending, _
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, password:="sandy crotch"
End Sub

J_Will wrote:

Password = "sandy crotch"

Can you put it into my macro, below, so I can cut and paste? So it
unprotecteds the sheet (sandy crotch) and password protects it (sandy crotch)

Sub sort_of()
'
' sort_of Macro
' Macro recorded 2/10/2006 by Julianne
'

'
ActiveSheet.Unprotect
Range("A16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("AF16"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

"Dave Peterson" wrote:

option explicit
sub yourmacro()
activesheet.unprotect password:="topsecret"
'your sort code here
activesheet.protect password:="topsecret"
end sub



J_Will wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson


--

Dave Peterson

How do I unprotect/with password to run marco and protect
 

Your other post said you got it working????

J_Will wrote:

Didn't work. Don't give up on me. I'll hold out until tonigh. If I can't
figure it out, I'll send with out my button.

"Rookie 1st class" wrote:

Try adapting these to meet your needs:

Private Sub Workbook_Open()
' Perform this when workbook opened.
' Macro recorded 06/15/00 by Lou Mullen
Worksheets("Recall").Select
Application.ActiveWorkbook.Protect Password:="YourCode"
range("A1").Select
ActiveSheet.Unprotect
Selection.Sort Key1:=range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect
Application.CommandBars("Recall Macros").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Perform steps on exit Macros
'Macro recorded 06/15/00 by Lou Mullen
Application.EnableCancelKey = xlDisabled
If Application.CommandBars("Recall Macros").Enabled = True Then
If Application.CommandBars("Recall Macros").Visible = True Then
Application.CommandBars("Recall Macros").Visible = False
End If
Application.ActiveWorkbook.Unprotect Password:="YourCode"
Application.CommandBars("Recall Macros").Delete
Else
Application.CommandBars("Recall Macros").Enabled = False
Application.ActiveWorkbook.Unprotect Password:=YourCode"
End If
End Sub

HTH Lou

"J_Will" wrote:

I have a locked sheet with columns unlocked for users to populate. I have it
protected with a password so users can only update certain cells. I wrote a
macro to sort data when entries are completed by clicking a button. How can
I unprotect it with password, run macro, and protect it with password after
sort is complete

--

Dave Peterson