I have ComboBox and CommandButton on UserForm. I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. I been struggling with the code below but for some reason i am
getting error. It copies the Activesheet to specified Workbook but
not protecting the macro of that sheet and also not opening it after
everything is done. Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub


Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub

Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks.


"K" wrote:

I have ComboBox and CommandButton on UserForm. I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. I been struggling with the code below but for some reason i am
getting error. It copies the Activesheet to specified Workbook but
not protecting the macro of that sheet and also not opening it after
everything is done. Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub


Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub

On 11 Sep, 17:37, Orion Cochrane
wrote:
Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks..



"K" wrote:
I have ComboBox and CommandButton on UserForm. *I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. *I been struggling with the code below but for some reason i am
getting error. *It copies the Activesheet to specified Workbook but
not protecting *the macro of that sheet and also not opening it after
everything is done. *Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub- Hide quoted text -

- Show quoted text -

I know how to lock the VBA Project Manually. I am doing this as in my
office we use same Workbook Sheet and to work in we copy it to other
Workbooks. I am the one who created that Worksheet and I have lots of
Macro in it which i dont want people to see so as you know that once
you Move or Copy macro proteced Worksheet to other Workbook it lose
its VBA Project Password and lots of people will be doing this in my
office so i want some code that when they copy this Sheet to their
Workbook the macro should also get proteced. the code i showed in my
question i am quite near to what i want but something is missing that
why i need help.

If you have set a password on your VBA code in the source workbook, it will
still be on there in the destination workbook. The security on the code
itself does not prevent the code from running, it only restricts access to
view or change the code. There is no facility to add or delete passwords,
nor to add or delete protection for the code with VBA. It must be done
manually.

"K" wrote:

On 11 Sep, 17:37, Orion Cochrane
wrote:
Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks..



"K" wrote:
I have ComboBox and CommandButton on UserForm. I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. I been struggling with the code below but for some reason i am
getting error. It copies the Activesheet to specified Workbook but
not protecting the macro of that sheet and also not opening it after
everything is done. Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub- Hide quoted text -

- Show quoted text -

I know how to lock the VBA Project Manually. I am doing this as in my
office we use same Workbook Sheet and to work in we copy it to other
Workbooks. I am the one who created that Worksheet and I have lots of
Macro in it which i dont want people to see so as you know that once
you Move or Copy macro proteced Worksheet to other Workbook it lose
its VBA Project Password and lots of people will be doing this in my
office so i want some code that when they copy this Sheet to their
Workbook the macro should also get proteced. the code i showed in my
question i am quite near to what i want but something is missing that
why i need help.

Thanks for backing me up. As to the OP, in your CommandButton1_Click macro,
do you get an error for not declaring your cv at the outset?
--
Please rate posts so we know when we have answered your questions. Thanks.


"JLGWhiz" wrote:

If you have set a password on your VBA code in the source workbook, it will
still be on there in the destination workbook. The security on the code
itself does not prevent the code from running, it only restricts access to
view or change the code. There is no facility to add or delete passwords,
nor to add or delete protection for the code with VBA. It must be done
manually.

"K" wrote:

On 11 Sep, 17:37, Orion Cochrane
wrote:
Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks..



"K" wrote:
I have ComboBox and CommandButton on UserForm. I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. I been struggling with the code below but for some reason i am
getting error. It copies the Activesheet to specified Workbook but
not protecting the macro of that sheet and also not opening it after
everything is done. Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub- Hide quoted text -

- Show quoted text -

I know how to lock the VBA Project Manually. I am doing this as in my
office we use same Workbook Sheet and to work in we copy it to other
Workbooks. I am the one who created that Worksheet and I have lots of
Macro in it which i dont want people to see so as you know that once
you Move or Copy macro proteced Worksheet to other Workbook it lose
its VBA Project Password and lots of people will be doing this in my
office so i want some code that when they copy this Sheet to their
Workbook the macro should also get proteced. the code i showed in my
question i am quite near to what i want but something is missing that
why i need help.

On 11 Sep, 20:14, Orion Cochrane
wrote:
Thanks for backing me up. As to the OP, in your CommandButton1_Click macro,
do you get an error for not declaring your cv at the outset?
--
Please rate posts so we know when we have answered your questions. Thanks..



"JLGWhiz" wrote:
If you have set a password on your VBA code in the source workbook, it will
still be on there in the destination workbook. *The security on the code
itself does not prevent the code from running, it only restricts access to
view or change the code. *There is no facility to add or delete passwords,
nor to add or delete protection for the code with VBA. *It must be done
manually.

"K" wrote:

On 11 Sep, 17:37, Orion Cochrane
wrote:
Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks..

"K" wrote:
I have ComboBox and CommandButton on UserForm. *I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. *I been struggling with the code below but for some reason i am
getting error. *It copies the Activesheet to specified Workbook but
not protecting *the macro of that sheet and also not opening it after
everything is done. *Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub- Hide quoted text -

- Show quoted text -

I know how to lock the VBA Project Manually. *I am doing this as in my
office we use same Workbook Sheet and to work in we copy it to other
Workbooks. *I am the one who created that Worksheet and I have lots of
Macro in it which i dont want people to see so as you know that once
you Move or Copy macro proteced Worksheet to other Workbook it lose
its VBA Project Password and lots of people will be doing this in my
office so i want some code that when they copy this Sheet to their
Workbook the macro should also get proteced. *the code i showed in my
question i am quite near to what i want but something is missing that
why i need help.- Hide quoted text -

- Show quoted text -

I try again to explain my question with example. Lets say I have
Workbook "A" in which I have macro codes in Sheet Module. In Workbook
"A" the VBA Project is Password Protected so if someone open Workbook
"A" and try to View Code he will be prompt to enter the Password.
Because of codes are in Sheet Module now if someone open Workbook "A"
and right click on Sheet Tab and select "Move or Copy" and in drop
down of "Move or Copy dilog" he select another Workbook which is
Workbook "B" and press OK then this thing will copy Workbook "A" Sheet
into Workbook "B" and now if someone right click on Sheet Tab which is
copied in Workbook "B" and click on View code , he can now clearly see
the macro code which he wasn't able to see in Workbook "A" because of
Password Protection of VBA Project. When you "Move or Copy" Sheet
from VBA protected Workbook to other non protected Workbook you can
easly see the code in Sheet Module. Above in my question i got the
code (see below)

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub

This code works fine as it protect the VBA Project. What i was trying
to do that instead of user going and right clicking on Sheet Tab in
Workbook "A" to select "Move or Copy" to copy Sheet in Workbook "B" I
wrote a code that when someone open Workbook "A" he should get
UserForm in which he press the button to do the "Move or Copy" thing
and during that i want above code to protect the VBA Project of copied
Sheet in Workbook "B". I hope i was able to explain my question.
Nothing is impossible if you put your mind into it. I asked other
question on this Forum and some said that its not possible and some
gave me the code which worked perfect. So there must be sultion what
i am trying to achive. Why i am trying to do this is because i have
many code in Sheet module which i created after lots of research and i
dont want people to see it.

Well, I stand corrected. You can use keystroke commands instead of manually
entering the password. So, it can be done with code. It still stands that
if you protect the code in the original document, it should remain protected
in the destination document.

"K" wrote:

On 11 Sep, 20:14, Orion Cochrane
wrote:
Thanks for backing me up. As to the OP, in your CommandButton1_Click macro,
do you get an error for not declaring your cv at the outset?
--
Please rate posts so we know when we have answered your questions. Thanks..



"JLGWhiz" wrote:
If you have set a password on your VBA code in the source workbook, it will
still be on there in the destination workbook. The security on the code
itself does not prevent the code from running, it only restricts access to
view or change the code. There is no facility to add or delete passwords,
nor to add or delete protection for the code with VBA. It must be done
manually.

"K" wrote:

On 11 Sep, 17:37, Orion Cochrane
wrote:
Just wondering why you would need a procedure to lock your project. Lock it
in VB under Tools VBAPriject Properties. Under the Protection tab, click
the Lock Project for Edititng checkbox and type in a password to lock your
project.
--
Please rate posts so we know when we have answered your questions. Thanks..

"K" wrote:
I have ComboBox and CommandButton on UserForm. I am trying that when
I click CommandButton on UserForm it should copy the Activesheet to
ComboBox.Value (which is Workbook.Name) and then protect its macro and
close that Workbook with saving all changes in it and then open it
again. I been struggling with the code below but for some reason i am
getting error. It copies the Activesheet to specified Workbook but
not protecting the macro of that sheet and also not opening it after
everything is done. Can please someone look my code below and tell
that what i am doing wrong.

Private Sub CommandButton1_Click()
cv = Me.ComboBox1.Value
ThisWorkbook.ActiveSheet.Copy
after:=Workbooks(cv).Sheets(Sheets.Count)
ProtectVBProject Workbooks(cv), "jack"
Workbooks(cv).Save
Unload UserForm1
Workbooks(cv).Close True
Workbooks.Open Filename:=Workbooks(cv)
End Sub

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub- Hide quoted text -

- Show quoted text -

I know how to lock the VBA Project Manually. I am doing this as in my
office we use same Workbook Sheet and to work in we copy it to other
Workbooks. I am the one who created that Worksheet and I have lots of
Macro in it which i dont want people to see so as you know that once
you Move or Copy macro proteced Worksheet to other Workbook it lose
its VBA Project Password and lots of people will be doing this in my
office so i want some code that when they copy this Sheet to their
Workbook the macro should also get proteced. the code i showed in my
question i am quite near to what i want but something is missing that
why i need help.- Hide quoted text -

- Show quoted text -

I try again to explain my question with example. Lets say I have
Workbook "A" in which I have macro codes in Sheet Module. In Workbook
"A" the VBA Project is Password Protected so if someone open Workbook
"A" and try to View Code he will be prompt to enter the Password.
Because of codes are in Sheet Module now if someone open Workbook "A"
and right click on Sheet Tab and select "Move or Copy" and in drop
down of "Move or Copy dilog" he select another Workbook which is
Workbook "B" and press OK then this thing will copy Workbook "A" Sheet
into Workbook "B" and now if someone right click on Sheet Tab which is
copied in Workbook "B" and click on View code , he can now clearly see
the macro code which he wasn't able to see in Workbook "A" because of
Password Protection of VBA Project. When you "Move or Copy" Sheet
from VBA protected Workbook to other non protected Workbook you can
easly see the code in Sheet Module. Above in my question i got the
code (see below)

Sub ProtectVBProject(wb As Workbook, ByVal Password As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection = 1 Then Exit Sub
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password &
"~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
wb.Save
End Sub

This code works fine as it protect the VBA Project. What i was trying
to do that instead of user going and right clicking on Sheet Tab in
Workbook "A" to select "Move or Copy" to copy Sheet in Workbook "B" I
wrote a code that when someone open Workbook "A" he should get
UserForm in which he press the button to do the "Move or Copy" thing
and during that i want above code to protect the VBA Project of copied
Sheet in Workbook "B". I hope i was able to explain my question.
Nothing is impossible if you put your mind into it. I asked other
question on this Forum and some said that its not possible and some
gave me the code which worked perfect. So there must be sultion what
i am trying to achive. Why i am trying to do this is because i have
many code in Sheet module which i created after lots of research and i
dont want people to see it.