PDA

View Full Version : [SOLVED] VBA Code not running if sheets are protected



Marsau
10-15-2019, 02:04 AM
Hi there all
I have three workbooks that are linked
If I protect the sheets the VBA code does not work
Specifically looking for problem in the monthly shopping list

Please assist

paulked
10-15-2019, 02:48 AM
Specifically looking for problem in the monthly shopping list
That's not specific, it's a needle in a haystack!!! Which workbook of the three? We could guess. Which code isn't running? What line does it fail on? Which sheets in which workbooks are you protecting? etc etc...

If you want help, please help us too!

Marsau
10-15-2019, 03:35 AM
In the monthly shopping list workbook
on the start menu sheet if i try to clear old menus, fill menu nos
and in the shopping list sheet if line 8 is protected and i use the dropdown months......the menu numbers dont update.

paulked
10-15-2019, 04:16 AM
I did:
Protected Shopping List Sheet
Start Menu sheet, chose Menu 24, Clear Old Menus, Fill Menu No's.
Shopping List sheet, changed Month to July

The data changed on the Shopping List sheet.

Marsau
10-15-2019, 04:22 AM
Protect sheets and try process again. then it doesnt work

paulked
10-15-2019, 05:34 AM
Which sheets? What doesn't work? I am not a mind reader and have no idea what is supposed to happen so "it doesn't work" is totally meaningless. It 'worked' for me because some numbers changed.

paulked
10-15-2019, 05:39 AM
What is 'normal' when working with protected sheets is:



Sub Ked()
Sheet1.Unprotect
'Do stuff on Sheet1
'...
Sheet1.Protect
End Sub

Marsau
10-15-2019, 07:23 AM
If you protect start menu and calendar menu, it does not allow me to make changes

was hoping that something like UserInterFaceOnly:=True would help

paulked
10-15-2019, 08:57 AM
Start Menu I can see, but Calendar Menu doesn't exist!

Marsau
10-15-2019, 09:04 AM
SORRY YOU ARE CORRECT.
IT WAS CHANGED TO YEAR PLANNER

paulked
10-15-2019, 09:23 AM
Sub Del12Months()
Dim i As Integer, j As Integer, r As Range, c As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual


With Worksheets("Year Planner")
'With ActiveSheet
.Unprotect
For i = 7 To 52 Step 15
'1st row of 3 months menus
Set r = Range(.Cells(i, "A"), .Cells(i, "W")).Offset(3)
For j = 0 To 10 Step 2
Set c = r.Offset(j)
c.ClearContents
Next j
Next i
.Protect
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub




An similar for any other subs

Marsau
10-15-2019, 09:58 AM
THANK YOU