pass variable from one workbook to another

[calebjill]

I have some workbooks that upon opening ask for a password. If the password
is incorrect or not supplied then it opens in a read only format. I have a
form that gets a username and password and if it is a certain password it
opens all the excel files in a folder. If this is the case I don't want each
file to open with the input box asking for the password. I want them all to
open in editable format. Can I pass the input box password and eliminate the
box all together. Here is the code for the workbook open which is on all the
excel workbooks that will be opening.

Private Sub Workbook_Open()
Dim psswd As Variant

psswd = InputBox("Please Click Enter", "Password", "Read Only")

If psswd = "j5rt342" Then

Else
'ThisWorkbook.ChangeFileAccess xlReadOnly
Application.Run "'master.xls'!selectingallworksheets"
Application.Run "'master.xls'!unprotecting"
Application.Run "'master.xls'!selectingallworksheets"
Range("A13:M21").Select
Range("A14").Activate
Selection.Locked = True
Selection.FormulaHidden = False
Application.Run "'master.xls'!protecting"
MsgBox ("The document is open in Read Only status, if you notice anything
wrong, please contact your administrator")
End If

End Sub

 

[Bernie Deitrick]

I would use an add-in file that has a place to store the password temporarily, and access it with
this, at the top of each workbook open event:

If Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value = "" Then
psswd = InputBox("Please Click Enter", "Password", "Read Only")
Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value = psswd
'prevent the user from being asked to overwrite the add-in because of the change
Workbooks("Add in name.xla").Saved = True
Else
psswd = Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value
End If

If psswd = "j5rt342" Then
'Other Code


When you create the add-in just make sure that the storage cell is blank prior to saving.

HTH,
Bernie
MS Excel MVP

 

[calebjill]

Worked great. Thanks

I would use an add-in file that has a place to store the password temporarily, and access it with
this, at the top of each workbook open event:

If Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value = "" Then
psswd = InputBox("Please Click Enter", "Password", "Read Only")
Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value = psswd
'prevent the user from being asked to overwrite the add-in because of the change
Workbooks("Add in name.xla").Saved = True
Else
psswd = Workbooks("Add in name.xla").Worksheets("Sheet name").Range("A1").Value
End If

If psswd = "j5rt342" Then
'Other Code


When you create the add-in just make sure that the storage cell is blank prior to saving.

HTH,
Bernie
MS Excel MVP