How to set a macro to input a password and not let the user know what the password is?

Hello kjy1989,

Using formulas to create a database is like the little pig that built his house from straw. While formulas are easy and familiar, they can not guard against the potential problems that databases encounter. It is more work to do it using VBA but provides you with a robust environment that will protect your database. Now your house is not only brick, it is reenforced with rebar.

This macro reads the database from a custom text file into a public collection named "Ingredients" and the file is then closed. The ingredient list is returned as a comma separate string when a recognized Order Code is used. This safeguards the database in several ways. The database file is read only using the macro preventing the user from changing anything. The database file is closed after being read which prevents accidental corruption of the data should Excel crash. Closing the file after reading also allows multiple users to have access to the database with no need for passwords.

ThisWorkbook Module Macro

' Thread:  http://www.excelforum.com/excel-programming-vba-macros/898038-how-to-set-a-macro-to-input-a-password-and-not-let-the-user-know-what-the-password-is.html
' Poster:  kjy1989
' Written: February 07, 2013
' Author:  Leith Ross www.excelforum.com

Public Ingredients As Collection
Public OrderCodeList As String

Sub LoadIngredients()

    Dim Filename As String
    Dim i As Long
    Dim Ingredient As Variant
    Dim n As Long
    Dim OrderCode As String
    Dim Text As String
    
        Filename = "Restaurant.dbt"
        
        Set Ingredients = New Collection
        OrderCodeList = ""
        
            Open Filename For Input Access Read As #1
                Do While Not EOF(1)
                    Line Input #1, Text
                    n = n + 1
                    
                    Select Case n
                        Case 1
                            If Text <> "Order Code|Ingredients:" Then
                                MsgBox "Wrong File Type - File Can Not Be Read!", vbCritical + vbOKOnly, "LoadIngredients"
                                GoTo EndMacro
                            End If
                            
                        Case 2
                            'Skip this line
                            
                        Case Else
                            i = InStr(1, Text, "|")
                            
                            If i = 0 Then
                                Ans = MsgBox("Syntax Error on Line #" & n & vbCrLf & vbCrLf _
                                    & "Do you want to continue?", vbQuestion + vbYexNo, "LoadIngredients")
                                    
                                If Ans = vbNo Then GoTo EndMacro
                            Else
                                OrderCode = Left(Text, i - 1)
                                Ingredient = Right(Text, Len(Text) - i)
                                
                                On Error Resume Next
                                
                                    Ingredients.Add Ingredient, OrderCode
                                    
                                    If Err <> 0 Then
                                        Ans = MsgBox("Duplicate Order Code on Line #" & n & vbCrLf & vbCrLf _
                                            & "Do you want to continue?", vbQuestion + vbYesNo, "LoadIngredients")
                                            
                                        If Ans = vbNo Then GoTo EndMacro
                                    Else
                                        If OrderCodeList = "" Then
                                            OrderCodeList = OrderCode
                                        Else
                                            OrderCodeList = OrderCodeList & "," & OrderCode
                                        End If
                                    End If
                                    
                                On Error GoTo 0
                            End If
                    End Select
                Loop
            Close #1
            
Exit Sub

EndMacro:
        Set Ingredients = Nothing
        OrderCodeList = ""
        Close #1
        
End Sub

The custom text file is named Restaurant.dbt. Currently, the macro looks for the this file in the same folder as the workbook. The first line identifies the file as the database file. The order code must be followed by a pipe character and then the ingrediants list which is separated by commas.

To edit this file use NotePad. When you select this file, Windows will ask you how you want to open the file. Select "Choose program" and select NotePad. You can make additional entries and save them. Just be sure to follow the format as outlined.

The macro will alert you to errors in the file and provide you with the line number in the file where the error was found. You will be asked if want to continue running the macro after an error has occurred.
Custom Database Text File

Order Code|Ingredients:
			
HTDG|Hotdog Relish,Chili,Cheese,Bun
TCO|Ground Beef,Lettuce,Tomato,Sauce,Taco Shell
HMBRG|Beef patty,Lettuce,Ketchup,Mayo,Bun
FRNCHF|Potatoes,Salt,Vegetable Oil,Cheese powder,Ground Beef