Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / November 2007

Tip: Looking for answers? Try searching our database.

Open Specific Form by User Login

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bowtie63 - 29 Nov 2007 21:46 GMT
I have a workbook that requires individuals to login to enter data.  Once
they login, data is entered via a form.  My question is how do I set the form
up so that only the users will see the form and not the manager?  There are 4
worksheets, all visible for the manager only.  The form puts the data into
one of the sheets.  Any help would be great!  Thanks!

This is what I have in the ThisWorkbook section:

Private Sub Workbook_Open()
  LogOnForm.Show
  EntryForm.Show
End Sub

This is the VB for the login form:

Private Sub cmdEXT_Click()
If MsgBox("Do you want to quit Excel", vbYesNo) = vbYes Then
   Unload Me
   Application.Quit
Else
   Unload Me
   Sheets("Logon").Activate

End If
End Sub

Private Sub cmdOK_Click()
Dim a, u, p, w(), i As Long, db As Worksheet, Flg   As Boolean
Dim j   As Long, x, y, c As Long, rSource As String
Set db = Sheets("DashBoard"): Flg = False: c = 0: x = 0
With db
   a = .Range("a1").CurrentRegion
End With

u = UCase(Me.tbUN): p = Me.tbPW: Flg = False
With Application
   x = .Match(u, .Index(a, 0, 1), 0)
End With

If Not IsError(x) Then
   If Application.Index(a, x, 2) = p Then Flg = True
   If Flg Then
       ReDim w(1 To UBound(a, 2) - 2)
       For j = 3 To UBound(a, 2)
           If UCase(a(x, j)) = "A" Then c = c + 1: w(c) = a(1, j)
       Next
   Else
       MsgBox "Incorrect Password", vbCritical + vbOKOnly
       Exit Sub
   End If
Else
   MsgBox "Incorrect User Name", vbCritical + vbOKOnly
   Exit Sub
End If

For i = 1 To Sheets.Count
   If Sheets(i).Name <> "Logon" Then
       If IsError(Application.Match(Sheets(i).Name, w, 0)) Then
           On Error Resume Next
           Sheets(i).Visible = xlVeryHidden
       Else
           Sheets(i).Visible = xlSheetVisible
       End If
   End If
Next

Sheets("Logon").Visible = xlSheetVisible

EntryForm.Show

End Sub

Private Sub UserForm_Activate()
   Me.tbPW.SetFocus
   Me.tbUN.SetFocus
End Sub
JLatham - 30 Nov 2007 00:21 GMT
You may be able to use
Environ("username")
to solve the problem - that will return the username who logged in.  Set up
an If ... Then to test to see if it's the manager or nor and act accordingly.

> I have a workbook that requires individuals to login to enter data.  Once
> they login, data is entered via a form.  My question is how do I set the form
[quoted text clipped - 72 lines]
>     Me.tbUN.SetFocus
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.