+ Reply to Thread
Results 1 to 8 of 8

How to Prompt for Sheet Unprotect Password using a Macro

  1. #1
    Registered User
    Join Date
    08-15-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    57

    Question How to Prompt for Sheet Unprotect Password using a Macro

    Hi,

    I have few sheets in my workbook however at a time only 1 is visible and on clicking on a command button with sheet name that sheet open and remaining get hidden.

    Macro as below:

    [/CODE]

    Sub Button1_Click()
    ActiveSheet.Unprotect Password:="1234"
    Sheets("Sheet2").Visible = True
    Sheets("Sheet1").Visible = False
    ActiveSheet.Protect Password:="3456"
    End Sub

    [/CODE]

    Instead of having the password in the macro itself I wish the macro to ask user for the password so that it can be open by intended user only.

    Please note the un-protect password and protect are not same for reason that user can modify the sheet.

    As I have said there are different sheet in the workbook each will have separate password so that only intended person can open the intended sheet.

    Just to clarify before closing the sheet a macro will run and change the password of each sheet to the one with user so that next when he click on the sheet button he can open it using his password.

    My question is still the same and i believe simple just beyond my knowledge: how to make Macro ask for user password.

    Hope for the earliest reply, thanks in advance.

    Reg,
    Vishu
    Last edited by cvishu; 10-18-2014 at 05:31 AM. Reason: to comply with rules

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Hi Vishu,
    1) please edit your post to comply with http://www.excelforum.com/forum-rule...rum-rules.html
    Namely - use code tags !

    2)
    Please Login or Register  to view this content.
    in standard module insert declaration of mystoredpassword
    like:
    Please Login or Register  to view this content.
    and think about initializing mystoredpassword when workbook is opened - so probably in Workbook_Open event handler.
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Just save your password on every sheet
    suppose "A1" of every sheet and then use

    ActiveSheet.protect Password:=a1 ' for protection
    ActiveSheet.Unprotect Password:=a1 ' for unprotect

    use input box to change the password and then change the value of sheet("A1") with them.

  4. #4
    Registered User
    Join Date
    08-15-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    57

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Thanks for the reply and I have gone through the rules and will post accordingly in future.

    However request you to guide me more about userpass as i am totally new to macro.

    Please also confirm if the userpass can be different for each sheet??

    Reg,
    Vishu

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Quote Originally Posted by cvishu View Post
    ... and will post accordingly in future
    No! I kindly asked you to edit your post, and you didn't.
    Any further meritoric input from me would be against rules.

  6. #6
    Registered User
    Join Date
    08-15-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    57

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Hi,

    I have few sheets in my workbook however at a time only 1 is visible and on clicking on a command button with sheet name that sheet open and remaining get hidden.

    Macro as below:

    Please Login or Register  to view this content.
    Sub Button1_Click()
    ActiveSheet.Unprotect Password:="1234"
    Sheets("Sheet2").Visible = True
    Sheets("Sheet1").Visible = False
    ActiveSheet.Protect Password:="3456"
    End Sub

    Please Login or Register  to view this content.
    Instead of having the password in the macro itself I wish the macro to ask user for the password so that it can be open by intended user only.

    Please note the un-protect password and protect are not same for reason that user can modify the sheet.

    As I have said there are different sheet in the workbook each will have separate password so that only intended person can open the intended sheet.

    Just to clarify before closing the sheet a macro will run and change the password of each sheet to the one with user so that next when he click on the sheet button he can open it using his password.

    My question is still the same and i believe simple just beyond my knowledge: how to make Macro ask for user password.

    Hope for the earliest reply, thanks in advance.

    Reg,
    Vishu

  7. #7
    Registered User
    Join Date
    08-15-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    57

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    Sorry but tried its not working....

    Hi,

    I have few sheets in my workbook however at a time only 1 is visible and on clicking on a command button with sheet name that sheet open and remaining get hidden.

    Macro as below:


    Please Login or Register  to view this content.
    Instead of having the password in the macro itself I wish the macro to ask user for the password so that it can be open by intended user only.

    Please note the un-protect password and protect are not same for reason that user can modify the sheet.

    As I have said there are different sheet in the workbook each will have separate password so that only intended person can open the intended sheet.

    Just to clarify before closing the sheet a macro will run and change the password of each sheet to the one with user so that next when he click on the sheet button he can open it using his password.

    My question is still the same and i believe simple just beyond my knowledge: how to make Macro ask for user password.

    Hope for the earliest reply, thanks in advance.

    Reg,
    Vishu

  8. #8
    Registered User
    Join Date
    08-15-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    57

    Re: How to Prompt for Sheet Unprotect Password using a Macro

    ok done hope you can guide me now.....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ActiveWorkbook.Unprotect (prompt for password)
    By jamesryan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2008, 04:54 AM
  2. Lost password for unprotect sheet
    By riquest in forum Excel General
    Replies: 2
    Last Post: 03-16-2007, 06:28 AM
  3. [SOLVED] Unprotect Sheet Password in Macros
    By Theresa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-02-2006, 08:55 AM
  4. Forgot the password (to unprotect a sheet)
    By Venkatesh V in forum Excel General
    Replies: 1
    Last Post: 03-29-2006, 03:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1