VBA code Worksheets protection and VBA code password protection

[reemt]

Hi,

i have a workbook of 3 sheets "A", "B", and "C". i need a code to protect sheets "B" and "C" in a way to be not visible only if the pasword that is entered on sheet"A" is correct on cell F14 of sheet A(for ex)

also, i need to protect this vba code with a password.

I'm new in VBA ,, never written a code.

can someone help please.

thanks

 

[JPM]

OK, the first thing that I would do is record a macro. Just start recording then click on some cell e.g. F14 and then stop the recording. This will set up a module in the file in which Excel will have placed your recorded macro.

Go to that module and in place of your recorded macro save this code:

Sub Macro2()
Sheets("B").Visible = False
Sheets("C").Visible = False
Sheets("A").Select
If Range("F14").Value = "password" Then
Sheets("B").Visible = True
Sheets("C").Visible = True
Else
MsgBox "You entered an incorrect password"
End If
End Sub

To protect the VBA code from viewing, in the VBAProject viewer, right mouse click on Module1 and select VBAProject properties, select the Protection tab, select "Lock project form viewing" and enter a password.

Hope this will do the trick for you.

 

[JPM]

Actually try this one instead to make the method of hidding the sheets more secure:
Sub Macro2()
Sheets("B").Visible = xlVeryHidden
Sheets("C").Visible = xlVeryHidden
Sheets("A").Select
If Range("F14").Value = "password" Then
Sheets("B").Visible = True
Sheets("C").Visible = True
Else
MsgBox "You entered an incorrect password"
End If
End Sub

 

[reemt]

Thanks for your reply..

actually i have a total of 41 sheets to hide and i saved this code but it didnt work.. the sheets are still showing:

Sub HidingSheets()
'
' HidingSheets Macro
' to hide sheets from being visible unless a password is entered

'
Sheets("Title").Visible = xlVeryHidden
Sheets("Introduction").Visible = xlVeryHidden
Sheets("Executive Summary").Visible = xlVeryHidden
Sheets("Service Charge Assumptions").Visible = xlVeryHidden
Sheets("Participation Summary").Visible = xlVeryHidden
Sheets("Common Area").Visible = xlVeryHidden
Sheets("Contingency Options").Visible = xlVeryHidden
Sheets("MC Cost Summary").Visible = xlVeryHidden
Sheets("MC Management").Visible = xlVeryHidden
Sheets("MC Utilities").Visible = xlVeryHidden
Sheets("MC Soft Services").Visible = xlVeryHidden
Sheets("MC Hard Services").Visible = xlVeryHidden
Sheets("MC Income").Visible = xlVeryHidden
Sheets("MC Insurance").Visible = xlVeryHidden
Sheets("MC Exc.Expenditure").Visible = xlVeryHidden
Sheets("MC Master Community").Visible = xlVeryHidden
Sheets("MC Reserve Fund Summary").Visible = xlVeryHidden
Sheets("Villa Cost Summary").Visible = xlVeryHidden
Sheets("Villa Management").Visible = xlVeryHidden
Sheets("Villa Utilities").Visible = xlVeryHidden
Sheets("Villa Soft Services").Visible = xlVeryHidden
Sheets("Villa Hard Services").Visible = xlVeryHidden
Sheets("Villa Income").Visible = xlVeryHidden
Sheets("Villa Insurance").Visible = xlVeryHidden
Sheets("Villa Exc.Expenditure").Visible = xlVeryHidden
Sheets("Villa Master Community").Visible = xlVeryHidden
Sheets("Villa Reserve Fund Summary").Visible = xlVeryHidden
Sheets("APT ' Cost Summary").Visible = xlVeryHidden
Sheets("APT ' Management").Visible = xlVeryHidden
Sheets("APT ' Utilities").Visible = xlVeryHidden
Sheets("APT ' Soft Services").Visible = xlVeryHidden
Sheets("APT ' Hard Services").Visible = xlVeryHidden
Sheets("APT ' Income").Visible = xlVeryHidden
Sheets("APT ' Insurance").Visible = xlVeryHidden
Sheets("APT ' Exc. Expenditure").Visible = xlVeryHidden
Sheets("APT ' Master Community").Visible = xlVeryHidden
Sheets("APT ' Reserve Fund Summary").Visible = xlVeryHidden
Sheets("Reserve Fund Intro").Visible = xlVeryHidden
Sheets("Reserve Fund Assumptions").Visible = xlVeryHidden
Sheets("Cost BreakDown Title Page").Visible = xlVeryHidden
Sheets("Database").Visible = xlVeryHidden
Sheets("Login").Select
If Range("B10:C10").Value = "capitala" Then
Sheets("Title").Visible = True
Sheets("Introduction").Visible = True
Sheets("Executive Summary").Visible = True
Sheets("Service Charge Assumptions").Visible = True
Sheets("Participation Summary").Visible = True
Sheets("Common Area").Visible = True
Sheets("Contingency Options").Visible = True
Sheets("MC Cost Summary").Visible = True
Sheets("MC Management").Visible = True
Sheets("MC Utilities").Visible = True
Sheets("MC Soft Services").Visible = True
Sheets("MC Hard Services").Visible = True
Sheets("MC Income").Visible = True
Sheets("MC Insurance").Visible = True
Sheets("MC Exc.Expenditure").Visible = True
Sheets("MC Master Community").Visible = True
Sheets("MC Reserve Fund Summary").Visible = True
Sheets("Villa Cost Summary").Visible = True
Sheets("Villa Management").Visible = True
Sheets("Villa Utilities").Visible = True
Sheets("Villa Soft Services").Visible = True
Sheets("Villa Hard Services").Visible = True
Sheets("Villa Income").Visible = True
Sheets("Villa Insurance").Visible = True
Sheets("Villa Exc.Expenditure").Visible = True
Sheets("Villa Master Community").Visible = True
Sheets("Villa Reserve Fund Summary").Visible = True
Sheets("APT ' Cost Summary").Visible = True
Sheets("APT ' Management").Visible = True
Sheets("APT ' Utilities").Visible = True
Sheets("APT ' Soft Services").Visible = True
Sheets("APT ' Hard Services").Visible = True
Sheets("APT ' Income").Visible = True
Sheets("APT ' Insurance").Visible = True
Sheets("APT ' Exc. Expenditure").Visible = True
Sheets("APT ' Master Community").Visible = True
Sheets("APT ' Reserve Fund Summary").Visible = True
Sheets("Reserve Fund Intro").Visible = True
Sheets("Reserve Fund Assumptions").Visible = True
Sheets("Cost BreakDown Title Page").Visible = True
Sheets("Database").Visible = True
Else
MsgBox "You entered an incorrect password"
End If
End Sub



do you have any clue why it is not working? it might be a procedure thing, i.e. maybe i might do something to run the macro, or does it run automaticaly?


and added to my previous question i have some other sheets i need them to be hidden all the time even if the password is correct,, how to do that...

Thanks

 

[pedie]

If you have that many sheets to hide then i think it would be better to go the other way round...

Modify the code to suit you.
If the sheets name <> Sheet5 and Sheet2 then hide.

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub Hidesheets()[/FONT]
[FONT=Courier New]Dim ws As Worksheet[/FONT]
[FONT=Courier New]For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet5" And ws.Name <> "Sheet2" Then
 ws.Visible = xlSheetVeryHidden
 Else
 ws.Visible = xlSheetVisible
End If
Next ws
End Sub

 

[reemt]

Hi Pedie,
Thanks for your reply

i tried your code to hide a list of 5 sheets and what happenned is that all the other sheets now are hidden , even the Login Sheets and 3 of the sheets that i want to hide are visible :S

any clue?

thanks

 

[pedie]

Hi Pedie,

Thanks for your reply

i tried your code to hide a list of 5 sheets and what happenned is that all the other sheets now are hidden , even the Login Sheets and 3 of the sheets that i want to hide are visible :S

any clue?

thanks

May I see your whole code? And list of sheet you want to hide and list that you do not want to hide...