Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro password-All sheets visible
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro password-All sheets visible
Hi, can this be modified so an user to have acces to all sheets?
Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Something like allsheets.visible?
Can this be done?


This is the entire code:
.................................................. .................................................. ..........
UserForm1:

Option Explicit

Private Sub cmdOK_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value

Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Case "Roy"
If strPword = "Open" Then
Sheets("Roy").Visible = xlSheetVisible
Sheets("Roy").Select
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly,
"Timewriting"
End Select
'optional
Sheets("main").Visible = xlSheetHidden
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!You must enter your user
name & password", vbCritical + vbOKOnly, "Password required"
End If
End Sub
.................................................. .................................................. ..........
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Module1.HideAllSheets
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub
.................................................. .................................................. .............

Module1:

Option Explicit

Sub HideAllSheets()
'make sure all sheets are hidden
Dim wSht As Worksheet
If Sheets("main").Visible = xlSheetHidden Then
Sheets("main").Visible = xlSheetVisible
End If
For Each wSht In Worksheets
If wSht.Name < "main" And wSht.Visible = xlSheetVisible Then
wSht.Visible = xlSheetVeryHidden
End If
Next wSht
End Sub


Thanks!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,533
Default Macro password-All sheets visible
Hi

Use this:

For each sh In ThisWorkbook.Sheets
sh.Visible=xlSheetVisible
Next

Regards,
Per

"puiuluipui" skrev i meddelelsen
...
Hi, can this be modified so an user to have acces to all sheets?
Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Something like allsheets.visible?
Can this be done?


This is the entire code:
.................................................. .................................................. .........
UserForm1:

Option Explicit

Private Sub cmdOK_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value

Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Case "Roy"
If strPword = "Open" Then
Sheets("Roy").Visible = xlSheetVisible
Sheets("Roy").Select
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly,
"Timewriting"
End Select
'optional
Sheets("main").Visible = xlSheetHidden
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!You must enter your user
name & password", vbCritical + vbOKOnly, "Password required"
End If
End Sub
.................................................. .................................................. .........
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Module1.HideAllSheets
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub
.................................................. .................................................. ............

Module1:

Option Explicit

Sub HideAllSheets()
'make sure all sheets are hidden
Dim wSht As Worksheet
If Sheets("main").Visible = xlSheetHidden Then
Sheets("main").Visible = xlSheetVisible
End If
For Each wSht In Worksheets
If wSht.Name < "main" And wSht.Visible = xlSheetVisible Then
wSht.Visible = xlSheetVeryHidden
End If
Next wSht
End Sub


Thanks!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro password-All sheets visible
Hi, i receive an error. I put your line like this:
Select Case strUser
Case "admin"
If strPword = "admin" Then
For Each Sh In ThisWorkbook.Sheets
Sh.Visible = xlSheetVisible
Next
Unload Me
End If
Am i doing something wrong?
The error is highlighting "sh" with blue and the first line af the macro
with yellow. "Private Sub cmdOK_Click()"
Error message:
Compile error:
Variable not defined.

Thanks!

"Per Jessen" wrote:

Hi

Use this:

For each sh In ThisWorkbook.Sheets
sh.Visible=xlSheetVisible
Next

Regards,
Per

"puiuluipui" skrev i meddelelsen
...
Hi, can this be modified so an user to have acces to all sheets?
Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Something like allsheets.visible?
Can this be done?


This is the entire code:
.................................................. .................................................. .........
UserForm1:

Option Explicit

Private Sub cmdOK_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value

Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Case "Roy"
If strPword = "Open" Then
Sheets("Roy").Visible = xlSheetVisible
Sheets("Roy").Select
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly,
"Timewriting"
End Select
'optional
Sheets("main").Visible = xlSheetHidden
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!You must enter your user
name & password", vbCritical + vbOKOnly, "Password required"
End If
End Sub
.................................................. .................................................. .........
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Module1.HideAllSheets
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub
.................................................. .................................................. ............

Module1:

Option Explicit

Sub HideAllSheets()
'make sure all sheets are hidden
Dim wSht As Worksheet
If Sheets("main").Visible = xlSheetHidden Then
Sheets("main").Visible = xlSheetVisible
End If
For Each wSht In Worksheets
If wSht.Name < "main" And wSht.Visible = xlSheetVisible Then
wSht.Visible = xlSheetVeryHidden
End If
Next wSht
End Sub


Thanks!


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Macro password-All sheets visible
You musr define Sh

Dim Sh as Worksheet


Gord Dibben MS Excel MVP

On Mon, 14 Sep 2009 12:53:02 -0700, puiuluipui
wrote:

Hi, i receive an error. I put your line like this:
Select Case strUser
Case "admin"
If strPword = "admin" Then
For Each Sh In ThisWorkbook.Sheets
Sh.Visible = xlSheetVisible
Next
Unload Me
End If
Am i doing something wrong?
The error is highlighting "sh" with blue and the first line af the macro
with yellow. "Private Sub cmdOK_Click()"
Error message:
Compile error:
Variable not defined.

Thanks!

"Per Jessen" wrote:

Hi

Use this:

For each sh In ThisWorkbook.Sheets
sh.Visible=xlSheetVisible
Next

Regards,
Per

"puiuluipui" skrev i meddelelsen
...
Hi, can this be modified so an user to have acces to all sheets?
Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Something like allsheets.visible?
Can this be done?


This is the entire code:
.................................................. .................................................. .........
UserForm1:

Option Explicit

Private Sub cmdOK_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value

Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Case "Roy"
If strPword = "Open" Then
Sheets("Roy").Visible = xlSheetVisible
Sheets("Roy").Select
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly,
"Timewriting"
End Select
'optional
Sheets("main").Visible = xlSheetHidden
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!You must enter your user
name & password", vbCritical + vbOKOnly, "Password required"
End If
End Sub
.................................................. .................................................. .........
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Module1.HideAllSheets
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub
.................................................. .................................................. ............

Module1:

Option Explicit

Sub HideAllSheets()
'make sure all sheets are hidden
Dim wSht As Worksheet
If Sheets("main").Visible = xlSheetHidden Then
Sheets("main").Visible = xlSheetVisible
End If
For Each wSht In Worksheets
If wSht.Name < "main" And wSht.Visible = xlSheetVisible Then
wSht.Visible = xlSheetVeryHidden
End If
Next wSht
End Sub


Thanks!



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default Macro password-All sheets visible
It's working!
Thanks allot!

"Gord Dibben" wrote:

You musr define Sh

Dim Sh as Worksheet


Gord Dibben MS Excel MVP

On Mon, 14 Sep 2009 12:53:02 -0700, puiuluipui
wrote:

Hi, i receive an error. I put your line like this:
Select Case strUser
Case "admin"
If strPword = "admin" Then
For Each Sh In ThisWorkbook.Sheets
Sh.Visible = xlSheetVisible
Next
Unload Me
End If
Am i doing something wrong?
The error is highlighting "sh" with blue and the first line af the macro
with yellow. "Private Sub cmdOK_Click()"
Error message:
Compile error:
Variable not defined.

Thanks!

"Per Jessen" wrote:

Hi

Use this:

For each sh In ThisWorkbook.Sheets
sh.Visible=xlSheetVisible
Next

Regards,
Per

"puiuluipui" skrev i meddelelsen
...
Hi, can this be modified so an user to have acces to all sheets?
Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Something like allsheets.visible?
Can this be done?


This is the entire code:
.................................................. .................................................. .........
UserForm1:

Option Explicit

Private Sub cmdOK_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value

Select Case strUser
Case "Jack"
If strPword = "Secret" Then
Sheets("Jack").Visible = xlSheetVisible
Sheets("Jack").Select
Unload Me
End If
Case "Roy"
If strPword = "Open" Then
Sheets("Roy").Visible = xlSheetVisible
Sheets("Roy").Select
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly,
"Timewriting"
End Select
'optional
Sheets("main").Visible = xlSheetHidden
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!You must enter your user
name & password", vbCritical + vbOKOnly, "Password required"
End If
End Sub
.................................................. .................................................. .........
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Module1.HideAllSheets
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub
.................................................. .................................................. ............

Module1:

Option Explicit

Sub HideAllSheets()
'make sure all sheets are hidden
Dim wSht As Worksheet
If Sheets("main").Visible = xlSheetHidden Then
Sheets("main").Visible = xlSheetVisible
End If
For Each wSht In Worksheets
If wSht.Name < "main" And wSht.Visible = xlSheetVisible Then
wSht.Visible = xlSheetVeryHidden
End If
Next wSht
End Sub


Thanks!




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
data only visible by using a password mariekek5 Excel Discussion (Misc queries) 3 June 19th 09 03:59 PM
Print Visible Sheets Macro Lisa C. Excel Discussion (Misc queries) 1 April 6th 09 06:08 AM
Using VB to password protect, but sheet remains visible JennyJeneralGraves Excel Discussion (Misc queries) 0 March 30th 06 02:12 AM
How to see macro code of a password protected macro without a password? Dmitry Kopnichev Excel Worksheet Functions 5 October 27th 05 09:57 AM
3 sheets in workbook, but visible only one? slaya_cz Excel Discussion (Misc queries) 3 September 30th 05 01:37 PM


All times are GMT +1. The time now is 06:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement