I am working on a project for a colleague and it has me baffled on how to proceed. Basically what I need to do is create a userform that collects log in information (user and password) and based on the user unhide certain columns in a spreadsheet. I have never used a userform before and the examples I have looked at are not quite fitting my needs. For the most part the users will need to only view the column that is theirs, but there are a handful of users that will also need to edit column(s).
I have the following code that I am trying to modify but am being very unsuccessful. The original code was designed to hide or unhide entire sheets, but I just want to hide or unhide specific columns.
Any assistance would be appreciated. I have attached a sanitized copy of the sheet.
Thanks,
Greg
Code: Select all
Private Sub UserForm_Click()
Dim bOK2Use As Boolean
Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean
bOK2Use = False
bError = True
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
bError = False
Select Case txtUser.Text
Case "user1"
If txtPass.Text <> "u1pass" Then bError = True _
Else
Columns("D").Hidden = False
Case "user2"
sSName = "u2sheet"
If txtPass.Text <> "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
' 'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If
Columns(sSName).Hidden = False
bOK2Use = True
Unload UserForm1
End If
End Sub
Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub
