Solved Using Password Macro to Show Parts of Sheet

May 21, 2019 at 13:46:12
Specs: Windows
I have a single sheet with a lot of information filling columns A-N and currently at 137 rows and counting. I have a few employees that access the sheet, each of them putting in different information into cells based off of their job role. To make things less cluttered, I was thinking of hiding columns based off of the user that is accessing the form.

I attempted using a dashboard page with a "Click Here to Begin" shape that I would assign a password prompt to. Depending on the password, the data sheet would show with only certain columns shown.

I have two problems here.

1: I can't make multiple passwords work. Only the first password works.
2: I can't find anywhere on this website or on Google if there is a way to hide certain columns only.

Help would be greatly appreciated. Here is the VBA I currently have.

Sub Useraccess()
Dim masterPW As String
Dim badPW As Integer

'Get Password
getPW:
   masterPW = Application.InputBox("Please Enter Your User PIN", vbOKCancel)
    
'Password
    If masterPW = "UserA" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate

    If masterPW = "UserB" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate      
      
    End If
    End If
End Sub

message edited by Steven4321


See More: Using Password Macro to Show Parts of Sheet

Report •

#1
May 21, 2019 at 15:58:09
✔ Best Answer
I don't have access to Excel right now, but here's a shot:

1: I can't make multiple passwords work. Only the first password works.

Move one of the End If's up

'Password
    If masterPW = "UserA" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate
    End If

    If masterPW = "UserB" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate      
      
    End If

or try ElseIf

'Password
    If masterPW = "UserA" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate

    ElseIf masterPW = "UserB" Then
      Sheets("2019").Visible = True
      Sheets("2019").Activate      
      
    End If

2: I can't find anywhere on this website or on Google if there is a way to hide certain columns only.

I'm surprised that you can't find that solution via Google. All I did was DAGS 'vba hide columns'. That query returned many, many hits.

'To Hide Columns B to D
Columns("B:D").EntireColumn.Hidden = True

'To UnHide Columns B to D
Columns("B:D").EntireColumn.Hidden = False

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
May 23, 2019 at 07:29:27
I was searching along the lines of "Excel password to show columns" and didn't find much helpful information. Just a matter of using the proper search terms.

I used your code and the additional passwords do work, as does the column hiding. However, the sheet is saving the form with altered column widths. For example, 'UserA' accesses the sheet and the VBA code hides columns D:P. Next, 'UserB' opens the form, but columns D:P are still shrunk column width regardless of what I set 'UserB' columns to. If I protect the columns and not allow column formatting, then the VBA code presents a "Run-time error '1004':" error and highlights this part of the code:
Columns("D:P").EntireColumn.Hidden = True

How do I set a default column width that it reverts to every time?

message edited by Steven4321


Report •

#3
May 23, 2019 at 23:26:21
object.ColumnWidth=30

You choose the object

Cells will refer to the entire Sheet or you could specify specific Columns.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
May 29, 2019 at 07:16:32
Works perfectly. Thank you.

Report •

#5
June 5, 2019 at 13:46:57
I have a follow up question regarding the same document. Is there an easy way to filter data and show only rows with target text when using the password process outlined above?

I've added in an AutoFilter section to the VBA and it does show only the row I want to see, but it adds filter drop downs to each column and allows people to change filter. Is there an easy fix for this?

This may help create the code line. Code should:
1: Search Column D for target text "xyz"
2: Show all rows with target text found and apply the hidden columns rule and width rule described in the original post.
3: Hide all other rows where target text is not found.
4: Do not display a drop-down filter selection at the top of each column.

message edited by Steven4321


Report •

Ask Question