Dynamic Named Range used on multiple sheets

[izet99]

Hi, anybody know how I can use predefined dynamic named ranges across multiple sheet. Here is my scanario;

I have macro which locks/protects dynamic named range with password. Sheet include 5 dynamic ranges

1. User 1, Named Range1 protects all saved/entered data. Named Range2 Provide unlocked cell to user to enter new data.
2. User 2, Named Range3 Protects all saved/entered data. Named Range4 Provide unlocked cell to user to enter new data
3. User 3, Named Range5, admin range unlock all Named Ranges, in case any changes need to be make.

Macro works as intended for the sheet that I have set dynamic range... my question is how can I use the same named range across my other 17 sheets. Format of other sheet is exactly the same, so I don't need to setup 5 new ranges for each of 17 sheets.

Maybe there is another way to setup my code, I can provide example code if needed. Any support or help would be greatly appreciated.

ID

[SamT]

my question is how can I use the same named range across my other 17 sheets. Format of other sheet is exactly the same, so I don't need to setup 5 new ranges for each of 17 sheets.

AFAIK, it cna't be done.

Why not just used (Range) Variables. They can be "Dynamic" and are not restricted to any given worksheet, or even a particular workbook.
Go ahead and post your Procedure so we can make intelligent suggestions.

[izet99]

Hi SamT,

Here is code for above procedure I'm trying to modify/adjust to use across multiple sheets. I'm attaching test sheet I'm working off, passwords and ranges provided below.Workbook Protection v.2.xlsm

ThisWorkbook Module

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   LockUser GetUserInputRange(AllInputCells:=True)
End Sub


Private Sub Workbook_Open()
   UnlockInputCells
End Sub

Module 1

Option Explicit


'Admin Passwords
Public Const PWD_REAL As String = "test"            ' this is the password the worksheet is actually locked with
Public Const NAMED_ALL As String = "ADMIN_WS"       ' Named Range that includes all input cells


'User passwords
Public Const PWD_INPUT1 As String = "ppe"           ' password to unlock INITAL WORKSCOPE - NAMED_RANGE1
Public Const PWD_INPUT2 As String = "ppe1"          ' password to unlock UPDATE SECTION - NAMED_RANGE2
Public Const PWD_INPUT3 As String = "tech"          ' password to unlock STAMPED DATA - NAMED_RANGE3
Public Const PWD_INPUT4 As String = "tech1"         ' password to unlock EMPTY CELL - NAMED_RANGE3


'Name Ranges
Public Const NAMED_RANGE1 As String = "PPE_LOCK"          ' Named Range for user 1 (PPE INITAL WORKSCOPE)
Public Const NAMED_RANGE2 As String = "PPE_UNLOCK"        ' Named Range for user 2 (PPE NEW UPDATE ONLY)
Public Const NAMED_RANGE3 As String = "TECH_LOCK"         ' Named Range for user 3 (TECH SIGN OFF ON WS)
Public Const NAMED_RANGE4 As String = "TECH_UNLOCK"       ' Named Range for user 4 (TECH WIP ACTION ITEM)

Module 2

Option Explicit


Sub UnlockInputCells()
   UnlockUser GetUserInputRange
End Sub
Sub LockInputCells()
   LockUser GetUserInputRange
End Sub


'-----------------------------------------------
Function GetUserInputRange(Optional AllInputCells As Boolean = False) As Range
   Dim rng As Range, strInputMsg As String


   Set rng = Nothing
   strInputMsg = "Enter your password to edit." & vbCrLf & vbCrLf _
               & "Press cancel if you just want to look at the report."


   If AllInputCells Then
      Set rng = ThisWorkbook.Names(NAMED_ALL).RefersToRange
   Else
      Select Case InputBox(strInputMsg)
      
      Case PWD_INPUT1
      Set rng = ThisWorkbook.Names(NAMED_RANGE1).RefersToRange
      
      Case PWD_INPUT2
         Set rng = ThisWorkbook.Names(NAMED_RANGE2).RefersToRange
         
      Case PWD_INPUT3
         Set rng = ThisWorkbook.Names(NAMED_RANGE3).RefersToRange
         
      Case PWD_INPUT4
         Set rng = ThisWorkbook.Names(NAMED_RANGE4).RefersToRange
      
      Case PWD_REAL
         Set rng = ThisWorkbook.Names(NAMED_ALL).RefersToRange
      End Select
   End If


   Set GetUserInputRange = rng


End Function


Private Sub UnlockUser(rngInput As Range)
   Dim sht As Worksheet
   
   Set sht = ActiveSheet


   If Not rngInput Is Nothing Then


      ' unprotect the worksheet
      Set sht = rngInput.Parent
      sht.Unprotect PWD_REAL


      ' unlock given user input cells
      With rngInput
         .Locked = False
         .Interior.Color = XlRgbColor.rgbAliceBlue
         '.Range("A1").Select
      End With


      ' reprotect the worksheet
      sht.Protect PWD_REAL
      MsgBox "Your input cells have been unlocked."


   End If


End Sub


Sub LockUser(rngInput As Range)
   Dim sht As Worksheet
   
   Set sht = ActiveSheet


   If Not rngInput Is Nothing Then
      ' If the range includes locked and unlocked cells, .Locked returns Null
      If Not rngInput.Locked Or IsNull(rngInput.Locked) Then


         ' unprotect worksheet
         Set sht = rngInput.Parent
         sht.Unprotect PWD_REAL


         ' lock given user fields
         With rngInput
            .Locked = True
            .Interior.ColorIndex = xlColorIndexNone
         End With


         ' reprotect worksheet
         sht.Protect PWD_REAL


      End If
   End If


End Sub

ID

[SamT]

First, let me assure you that all coders regularly reject and rewrite in toto their first attempt at a project. I still expect to do so every time I start a new project, and I've been writing VBA for 13 years.

Second, let me say that I am very impressed by your code.

Third, you have so obfuscated the workbook that I can't discern its purpose is so I have no advice there.

Personally, I would

• Put Property Get and Let subs in the ThisWorkbook module to hold the Users Password.
• I would set that password when the Wbk was opened by moving "InputBox(strInputMsg)" out of Function GetUserInputRange.
• I would call Lock and Unlock ...InputCells by the Workbook's Sheet ... Activate and Deactivate events, passing the sheet to those subs
• In turn, I would have those subs pass the sheet on to Function GetUserInputRange.
• In Function GetUserInputRange I would use the Named Range Address assignment formulas as WorksheetFunctions to get the rng assignment for the Sheet passed in as a parameter.

ThisWorkbook code

Dim m_PW As String
Property Get PW() As String
PW = m_PW
End Property
Property Let PW()
m_Pw = LCase(PW) 'LCase allows case-insensitive passwords edit as needed
End Property

Some of GetUserInputRange(Sht As WorkSheet, Optional AllInputCells As Boolean = False) As Range code

Select Case Me.PW      
      Case PWD_INPUT1
Set rng = WorksheetFunction.OFFSET(Sht.Range("A1"),0,0,MATCH("*",Sht.Range("C:C"),-1),8)

Watch out!!. I did NOT take the time to ensure that the formula and password are correctly paired.

[izet99]

Hi SamT,

Thanks for replay, however as far code itself, I found it by googling, found on this site so credit where its due. I was searching and looking for code that would lock user entry or named range after input is saved and this code is closest I could get to my requirements.

I'm working on template for workscope/instruction to tech/mechanics on shop floor. When engine come for repair or overhaul, engine consist of 17 module and its disassembled into 17 different area. My template will keep track of progress and what is completed for each module... mechanics for each designated module will sign off on provided step by step instruction....

So, my requirement is I need protect initial written workscope to be protected so tech/mechanics guys can't change or modify, also when tech guys sign off, they can't go back and change their input after they save, or prevent somebody else to override somebody sign offs. So when file is released to shop and tech guys open file, he/she will open one of 17 sheet, enter password and his section, column I to K would unlock cells with no entry/blank. Any cell with entry would need to be locked to prevent them from changing as it might be completed by other user/tech.

Initial workscope instructions use A to H column, my range is based on column C, dynamic range defines my last used row in column C. This range will be locked when we release instruction to shop floor. While engine is in shop we might have updates/new instruction for mechanics but new instruction will be inserted below locked range. New instruction will be locked after user save file, as dynamic range will expand.

Tech/Mech sign off column is from column I to K, for each line of instruction, tech/mechanics will sign off with Ref #, user ID and date of completion.

I tried code above and work for one sheet and I think I could use but I would need to adapt to multiple sheet, so if it can be modify in such a way to be used with multiple sheet that would be great. I'll try follow your instruction tomorrow and see how it goes, not sure that I fully understand but I'll try your recommendation.

I hope, I explain well enough purpose and how it will be used, maybe with better understanding what and how it will be used, maybe there is a better way to do this!!!

Once again, thank you for your help and suggestion. Will try tomorrow and I'll see how it goes.

Regards,
Izet

[Jan Karel Pieterse]

The quickest way is to create sheet-level range names for each applicable worksheet. You can make that a simple process by downloading my Name Manager, use it to convert the first one to local and then use the List and Pickup buttons to first build the range names in a list (IN Excel) and then have Name Manager define them quickly for you. www.jkp-ads.com/officemarketplacenm-en.asp. The manual describes how to do this.

[SamT]

Jan's Name Manager is a godsend for those who use Named Ranges.

I would approach your project in a different way. It (the Project) looks to me more like a data base with 17 tables (Sheets) where a SuperUser selects which Rows normal Users can see and where normal Users can only enter new data into three Columns of only one Sheet, but not edit existing data.

I would use 18 workbooks: The Master book, with 17 Sheets, (with all possible details,) and 17 Module Level books. When the Boss (Super User) is done, the code in the Master Book adds only the required steps to each Module Level book. Only the single Sheet Module Level books are distributed to the normal Users.

I would then use dynamic UserForms for Data Entry in the Module Level books. This allows for each book to be customized for each Module's workstation. It also means that modifying the book to accommodate changes in Engines is very easy. Making the actual Worksheet xlVeryHidden keeps Users totally away from the Data itself. The DE Forms in these books are very similar and can be copied from book to book with only minor changes.

I would start this Project by designing one of the Module level sheets to be as complete as possible for that module without regard to any other module, except that all common columns would be at the start of the sheet; ie Column A for Super User check marks (to include this Row in the Module level Book,) and the three sign off columns would be in B, C. and D. From my house, it looks like Columns

• E would be major assembly names,
• F would be sub-assembly names, (May need more columns for sub-sub-assemblies.)
• G for Repair steps, (regardless of # of sub assemblies, all Repair steps should be in same column.)
• H thru as needed for repair instructions.

Do use Column Headers/Titles, but not Named Ranges

If you decide to try this method, upload a complete module sheet, and I will show you how to encode it for easy use with the dynamic UserForm.

[izet99]

Hi SamT,

You're on right track, that is what I kind of have if I understand you correctly.

I have one "Setup" sheet, super user input project information and selects level of repair for each module...when level of repair is selected, (Level 1 - 4), I have Index/Match multi conditional formula which populates module sheet with "work instruction". It populates module sheet with standard work instruction.

All work instructions are pulled from "Workscope Database" sheet, this sheet consisted of few thousands of line items based on product line, project type and level repair for each module.

So lets say, in Setup page
Super user select Level 1 repair for "ENG" module, than formula populates ENG sheet automatically based on level and project setup data...
If user select Level 4 for CFF module, than formula populates CFF module based on level and project setup data and so on for all other 17 sheets.

When engine is inducted for work, workscope template is shared over network, tech guys for each module access their sheet and sign off on work instruction as it gets completed. But since, work instruction is excel based I need to way to protect content of given instruction from attentional/unintentional changes to the sheet as well as accountability for performed work by sign off, once its sign off nobody can go back and update user ids and dates.

I have done some other automation, vba based action that is integrated into Setup sheet, like adding additional "Update/Revision section" after last used row, loading additional workinstraction, reference to different manual based on product line etc but this is all done upfront when project is setup.

I'm attaching sample template, its basically just layout, structure of the template. Hope this will clarify some questions.

SampleTemplate.xlsx

Regards,
Izet

[izet99]

Hi Jan,

I'll definitely take look and test it out and see how it work. I do use named ranges a quite a bit so this might be take a way some of my headaches. This might help me with my dynamic printing ranges that I have setup for each sheet for this project along with some other helper named ranges.

Thanks a lot for sharing it and providing link to it.

Regards,
Izet