Speed Up Shared Workbook?
Aug 19, 2013how to speed up a shared excel worksheet?
View 4 Replies
ADVERTISEMENT
Delete Sheets And Create New Workbook In Shared Workbook
Jun 2, 2014I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.
The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?
Changes To Shared Workbook
May 6, 2006I have an Excel workbook which is networked throughout our business. Its been in daily use for years with no problems. Every month I add a new sheet and to do that I untick the "allow changes by more than one user...etc". However this month the tick is greyed out and wont let me clear it. The pc's run windows XP professional with Excel 2003.
View 2 Replies View RelatedRemove [Shared] Workbook
Jul 22, 2009I have a workbook that is no longer being shared. I tried clicking on Tools > Shared Workbook. BUT i keep getting a message saying "The file C:CCTPAccountsSummary1.XLS cannot be found". Is there any way to kill this Shared workbook so I am free to modify everything?
View 2 Replies View RelatedCombobox In Shared Workbook
Dec 16, 2008Please follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.
View 14 Replies View RelatedShared Workbook Saving
Mar 5, 2010How do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below
HTML Sub Time()
Call Save
Application.OnTime Now + TimeValue("00:00:45"), "Time"
End Sub
HTML Sub Save()
ActiveWorkbook.Save
End Sub
UserInterFaceOnly In Shared Workbook
Nov 16, 2006I have a workbook that i am trying to make a shared workbook.
Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.
Once a workbook is shared it's protection cannot be changed.
I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).
Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.
Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.
Charts In Shared Workbook
Jan 9, 2007We have a worksheet, which contains charts and data which requires updating by different department.
However, when I have this work- book as a Shared workbook (Tools - share workbook), The charting and macro has all been deactivated.
Would anyone have any idea on how do I share a workbook - while being able to also update the Excel Charts within it ?
(Different user will be updating different charts, and there will be no conflict in between them)
VBA Save Workbook As Shared Workbook
Aug 30, 2012HTML Code:
Private Sub SaveAsAndShare()
ActiveWorkbook.SaveAs Filename:= _
"S:LdnPath1Path2Path32012Folder1Folder 2Aug12ReportName " & Format(Date, "MM-DD") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub
HTML Code:
ActiveWorkbook.SaveAs FileName, , , , , , xlShared
My code is the first bit, the 2nd code I found from another post, I want to add xlshared to my code above so it is shared when saved down.
Speed- How Do I Determine What Is Causing My Workbook To Be Slow
Apr 16, 2009I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
Sample Formulas: ...
Track Changes Without Switching To A Shared Workbook
May 21, 2009Which version of excel allows you to do a Track Changes without switching to a shared workbook? I am awared the Excel 2002 version allows Track Changes on the conditioned that the workbook is switch to shared mode.
View 3 Replies View RelatedShared Workbook Scrolling And Views
Oct 6, 20091. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
Personal View Of Shared Workbook?
Nov 8, 2011Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
View 2 Replies View RelatedShared Workbook: Revision History
Aug 18, 2009Our main spreadsheet in the office is a shared filed which is accessed by 3 pc's.
We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
Move Picture In Shared Workbook
Sep 8, 2006i have a workbook with picture and that workbook in share mode.
i have to move picture in share mode.
Protecting Cells In Shared Workbook
Oct 27, 2006I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
it is making a well laid out and good sheet look like something a child has done
I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
Macros Disabled In A Shared Workbook
Jan 17, 2007My macros don't work when the workbook is shared. They are not stored in my personal workbook and so are available to all users. What's going on?
View 3 Replies View RelatedCapture First User Of A Shared Workbook?
Feb 28, 2007Using Excel 2000, I have a shared workbook which is effectively a single sheet, flat database. Rows contain dates, and when certain dates occur, I want to send an email alert to specific users. I have searched and found lots of guidance on that aspect, but am wondering how best to trigger the email alert. At least one, and up to 10 users access the shared workbook at different times of day, and I only want to review the data and send the email alerts to the relevant users once per day.
I thought about a timed event, but cannot be sure the shared workbook will be open. I wondered whether an event in Personal.xls might do it, if I ensure I open Excel every day, but it's not ideal. Finally, I wondered if I could somehow trigger the alerts when the first user opens the workbook - but can't find how to capture that.
Advanced Filter In Shared Workbook
Jul 24, 2007Can you use or apply advanced filters in a shared workbook?
I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).
Sheets Missing In Shared Workbook
Sep 8, 2007SETUP: I have a shared workbook that is accessed by many people. It is designed to control the flow of work during the monthly close process. These processes are controlled by macros. There are 40 sheets in this workbook.
PROBLEM: On of my location's workbooks frequently loses half of it's sheets in the workbook. Counted in the neighborhood of 14 to 19 sheets go missing.
EFFORTS: I have attempted to Delete, Cut, Move, etc... these sheets with no success.
Only when I put the file in an exclusive state can I manipulate the sheets.
Macro Fails In Shared Workbook
Sep 25, 2007I have a workbook which I need to give shared access.
The workbook has macros which make sheets visible and VeryHidden and also give certain sheets protection. Also a filter runs on one sheet.
When I give the workbook shared access, the macros error?
how I can give me workbook shared access but still allow the macros to hide/unhide sheets, filter and protect worksheets?
Determine If Shared Workbook Open
Apr 6, 2008Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -
A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box
B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.
Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.
Function IsFileOpened(StrFilePath As String) As Integer
Dim FileNum As Integer
'First check filepath exists
If Len(Dir(StrFilePath)) > 0 Then
FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
If Err.Number <> 0 Then
IsFileOpened = 1 'File open
Else
IsFileOpened = 0 'File Closed
End If
Close FileNum
Else
IsFileOpened = 2 'File not found
End If
End Function
Conflicting Saved Changes In Shared Workbook
Apr 25, 2008I've designed a performance indicator (PI) recorder for up to 12 users, it has a userform allowing the users to input their data and a button to save the data and unload the user form. the data is saved using the following
Dim NextRow As Integer
On Error Resume Next 'skips line if workbook is not open
ActiveWorkbook. Sheets("DATA").Activate 'sets the "data" worksheets as active
NextRow = Worksheets("DATA"). Range("A" & Rows.Count).End(xlUp).Row + 1 ' finds the next empty row
With Worksheets("DATA").Range("A" & NextRow) 'inserts the data from the user form
there follows a series of offsets to slot the data into the correct column,
then this code to save the workbook
ActiveWorkbook.Sheets("reports").Activate
Application.DisplayAlerts = False ' suppress overwrite warning message
ActiveWorkbook.SaveAs "MY PATH"
CreateBackup=False
Application.DisplayAlerts = True
MsgBox "Spread sheet Saved"
The problem I'm having is that when the user then closes the excel application there is a conflict between the users 'saves' i.e. one ultimately will overwrite the other. is there a way round this ?? (this is in EXCEL 2000)
VBA - Change Calculation Settings Of Workbook To Speed Up Macro
Sep 29, 2011I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
Excel 2002 :: Limitations In Shared Workbook
Jul 20, 2013I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
Enable Outlining In A Shared/protected Workbook
Apr 22, 2009I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.
Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
After enabling 'Share Workbook' and re-opening the file it presents the following error:
Run-time error '1004': Method 'Protect' of object '_Worksheet' failed
Get Array Formula To Work In Shared Workbook?
Dec 12, 2012is there a a way to get an Array formula to work in a shared workbook? when this was put in the workbook It worked perfectly but I didn't think about the fact the workbook was going to be shared !
The formula is:
Code:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(S$3:S$40,SMALL(IF(COUNTIF(L$3:L$38,S$3:S$40)=0,ROW(S$3:S$40)),ROWS(T$3:T3))-ROW(S$3)+1)))
Double Click Event In Shared Workbook?
Jul 29, 2014I have a Shared Workbook that 10 or 15 users are in and out of all day. In Column A on a few sheets I have a Before Double Click Event that launches a userform. The macro works for all users except this one person. I have tried several things:
1. I closed the file and Reopened it, to make sure that the user did not disable macros.
2. I checked the file on other users computers to see if the file was working properly (It was.)
3. I went to Options>Trust Center>Trust Center Settings>Macro Settings and Enabled All Macros on this User's system
None of these actions corrected the issue.
The purpose of the BeforeDoubleClick Event is to store Columns A:E data and then let the user add more information through the form. Once the User fills out the UserForm. A:E is transferred to one of a number of sheets depending on criteria in the UserForm. A:E is transferred along with the new information that the user has entered in the UserForm.
Another symptom is that on other computers when the Before Double Click Event is activated the Userform launches and the cursor does not appear in the cell until after the User Clicks a Command Button to Update Data on the Userform. On this individual's computer the cursor does appear in the cell without the Userform launching....
Use Permitted Area Only In Shared Unprotected Workbook
Mar 28, 2008I have a worksheet that has headings along the and summary stats down the right and bottom of an area where details can be entered.
Depending on a persons user name columns are hidden/visible so that they can only enter information relevant to their section. The workbook is also shared.
What I want to do is stop people from being able to overwrite the headings or formula that calculate the stats but as the workbook is shared and the VBA that hides/unhides the columns needs the sheet to unprotected I am having to use code to prevent this: ....
Stability Issues Of Shared Workbook With VB Codes
Aug 27, 2008[I have two Shared Workbook accessed by multiple users. Both workbook has multiple users adding around 100 rows everyday and each book has 3 sheets - one for Request tracker- one for Call Tracking (which has the VB code) and 3d sheet has name range for the drop down menus]
1)Does a shared workbook become unstable if it has macro /VB in it?
I have two separate shared workbooks that are accessed by multiple users. After I added the macro one of these workbooks crashed and I got a file corruption error. I recreated that workbook and for almost 1 week it’s holding without issues.
2)I am using macro to make the trackers a bit more user friendly for my hardworking team , Some people say using macro is not advisable because over a period of time all macros become Virus and the file gets corrupted- Is there any truth to this ? I don’t want my important files to become unusable.
3)Is there a limit on how many macro/ VB scripts I can have in a workbook/sheet without making it unstable?
4)On an average how many rows of information can such a workbook take without out becoming unstable (eg 1000 or 2000 rows)
5)I am planning to combine both workbook into one – which would mean the users accessing the book would be double around 10 and the entries would also be double close to 500 rows of information everyday. On both sheets. Plus I plan to have more macros to automate most fields to ease the load on my team - Is that ok or a bad idea? (I would have a auto backup enabled )
6)Finally for the same workbook I am planning to have a more friendly FORM template to further ease the load on my team – can a form template take such a load [ I plan to have the form in Sheet I and the data inputed from the FORM would go to sheet 2 & 3 – would there be any issues with that ? There would be few automation macros like static time & date.
Coming to Backup workbook option during save process
1)Can I password protect a backup workbook – would the backup work in such a case? I don’t want someone to access the backup workbook accidentally and delete or input any data there (remember multiple users)
2)Does a Backup work book needs to be in the same folder as the original workbook - can’t I save the backup WB in a different folder in the same drive?
This is the macro I am using on sheet 2 of both workbook currently
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target(1, 2) = Time
Target(1, 3) = Date
Columns("B:C").AutoFit
End If
End Sub