• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 49
  • Last Modified:

Masking everycell in excel that is having a header as password or Pwd or pwd_Abc

Hi Team,

I want to mask each and every cell in excel, where the header of excel having a keyword like "Password", "Pwd", "pwd_Param1", "pwd_param2"etc...
uma p
uma p
  • 2
1 Solution
Wayne Taylor (webtubbs)Commented:
You can use this macro, which loops through the columns and masks those with "pwd" or "password" in the header text.

Sub MaskPasswordColumns()

    'protection of the sheet is required otherwise the password will still be visible in the formula bar
    'check if protection is in place already and unprotect is required
    Cells.Locked = False

    Dim col As Integer, HeaderRow As Integer
    HeaderRow = 1 ' the row the column headers are in
    For col = 1 To Cells(HeaderRow, Columns.Count).End(xlToLeft).Column
        If InStr(1, Cells(HeaderRow, col), "pwd", vbTextCompare) Or InStr(1, Cells(HeaderRow, col), "password", vbTextCompare) Then
            With Range(Cells(HeaderRow + 1, col), Cells(Rows.Count, col).End(xlUp))
                .NumberFormat = ";;;**"
                .FormulaHidden = True
                .Locked = True
            End With
        End If
    'protect the sheet again
End Sub

Open in new window

MurpheyApplication ConsultantCommented:
Hi uma p,

Wayne Taylor give you a solution to mask the passwords, but you can retrieve them anyway by "copy" and "past-as-value".

I'm almost sure that it is also NOT legal in your country to store passwords in any readable form.
The maximum you are (probably) to allowed to store is the Hashcount of the password,
so you are able to check "if StoredHash = Hash(password_entry) then...."
Wayne Taylor (webtubbs)Commented:
Question answered
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now