Workbook/Macro/Worksheet Protection...

[Murph]

I've seen a couple posts on this but did not see any direct answers.

I have a workbook that has protected worksheets inside, I have a macro
inside one of those protected worksheets that I would like everyone to be
able to access.

When I share the workbook and click on the macro I get a run time error.

I have added the "ActiveSheet.Protect UserInterfaceOnly:=True" and that
seems to work fine if the workbook is not shared (unprotects the sheet, runs
the macro, then protects it again). What would cause it not to work once I
activate sharing? And is there anything I can do to correct?

Macro is here:
Sub Barcode()
ActiveSheet.Protect UserInterfaceOnly:=True
Dim ActRow As Integer
Dim Iloop As Integer
Dim shp As Shape
Dim rng As Range
Set shp = ActiveSheet.Shapes(Application.Caller)
Set rng = shp.TopLeftCell.Offset(0, 1)
rng.Value = Now

Application.ScreenUpdating = False
ActRow = rng.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 14
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(9)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B8")
.Font.Size = 30
With Worksheets("Counts").Range("B9")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B9").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False


End Sub

 

[Tom Ogilvy]

In help under limitations of a shared workbook:

[Feature Not allowed:]
"Protect or unprotect worksheets or the workbook "
[Results:]
"Existing protection remains in effect."

You are trying to change the protection settings.

 

[Murph]

bottom line - there's no way around it for a shared workbook?

damnit

In help under limitations of a shared workbook:

[Feature Not allowed:]
"Protect or unprotect worksheets or the workbook "
[Results:]
"Existing protection remains in effect."

You are trying to change the protection settings.

--
Regards,
Tom Ogilvy

I've seen a couple posts on this but did not see any direct answers.

I have a workbook that has protected worksheets inside, I have a macro
inside one of those protected worksheets that I would like everyone to be
able to access.

When I share the workbook and click on the macro I get a run time error.

I have added the "ActiveSheet.Protect UserInterfaceOnly:=True" and that
seems to work fine if the workbook is not shared (unprotects the sheet, runs
the macro, then protects it again). What would cause it not to work once I
activate sharing? And is there anything I can do to correct?

Macro is here:
Sub Barcode()
ActiveSheet.Protect UserInterfaceOnly:=True
Dim ActRow As Integer
Dim Iloop As Integer
Dim shp As Shape
Dim rng As Range
Set shp = ActiveSheet.Shapes(Application.Caller)
Set rng = shp.TopLeftCell.Offset(0, 1)
rng.Value = Now

Application.ScreenUpdating = False
ActRow = rng.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 14
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(9)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B8")
.Font.Size = 30
With Worksheets("Counts").Range("B9")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B9").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False


End Sub