You can hide each sheet except for the one that should be seen. But this
becomes kind of intricate (and easily screwed up).
My suggestion is this:
Save the workbook with the real data with a password (under the
File|SaveAs|Tools menu). Don't share that password with anyone.
Now create another workbook that validates the user (move the userform out of
the workbook you just put it in (sorry)).
Then if the user disables macros, they won't get to open the other workbook.
If they enable macros, you can validate the user. If they're valid, you can
open the real workbook (you supply the password).
After you open the workbook, you can hide/show the worksheets that you want to
hide/show.
Kind of like...
Option Explicit
Private Sub CommandButton1_Click()
Dim HidWks As Worksheet
Dim res As Variant
Dim OkToContinue As Boolean
Dim RealWkbk As Workbook
Dim RealWkbkName As String
Dim RealWkbkPwd As String
Dim testStr As String
Dim wks As Worksheet
Dim wksToSee As String
Dim myID As Variant
RealWkbkName = "C:\my documents\excel\book2.xls"
RealWkbkPwd = "a"
testStr = ""
On Error Resume Next
testStr = Dir(RealWkbkName)
On Error GoTo 0
If testStr = "" Then
MsgBox "Design error--workbook not found!"
Else
Set HidWks = Worksheets("hidden")
OkToContinue = True
With HidWks
If IsNumeric(Me.TextBox1.Value) Then
myID = CLng(Me.TextBox1.Value)
End If
res = Application.Match(myID, .Range("a:a"), 0)
If IsError(res) Then
MsgBox "Invalid User Id"
OkToContinue = False
Else
If CStr(.Range("a:a")(res, 2).Value) <> Me.TextBox2.Value Then
MsgBox "Invalid password for ID"
OkToContinue = False
End If
End If
If OkToContinue = True Then
wksToSee = .Range("a:a")(res, 3).Value
Set RealWkbk = Workbooks.Open(Filename:=RealWkbkName, _
Password:=RealWkbkPwd)
'show the one you need to show
If WorksheetExists(wksToSee, RealWkbk) = False Then
MsgBox "Design error--sheet doesn't exist"
Else
RealWkbk.Worksheets(wksToSee).Visible = True
For Each wks In RealWkbk.Worksheets
If LCase(wks.Name) = wksToSee Then
'already shown, do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks
End If
End If
End With
End If
Unload Me
End Sub
Then in a general module:
Option Explicit
Sub auto_open()
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.EnableCancelKey = xlInterrupt
'ThisWorkbook.Close savechanges:=True
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function
When you're done testing and after you've saved, you can uncomment that .close
line at the end.
That second procedure (worksheetexists was stolen from Chip Pearson.)
The .enablecancelkey stops the user from breaking out of your code while it's
executing.
Sorry to bother you again Dave, but I had one more question.
The excel file is being shared by 50 employees, so does this mean that once
a sheet is made visible that everyone will be able to see their sheet. Is
there a way to make it visible to only that person, or is that stretching it.
Thanks
