Excel unprotect workbook and make a change to range name

Conernesto
Conernesto used Ask the Experts™
on
I have an Excel workbook that is protected. The workbook password is AA12. I have a name range called EntityNumber. I need to change the EntityNumber. Is there Excel code that will

1 Allow me to select a workbook from a folder
2 Unprotect the workbook
3 Let me change the EntityNumber
4 Protect the workbook
5 let me save and close the file

Let me know if you need more information. Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Assuming the password is only to open the Excel file and the named range EntityNumber has a workbook scope, you may try something like this...
Sub UnprotectWorkbookAndChangeRangeValue()
Dim wb As Workbook
Dim strFileName As String

Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Excel File!"
    .Filters.Clear
    .Filters.Add "Excel File", "*.xls*"
    .AllowMultiSelect = False
    If .Show = -1 Then
        strFileName = .SelectedItems(1)
    Else
        MsgBox "You didn't select any file!", vbExclamation
        Exit Sub
    End If
End With
Set wb = Workbooks.Open(strFileName, False, False, , "AA12")
wb.Names("EntityNumber").RefersToRange.Value = "Your value here"    'Change value here
wb.Close True
Application.ScreenUpdating = True
MsgBox "Task completed!", vbInformation
End Sub

Open in new window

Author

Commented:
Thank you very much.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial