How to allow Autofill dropdown selection to cells above or below on a protected sheet.

SWHarmon79

New Member
Joined
Aug 18, 2018
Messages
9
I have a workbook that is for internal estimating. The workbook itself has a large amount of formulas and tables that I don't want the user to be able to screw up. I have added protection through VBA to lock the workbook upon open with the below macro

Code:
Private Sub Workbook_Open()


Dim sht As Worksheet


'Loop through each Worksheet in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
  'Password Protect Current Sheet
      sht.Protect Password:="W3lcome2019", UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
  'Enable Group Collapse/Expand Capabilities
      sht.EnableOutlining = True
Next sht


If ThisWorkbook.Name <> "Estimate Template.xlsm" Then Exit Sub


Call Start_Estimate_Msgbox




End Sub

I also have an admin tab that has protect and unprotect buttons with the below macros

Code:
Sub Unprotect_Workbook()


'To Turn Off screen updating at the start of code. This stops the screen flashes while the code runs.
Application.ScreenUpdating = False




Dim sht As Worksheet


'Loop through each Worksheet in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
  'Password UnProtect Current Sheet
      sht.Unprotect Password:="W3lcome2019"


Next sht


'Return to starting sheet
      Sheet33.Select
      
'To Turn on at the end of the code.
Application.ScreenUpdating = True


End Sub

Code:
Sub Protect_Workbook()


Dim sht As Worksheet


'Loop through each Worksheet in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
  'Password Protect Current Sheet
      sht.Protect Password:="W3lcome2019", UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
  'Enable Group Collapse/Expand Capabilities
      sht.EnableOutlining = True
Next sht




End Sub

My issue now is that there are instances where the user has to enter a lot of identical information and would greatly benefit from being able to use autofill. The user can use autofill on blank cells but there is a dropdown and a dependent dropdown on each row. The user isn't able to use autofill on the dropdowns alone. The sheets only have the cells that have formulas protected and in the above macros I have allowed some other needed functionality but am unable to figure out or find a solution through the forum search or Google. I discovered a workaround that works everytime but isn't what I want. The workaround is as follows

The sheet in question has columns A:O and the sheet is broken into identical sections that are 24 rows long. Column A is a blank cell that the user enters in their description, Column B is a dropdown and Column C is a dependent dropdown. If the user doesn't use Column A then they can select the row needed and Columns A:C and are able to use the Autofill handle. This works but if the user does use Column A like they should every time then when using the above method it also obviously fills column A's content down as well. Then they have to just delete Column A data and enter the correct data. This is inconvenient and I would like to find a real solution.

I tried to include as much info as possible but if more is needed let me know. Thanks for any and all help.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,712
Members
409,785
Latest member
lalz1205

This Week's Hot Topics

Top