| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more question on worksheet protection
Is there a way to enable/disable the VBA password and lock project for
viewing, using a macro? Dylan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more question on worksheet protection
Hi Dylan,
Assuming you know the password, the following code, based on work by fellow MVP Bill Manville, will unprotect/protect a vba project: Sub UnprotectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection < 1 Then Exit Sub ' already unprotected Set Application.VBE.ActiveVBProject = vbProj SendKeys Pwd & "~~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Sub ProtectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Cheers -- macropod [MVP - Microsoft Word] "dd" <dd.dd wrote in message ... | Is there a way to enable/disable the VBA password and lock project for | viewing, using a macro? | | Dylan | | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more question on worksheet protection
Hi macropod
macropod, your original version probably works fine, but due to my lack of knowledge, I don't know how to run it !! I want to assign it to a custom menu button, but can't see the macro in the list of available macros. If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change Sendkeys to include the confirm password. When I now run it, the Dialog box remains on the screen. How do I use the original code? How do I okay the dialog? Sub ProtectVBProj() ' to allow it to appear as a macro 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a macro? Dim Pwd As String Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345" 'However, the dialog remains on the screen - how do I update it. Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub "macropod" wrote in message ... Hi Dylan, Assuming you know the password, the following code, based on work by fellow MVP Bill Manville, will unprotect/protect a vba project: Sub UnprotectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection < 1 Then Exit Sub ' already unprotected Set Application.VBE.ActiveVBProject = vbProj SendKeys Pwd & "~~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Sub ProtectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Cheers -- macropod [MVP - Microsoft Word] "dd" <dd.dd wrote in message ... | Is there a way to enable/disable the VBA password and lock project for | viewing, using a macro? | | Dylan | | |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more question on worksheet protection
macropod,
Thanks very much for providing this macro and answering my question. I managed to figure out that: & "{Enter}" will close the dialogs. I now find that the macro runs on the workbook where it is saved (personal macros workbook, which is hidden) rather than the workbook I'm working on. Is there any way to apply it to all open workbooks, or, maybe to select from a list of workbooks? Regards Dylan "dd" <dd.dd wrote in message ... Hi macropod macropod, your original version probably works fine, but due to my lack of knowledge, I don't know how to run it !! I want to assign it to a custom menu button, but can't see the macro in the list of available macros. If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change Sendkeys to include the confirm password. When I now run it, the Dialog box remains on the screen. How do I use the original code? How do I okay the dialog? Sub ProtectVBProj() ' to allow it to appear as a macro 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a macro? Dim Pwd As String Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345" 'However, the dialog remains on the screen - how do I update it. Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub "macropod" wrote in message ... Hi Dylan, Assuming you know the password, the following code, based on work by fellow MVP Bill Manville, will unprotect/protect a vba project: Sub UnprotectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection < 1 Then Exit Sub ' already unprotected Set Application.VBE.ActiveVBProject = vbProj SendKeys Pwd & "~~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Sub ProtectVBProj(ByVal Pwd As String) Dim vbProj As Object Set vbProj = ThisWorkbook.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Cheers -- macropod [MVP - Microsoft Word] "dd" <dd.dd wrote in message ... | Is there a way to enable/disable the VBA password and lock project for | viewing, using a macro? | | Dylan | | |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more question on worksheet protection
Hi Dylan,
To do run the code against a different workbook, or a set of workbooks, you'd add an additional argument to the code, as follows, then call the routines with both the workbook names and passwords. Sub UnprotectVBProject(WkBk As Workbook, ByVal Pwd As String) Dim vbProj As Object Set vbProj = WkBk.VBProject If vbProj.Protection < 1 Then Exit Sub ' already unprotected Set Application.VBE.ActiveVBProject = vbProj SendKeys Pwd & "~~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Sub ProtectVBProject(WkBk As Workbook, ByVal Pwd As String) Dim vbProj As Object Set vbProj = WkBk.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Cheers -- macropod [MVP - Microsoft Word] "dd" <dd.dd wrote in message ... | macropod, | | Thanks very much for providing this macro and answering my question. | | I managed to figure out that: & "{Enter}" will close the dialogs. I now find | that the macro runs on the workbook where it is saved (personal macros | workbook, which is hidden) rather than the workbook I'm working on. | | Is there any way to apply it to all open workbooks, or, maybe to select from | a list of workbooks? | | Regards | Dylan | | "dd" <dd.dd wrote in message ... | Hi macropod | | macropod, your original version probably works fine, but due to my lack of | knowledge, I don't know how to run it !! | | I want to assign it to a custom menu button, but can't see the macro in the | list of available macros. | | If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change | Sendkeys to include the confirm password. When I now run it, the Dialog box | remains on the screen. | | How do I use the original code? | How do I okay the dialog? | | Sub ProtectVBProj() ' to allow it to appear as a macro | 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a | macro? | Dim Pwd As String | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection = 1 Then Exit Sub ' already protected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345" | 'However, the dialog remains on the screen - how do I update it. | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | | "macropod" wrote in message | ... | Hi Dylan, | | Assuming you know the password, the following code, based on work by fellow | MVP Bill Manville, will unprotect/protect a vba project: | | Sub UnprotectVBProj(ByVal Pwd As String) | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection < 1 Then Exit Sub ' already unprotected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys Pwd & "~~" | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | Sub ProtectVBProj(ByVal Pwd As String) | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection = 1 Then Exit Sub ' already protected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | Cheers | | -- | macropod | [MVP - Microsoft Word] | | | "dd" <dd.dd wrote in message ... | | Is there a way to enable/disable the VBA password and lock project for | | viewing, using a macro? | | | | Dylan | | | | | | | | |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more question on worksheet protection
Hi Macropod,
I still can't see it in my macros list. Can you give me an example of how to call it? Much obliged Dylan "macropod" wrote in message ... Hi Dylan, To do run the code against a different workbook, or a set of workbooks, you'd add an additional argument to the code, as follows, then call the routines with both the workbook names and passwords. Sub UnprotectVBProject(WkBk As Workbook, ByVal Pwd As String) Dim vbProj As Object Set vbProj = WkBk.VBProject If vbProj.Protection < 1 Then Exit Sub ' already unprotected Set Application.VBE.ActiveVBProject = vbProj SendKeys Pwd & "~~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Sub ProtectVBProject(WkBk As Workbook, ByVal Pwd As String) Dim vbProj As Object Set vbProj = WkBk.VBProject If vbProj.Protection = 1 Then Exit Sub ' already protected Set Application.VBE.ActiveVBProject = vbProj SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute End Sub Cheers -- macropod [MVP - Microsoft Word] "dd" <dd.dd wrote in message ... | macropod, | | Thanks very much for providing this macro and answering my question. | | I managed to figure out that: & "{Enter}" will close the dialogs. I now find | that the macro runs on the workbook where it is saved (personal macros | workbook, which is hidden) rather than the workbook I'm working on. | | Is there any way to apply it to all open workbooks, or, maybe to select from | a list of workbooks? | | Regards | Dylan | | "dd" <dd.dd wrote in message ... | Hi macropod | | macropod, your original version probably works fine, but due to my lack of | knowledge, I don't know how to run it !! | | I want to assign it to a custom menu button, but can't see the macro in the | list of available macros. | | If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change | Sendkeys to include the confirm password. When I now run it, the Dialog box | remains on the screen. | | How do I use the original code? | How do I okay the dialog? | | Sub ProtectVBProj() ' to allow it to appear as a macro | 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a | macro? | Dim Pwd As String | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection = 1 Then Exit Sub ' already protected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345" | 'However, the dialog remains on the screen - how do I update it. | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | | "macropod" wrote in message | ... | Hi Dylan, | | Assuming you know the password, the following code, based on work by fellow | MVP Bill Manville, will unprotect/protect a vba project: | | Sub UnprotectVBProj(ByVal Pwd As String) | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection < 1 Then Exit Sub ' already unprotected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys Pwd & "~~" | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | Sub ProtectVBProj(ByVal Pwd As String) | Dim vbProj As Object | Set vbProj = ThisWorkbook.VBProject | If vbProj.Protection = 1 Then Exit Sub ' already protected | Set Application.VBE.ActiveVBProject = vbProj | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~" | Application.VBE.CommandBars(1).FindControl(ID:=257 8, | recursive:=True).Execute | End Sub | | Cheers | | -- | macropod | [MVP - Microsoft Word] | | | "dd" <dd.dd wrote in message ... | | Is there a way to enable/disable the VBA password and lock project for | | viewing, using a macro? | | | | Dylan | | | | | | | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet Protection Question | Excel Discussion (Misc queries) | |||
| Worksheet protection question | Excel Discussion (Misc queries) | |||
| A Worksheet Protection Question | New Users to Excel | |||
| Excel97 - Worksheet Protection Question | Excel Programming | |||
| question about worksheet protection using userinterfaceonly:=true | Excel Programming | |||
Hybrid Mode
