We help IT Professionals succeed at work.

Excel unprotect workbook and make a change to range name

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.
Watch Question

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
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.Add "Excel File", "*.xls*"
    .AllowMultiSelect = False
    If .Show = -1 Then
        strFileName = .SelectedItems(1)
        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


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

You're welcome!