Free Microsoft Excel 2013 Quick Reference

Unshare a workbook Results

Hi I have a workbook attached which works fine in all aspects expected of it. However when I share the workbook none of the macros function so I have had to leave it for now unshared.

The button macros which populate the chart on sheet 3 seem to be the achilles heel.

Any suggestions appreciated

Rgds Nigel

Hi All,

Reposted after thinking that people would think that l have not read that
the VBA code is unavailable in a shared workbook.

I have a workbook with a significant amount of VBA code which needs to be
shared. Using the following code (but using a password on sheet protection)
l am able to make the workbook 'unshared' on opening, do various
initialisation tasks, re-protect the worksheets and make the workbook shared
again. Everything l have tested so far works fine. However l now need to
make some enhancements and find that a lot of the facilities in the VBE are
'greyed out' even after unsharing the workbook. ie the Edit menu can be
accessed but everything except 'Bookmark' is greyed out. I vaguely remember
reading somewhere that sharing a workbook with sheets protected by a
passwords can cause problems, is this true?

Does anybody know what is causing this and what the solution is ?

I am running Windows XP and Excel 2003.

All contributions / suggestions gratefully received.

Private Sub Workbook_Open()

Dim Filename As String
Filename = ActiveWorkbook.Name
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code into the 'Shared' workbook - PART 1 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.UnprotectSharing
ActiveWorkbook.ExclusiveAccess
End If
Application.StatusBar = ("Please wait initialising workbook.")
Application.DisplayAlerts = True
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 1
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Unprotect
Next Sht1

MsgBox ("The sheets are unprotected")

For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Protect , userinterfaceonly:=True
Sht1.EnableAutoFilter = True
Next Sht1

MsgBox ("The sheets are protected")
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code to 'Shared' workbook - PART 2 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
accessMode:=xlShared
End If
Application.DisplayAlerts = True
Application.StatusBar = False
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox ("The workbook is Shared")

End Sub

Regards

Michael Beckinsale

Here's my dilemma;

I have a workbook which has a hidden worksheet where a password resides. My macro goes to this worksheet, collects the password and comes back to the current worksheet, unprotects it (with that password), imports data, and then reprotects the worksheet. Because the workbook is also protected, the sheet with the hidden password cannot be accessed.

Now I want the workbook to be "shared and protected" with "Track Changes". This will prevent the macro itself from being tampered with and will enable an audit trail of sorts on a 3rd worksheet within the workbook.

However, if the workbook is shared and protected, one needs to unshare and unprotect it first before unprotecting the worksheet that I need to import into. Sooooooooo,

I need to figure out how to (in VB) have the macro first unprotect and unshare the workbook (using the hidden password), then unprotect the worksheet (got that part figured out already), then reprotect the worksheet and the shared workbook. Any ideas on how I would do that?

I can send the current macro to those who wish to investigate.

Is there another way to protect the macros from being edited by someone with rights to the workbook?

Thanks in advance
ChemistB

I have shared workbooks. My code can unshare the workbook to unprotect
sheets and add named ranges. But I cannot convert the workbook back to
shared mode.

Can you help?

Here is the code I have written to put the workbook back into shared mode,
but it doesn't work. What am I doiung wrong?

With ActiveWorkbook
.KeepChangeHistory = True
.ChangeHistoryDuration = 1
End With
ActiveWorkbook.SaveAs Filename:= _
myProjectFolder & myProject , AccessMode:=xlShared
ActiveWindow.Close

Thank you for your assistance.
--
Don

Hi all,

i have a workbook with lots of macros that password protect and unprotect sheets. Workbook works fine when it is unshared.

Problem is when i share the workbook and run the same macros i keep getting the error message "run time error 1004: Unprotect method of worksheet class failed"
Surely excel allows shared workbooks to run scripts with password protection? It's annoying because you can't debug in shared workbooks neither! Grrr...

Any help appreciated ASAP because i wanted to go live with my program this evening.

Thanks,

Chris

I have a shared workbook with 2 'Protected sheets, one is being used as a
form to allow user to enter into fields and click on the button which prints
the sheet and enters the field data onto sheet 2.
The problem is that I need to first unshare the workbook complete the rest
of the tasks and then lastly turn the share workbook and close.
Can anyone pass on some code to turn off 'share workbook' and then turn back
on?
Thank you!

Hi All,

I have a workbook with a significant amount of VBA code which needs to be
shared. Using the following code (but using a password on sheet protection)
l am able to make the workbook 'unshared' on opening, do various
initialisation tasks, re-protect the worksheets and make the workbook shared
again. Everything l have tested so far works fine. However l now need to
make some enhancements and find that a lot of the facilities in the VBE are
'greyed out' even after unsharing the workbook. ie the Edit menu can be
accessed but everything except 'Bookmark' is greyed out. I vaguely remember
reading somewhere that sharing a workbook with sheets protected by a
passwords can cause problems, is this true?

Does anybody know what is causing this and what the solution is ?

I am running Windows XP and Excel 2003.

All contributions / suggestions gratefully received.

Private Sub Workbook_Open()

Dim Filename As String
Filename = ActiveWorkbook.Name
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code into the 'Shared' workbook - PART 1 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.UnprotectSharing
ActiveWorkbook.ExclusiveAccess
End If
Application.StatusBar = ("Please wait initialising workbook.")
Application.DisplayAlerts = True
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 1
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Unprotect
Next Sht1

MsgBox ("The sheets are unprotected")

For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Protect , userinterfaceonly:=True
Sht1.EnableAutoFilter = True
Next Sht1

MsgBox ("The sheets are protected")
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code to 'Shared' workbook - PART 2 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
accessMode:=xlShared
End If
Application.DisplayAlerts = True
Application.StatusBar = False
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox ("The workbook is Shared")

End Sub

Regards

Michael Beckinsale

Hi guys,

I'm using a spreadsheet shared over a network, and every so often when other users try to open it, it tells them it's unshared and I've locked it for editing? even though I haven't unshared the workbook, I re-share and everything is fine again for a while?

Does anyone know why this may be happening?

I have a shared workbook in Excel, with no track changes set, but which grows
rapidly in size.
The workbook is on a network drive and accessed by over 50 people.
All clients are Windows 2k.
The 'who has this workbook open now' has many entries, sometimes duplicated.
I understand this can be caused by network problems, although none are
reported.
Unsharing the workbook reduces the size back to near normal for data content.
Other shared workbooks do not have the same issue, although they are
admittedly accessed by a significantly smaller number of people.

Dear Experts

I have a call tracker in a shared excel workbook format.

This tracker is accesed by 4 to 5 person and is updated every 3 to 4 minutes depending upon the call flow.

Recently the systems have been upgraded to Excel 2007 and that when the problem started

Previously the tracker used to get corrupted ( ideal file size is 2 to 3 MB - and due to corruption the file size would inflate to 100 or higher MBs making slowing down the tracker functionality and finally coming to a stop - forcinmg me to re create another tracker )

even though it was an irritant - it used to happen once a month or so.

After the systems were upgraded to excel 2007

I first created the trackers with .xlsm extension ( macro mode ) and to my horror it crashed within a couple of days.

Then I recreated the trackers as a .xls extension and though it has not crashed yet , I can see the file size flutuatining around 20 mb - this is not normal because even at the end of the month with 1000 / 2000 entries these tracker should not be above 3 mb

My tracker has a macro to insert the date and time when a cell is selected and apart from that few basic formulas.

Please advise on whats causing this problem and what is the best way forward ( using an unshared excel workbook is out of the question and if I have to recreate a new tracker every two days then that would also be a great inconvinience )

If I dont get a proper solution then you would be responsible when I jump out of the window

Cross posted at http://www.mrexcel.com/forum/showthr...26#post2080526

Thanks

I have a "master workbook" set up with locked cells, Freeze Panes on F6, and woksheet-level protection with a password. Others copy this workbook, set it up for their own projects, and Share those workbooks among their team.

With the worksheet protected, the Freeze Panes button is disabled. But it seems a team member has found the Split button. Turning Split on and then off removes the freeze, but doesn't remove either the sharing or the protection. So to reset the Freeze Pane, I have to physically go to the location, unshare, unlock, Freeze Panes, re-lock, and share.

Much better if I could somehow disable the Split button, I think.
Any suggestions on making this happen?

Ed

I have a worksheet that is shared. It has hidden rows and columns.

Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared.....

How do I keep the rows and columns hidden?

Hi all

I'm wanting my macro to unshare any woorkbooks that I'm using. Something that the computer would read as "If target woorkbook is shared, unshare, if not, do nothing."

It's a simple macro where I mouse click on stuff and excel repeats the steps for me, I don't have any knowledge of writing in VBA though.

The files are sent to me from other locations within the business and the senders are not consistent with regards to sending a shared workbook one month and the next month it may not be shared.

Here is a sample of what the code looked like after I recorded the mouse clicks the first time.

	VB:
	
Windows("0451 WORKING FILE 0506.xls").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0451 WORKING FILE 0406.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0451 WORKING FILE 0306.XLS").Activate 
Windows("0436 WORKING FILE 0506.xls").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0436 WORKING FILE 0406.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0436 WORKING FILE 0306.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0197 WORKING FILE 0506.xls").Activate 
Windows("0197 WORKING FILE 0406.XLS").Activate 
Windows("0197 WORKING FILE 0306.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0285 WORKING FILE 0506.xls").Activate 
Windows("0285 WORKING FILE 0406.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 
Windows("0285 WORKING FILE 0306.XLS").Activate 
ActiveWorkbook.ExclusiveAccess 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help!

Hi all,

I have a slight dilemma,

I have inherited a shared workbook which needs some of the code ammending, the trouble is, i cannot switch off the share workbook feature!

I have tryed coping the file to a new location and then opening it so i am the only user but the "Allow workbook to be used by........." box is greyed out so i cannot switch off the sharing / get to the VBA

Any ideas?

i have a macro which updates a shared workbook , whilst the book is shared the macro hangs for about 5 mins, when the book is no shared it is instant , is there a way to make it run quicker as i need to have this book shared at all times, at the moment i am unsharing to update then sharing ?

Codes is below

Code:
Application.ScreenUpdating = False

Sheets("Output Tracker").Range("B17:E17").Copy
Sheets("HTB").Range("B2").PasteSpecial Paste:=xlPasteValues

If Sheets("HTB").Range("B6").Value  "" Then
    Sheets("HTB").Range("B2:E6").ClearContents
    Sheets("Output Tracker").Range("B17:E17").Copy
End If

For i = 2 To 6
    If Sheets("HTB").Range("B" & i).Value = "" Then
        Sheets("HTB").Range("B" & i).PasteSpecial Paste:=xlPasteValues
        Exit For
        
        
    End If
    
    
Next i

 ChDir "I:"
                  Workbooks.OpenText Filename:="I:S17.XLS", Origin:=xlWindows, StartRow:= _
            1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1)
            Cells.Select
    Selection.Copy
    
                   Windows("S17 Load  Capacity Master.xls").Activate
                  Sheets("MCPB").Select
            Cells.Select
      ActiveSheet.Paste
      
                  Range("C7").Select
                  
                        Application.CutCopyMode = False
                        
                               ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)"
                                     ActiveCell.FormulaR1C1 = "=-2.9+MID(RC[-1],5,8)"
                                    Selection.ClearContents
                                    
                               Range("C7").Select
                               
                              ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,8)"
                        Selection.AutoFill Destination:=Range("C7:C6555"), Type:=xlFillDefault
                    Range("C7:C6555").Select
              Sheets("Output Tracker").Select
         Windows("s17.xls").Activate
    ActiveWorkbook.Close
    
    
      Application.ScreenUpdating = True


End Sub

thanks 

Merc


I have a nice wpreadsheet in excel 2003 with 15 worksheets. Each worksheet is specific to site visits made by technicians to a particular customer.

in each sheet when a technician has been to a site, they enter the date of the visit in the date column.

I am trying to create a graph which will list all the dates when a vist was made to any of the sites - e.g. for 15 worksheets, and how many visits were done that day. so for example we may have made 6 visits to 6 different sites on 1st july 2008. so my x axis is to be date, and y axis, visits made on that day.

So far i have tried to do this by copying the date columns from each worksheet into one column, leaving gaps for future visits, and then creating a pivot table to count them. this sort of works but in a shared workbook it doesnt update until i unshare the workbook and refresh the table manually.

does someone have a much easier way of doing this?

thanks in advance!!!

Jools

Hello:

Our company utlizes a shared Excel 2003 workbook. When I unshare and make changes to the workbook, including hiding some colums in the worksheets. When workbook is shared again the columns that had been hidden in the individual worksheets are displayed again.

Any help or advice on how to solve this problem would be sincerely appreciated.

kawed

Hi, the below code works perfectly fine in a normal unshared workbook. But once I share the workbook (which I need to as its used concurrently by different people), I get this error "Run-time error 1004: unable to set the ColorIndex property of the Font class".

****************************************************************
Public Sub StartFlashing()
Application.OnTime EarliestTime:=Now() + TimeSerial(0, 0, 1), _
Procedure:="FlashText", _
Schedule:=True
End Sub

Public Sub FlashText()
Static bBoolean As Boolean
With ActiveWorkbook.Styles("Flash")
If bBoolean = True Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
Else
.Font.ColorIndex = 1
.Interior.ColorIndex = 2
End If
End With

With ActiveWorkbook.Styles("Flash_Var_Red")
If bBoolean = True Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
Else
.Font.ColorIndex = 1
.Interior.ColorIndex = 2
End If
End With

bBoolean = Not bBoolean
Application.OnTime EarliestTime:=Now() + TimeSerial(0, 0, 1.5), _
Procedure:="FlashText", _
Schedule:=True
End Sub
****************************************************************

I think its not letting me change the style settings. Any ideas on how I can overcome this problem?

Thanks in advance!!

I have a "Save" macro on my workbook that does all manner of things before closing the workbook down.

The workbook is a shared workbook.

What I would like to add into the code is for the macro to save a copy in a different folder but to save it as a normal workbook and not a shared one.

Any ideas?

Background:

I have several very complicated spreadsheets in excess of 30 MB. I noticed that when I took a copy of a worksheet, it ended up being stupidly big. This prompted me to use several cleanup macros available on the internet to resolve this, however, none of them worked.

This prompted me to delayer the spreadsheet to see where the problem lay:

I deleted every single cell (contents, formatting,……….)
I deleted all named ranges
I deleted all VB code
I unshared the workbook if applicable
I deleted all audit history

It should have been a blank fresh workbook, BUT, it was 1.5MB!

I only have one conclusion, since the workbooks are well used and complicated in nature that they naturally become corrupt?

If anyone can shed any light on the matter let me know, but, that’s a side issue.

The Solution

I started rebuilding one of the workbooks from scratch and everything is now reasonable. I then started writing a macro to do this automatically, however, I need the help of you guys to finish it off.

Step1:
Hopefully, the following code should be quite clear? I admit it’s not the most efficient.
It copies the used area of each worksheet from the corrupt workbook to a new workbook. I also copy over some other features such as column widths etc.

Code:
Sub WorkbookRebuild()

Dim OldWB As Workbook
Dim Rebuild As Workbook

Application.ScreenUpdating = False

Set OldWB = ActiveWorkbook
Workbooks.Add
Set Rebuild = ActiveWorkbook

Do While Rebuild.Sheets.Count < OldWB.Sheets.Count
    Rebuild.Activate
    Worksheets.Add
Loop

For x = 1 To OldWB.Sheets.Count

    OldWB.Activate
    Sheets(x).Activate
    With ActiveWindow
        Grid = .DisplayGridlines
        Zoo = .Zoom
    End With

    LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
    LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column

    Dim RH(65536)
    For i = 1 To LastRow
        RH(i) = Cells(i, 1).EntireRow.RowHeight 
    Next i

    Dim CW(256)
    For j = 1 To LastCol
        CW(j) = Cells(1, j).EntireColumn.ColumnWidth
    Next j

    Range(Range("A1"), Cells(LastRow, LastCol)).Copy
    Rebuild.Activate
    Sheets(x).Activate
    ActiveSheet.Paste

    With ActiveWindow
        .DisplayGridlines = Grid
        .Zoom = Zoo
    End With

    For i = 1 To LastRow
        Cells(i, 1).EntireRow.RowHeight = RH(i)
    Next i

    For j = 1 To LastCol
        Cells(1, j).EntireColumn.ColumnWidth = CW(j)
    Next j

    Rebuild.Sheets(x).Name = OldWB.Sheets(x).Name

    Next x

    Application.ScreenUpdating = True

End Sub
Step2:
I save the new workbook and via Edit >>> Edit Links, I change the links from the old workbook to the new current workbook. This ensures that there are no links to the old workbook which ultimately should be self-contained in the new version.

Step3:
Copy over all named ranges from the old workbook to the fresh rebuilt version

Step4:
Copy over all VB models from the old workbook to the fresh rebuilt version

I am currently doing steps 2 to 4 manually

I have run out of time, patience and in some cases knowledge to incorporate steps 2 to 4 into to a macro. So, can I put it to the floor for it to be completed?

Thanks in advance for any help.

Dan