Locked Spreadsheet

[alexdurc09]

Hello,
I have written some VBA code but part of the code unlocksand then locks a spreadsheet again once the code has run. The idea behind thisis so that users aren’t able to mess with the raw data / delete entries withoutgoing through the correct procedure.

There is however a requirement to use filters on the master data to make searching easier but this can’t bedone because it is password protected and I don’t want to start giving out thepassword.

Is there any way I can allow users to be able to use thefilter function even when the spreadsheet is locked?
Thanks,
Alex

 

[Jaafar Tribak]

Hello,
I have written some VBA code but part of the code unlocksand then locks a spreadsheet again once the code has run. The idea behind thisis so that users aren’t able to mess with the raw data / delete entries withoutgoing through the correct procedure.

There is however a requirement to use filters on the master data to make searching easier but this can’t bedone because it is password protected and I don’t want to start giving out thepassword.

Is there any way I can allow users to be able to use thefilter function even when the spreadsheet is locked?
Thanks,
Alex

When locking the worksheet, there is an option on the list to allow automatic filter.

 

[alexdurc09]

Thanks for your reply - I have tried this but the next time the code runs and the sheet is unlocked and locked, it reverts back to having the allow automatic filter not selected

 

[Jaafar Tribak]

Thanks for your reply - I have tried this but the next time the code runs and the sheet is unlocked and locked, it reverts back to having the allow automatic filter not selected

You coul set the AllowFitering argument to TRUE when locking back the worksheet via code

Me.Protect AllowFiltering:=True

Also, do a search for UserInterFaceOnly.

 

[alexdurc09]

Having trouble with getting bot methods to work - how would I get them working with the below code? TIA

Private Sub UpdateRecord_Click()
Sheet1.Unprotect Password:="manlog"
Sheet7.Unprotect Password:="manlog"
If Me.Reg1.Value = "" Then
MsgBox "Container Number Can Not be Blank!", vbExclamation, "Container Number"
Exit Sub
End If
CONTAINERNUMBER = Me.Reg1.Value
Sheets("Containers").Select
Dim rowselect As Double
Dim findrow As Range
Dim lastRowHistory As Long

Set findrow = Worksheets("Containers").Range("A:A").Find(what:=Me.Reg1.Value, LookIn:=xlValues)
rowselect = findrow.Row
'move current record to history
lastRowHistory = Worksheets("Historical Records").Cells(Rows.Count, "A").End(xlUp).Row
lastRowHistory = lastRowHistory + 1
Rows(rowselect).Select
Selection.Copy
Sheets("Historical Records").Select
Rows(lastRowHistory).Select
ActiveSheet.Paste
Sheets("Containers").Select
'rowselect = Me.Reg1.Text
'rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 2) = Me.Reg2.Text
Cells(rowselect, 3) = Me.Reg3.Text
Cells(rowselect, 4) = Me.Reg4.Text
Cells(rowselect, 5) = Me.Reg5.Text
Cells(rowselect, 6) = Me.Reg6.Text
Cells(rowselect, 7) = Me.Reg7.Text
Cells(rowselect, 8) = Me.Reg8.Text
Cells(rowselect, 9) = Me.Reg9.Text
Cells(rowselect, 10) = Me.Reg10.Text
Cells(rowselect, 11) = Me.Reg11.Text
Cells(rowselect, 12) = Me.Reg12.Text
Cells(rowselect, 13) = Me.Reg13.Text
Cells(rowselect, 14) = Me.Reg14.Text
MsgBox "Container updated!"
Sheets("Main Screen").Select
Unload Me
Sheet1.Protect Password:="manlog"
Sheet7.Protect Password:="manlog"
End Sub