Sharing a Workbook That Includes Macros

[Guest]

Hi,

I want to share a workbook that has macros and is on a network. The macros
(amongst other things) will protect and unprotect the sheet but once I make
the workbook shared I lose this functionality.

Can anyone offer any solutions?

 

[Debra Dalgleish]

You can't change the protection in a shared workbook. If you explain
what you're trying to do during the protect and unprotect, perhaps
someone could suggest a different way to accomplish the task.

 

[Guest]

I use a Workbook to control bookings in a restaurant. To eliminate the
possibility of less experienced users deleting data, deleting columns etc the
Worksheets are protected via password. When a new booking is made they click
on a macro button that will produce a user form for them to key the data into
and then press ok. The macro will then unprotect the worksheet
(ActiveSheet.Protect Password:="xxxxxx"), puts the correct data into the
correct fields an protect the worksheet again.

There are more things done but this is the first stumbling block. If I get
through this I should be fine to continue.

Thanks

 

[Debra Dalgleish]

You could store the data on a separate, hidden worksheet. Then, it
wouldn't have to be protected, and the new data could be added from the
user form.

There's an example here:

http://www.contextures.com/excelfiles.html

Under UserForms, look for Parts Database with Comboboxes

 

[Dave Peterson]

Search Excel's help for: "Features that are unavailable in shared workbooks".

You'll find that worksheet protection can't be changed.

I think you'll have to either compromise (take off protection or take off
sharing--or change your philosphy).

Maybe you could just hide that worksheet (leaving it unprotected) and have the
userform do everything you want.

 

[Guest]

Try this to auto hide your worksheets when you launc excel. Works well.
Default must be a blank sheetsheet with no data.

Sub Auto_Open()

On Error Resume Next
Sheets("Default").Select
Sheets("Default").Cells(1, 1).Select
ActiveWindow.DisplayWorkbookTabs = False

Dim sh As Worksheet ‘This will hide your worksheets, no need to preotect
For Each sh In Worksheets
sh.Visible = xlSheetVisible
Next

Call Login ‘This would be your login screen code


End Sub