I have a 4 MB Excel file containing some very basic VBA that is shared on a server (it is a large file because it contains LOTS of "VLOOKUP" functions). But the VBA only works a part of the time. I assume the reason it might not work is because the server at that time was under high traffic and thus the VBA failures can be traced to poor server performance at that time.
View 4 RepliesI have a spreadsheet here that collect information through vb, now when the first agent is done with the information needed and click on the save command button the information is saved in the spreadsheet.
For example:
A | B | C | D | E
1 Date | Name | Birthdate | Gender | Age
2 11/20/11 | Greg White | 04/12/1977 | Male | 34
3 11/20/11 | Greg Blue | 04/12/1977 | Male | 34
4 11/20/11 | Greg Green | 04/12/1977 | Male | 34
5 11/20/11 | Greg Black | 04/12/1977 | Male | 34
Now theres no problem with this instance.... the problem arise when the second agent do the same thing..... when i check the spreadsheet the information was overwritten.
What really should be happening is that when the first agent click the save command button the spreadsheet will be saved so that when the next agent click the save button it will go to the next line.
Another thing that i noticed, since the spreadsheet is shared through the network, it takes time before the information is saved.
1. When Save button is clicked the spreadsheet will be saved.
2. A code (if there is any) to speed up the saving of shared excel file.
I am designing a workbook that techs in my lab can use from any of our network computers to submit requests for making chemical solutions they need to run their tests. Currently they have to fill out a paper form to make the request, so this should make things a little more efficient and less paper intensive.
I have the workbook designed and working, but now need to implement it as a shared file and this is where the problems are occurring. I assumed that shared files could allow access to the same file AT THE SAME TIME with no issues, but that is not the case in Excel97 on our network. Using two networked computers side by side I have tried updating the shared file at the same time and got a myriad of popups. The include:
1) Do you want to save the changes you made to "..."? (I did not include the actual filename here.)
2) A file named "......" already exists in this location. Do you want to replace it?
3) "......" is now available for editing. Choose Read-Write to open it for editing.
4) This file is locked. Try the command later.
5) This file has been locked by for saving. Try again later.
Question:
Are these error messages normal for shared files? Do some reflect issues more with the network than with Excel? Is there any way to get VBA to simulate clicking a Yes, No, or Cancel, or OK whenever a one of these specific messages comes up?
I 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 RelatedI have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.
View 4 Replies View RelatedWe have Excel 2003 at our workplace. I have shared a file, which is used by many users, so we can use the file at the same time. My problem is that if let's say I am writing a value in cell C2 and another user uses the same cell and writes another value there, and he saves the file and then I try to save the file as well, Excel will ask me which value to keep (the value written by the other user, or my value). Actually what I would need Excel to do is to save both, and put one under the other.
View 2 Replies View RelatedI 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 RelatedPlease follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.
View 14 Replies View Relatedhow to speed up a shared excel worksheet?
View 4 Replies View RelatedI have a master timesheet that i would like to update for several employees. Each employee will be sending their own spreadsheet at the end of the month. the columns in the spreadsheets have the date, the employee's name, the project they worked on, and how many hours they worked on that project.
when i receive all of the employee's spreadsheets (they will be emailing them to me) then i would like to import the data into a single table that i can then evaluate using pivot tables and pivot charts.
I tried using sharing and merging but i can't figure out how to have their information merge into a single table without overwriting each other (the numbers of rows with information is different for each employee and is not predictable - it will vary from month to month).
I would like to make this as automatic as possible. Ideally, they will send their spreadsheets to me and i would like to have their information automatically imported into one big table.
how to add ticker( splash roller message to shared file) like when the user opens any sheet in shared file there should be some rolling msg in the bottom.
View 9 Replies View RelatedI have a shared worksheet my self and a few others key pricing into weekly, more or less a price check for stores we service. I have created another workbook that has the same basic template with links to the "shared" worksheet. In this workbook Im trying to run a macro to copy the pricing "template" into a new tab in the workbook. I keep getting this error as it pertains to "sheet". I would like the tab that is created to be named whatever the current day is if possible.
Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Select
Range("A1:AM61").Select
Selection.Copy
Sheets("Template-Link").Select
Sheets.Add
Sheets("sheet").Select
How 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
I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.
View 4 Replies View RelatedI 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.
We 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)
I've set up a macro to run in the workbook open event, to open a user form that restricts user access to the spreadsheet. This works fine, but I need to make the spreadsheet shared and when I open it the macro doesn't run. it's still possible to use the workbook_open event on shared workbooks or if not suggest an alternative method of running the macro on opening.
View 2 Replies View RelatedWhen a supervisor goes into one of my sheets and adds some things in, she quite often forgot to share it again. To try and combat this I added the following code:-
Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim myworkb As String
myworkb:
myworkb = ThisWorkbook. Name
If myworkb = "SLE.MasterSheet.SLE1.xls" Then
Call NoProtection
ActiveWorkbook. SaveAs Filename:="SLE.Mastersheet.SLE1.xls", accessmode:=xlShared
End If
Call Limpa
Run "DeleteMenu"
End Sub
The code appears to do everything it should, and when I first ran it appeared to be ok. However, now it appears to save it as shared yet when you go back into the file it opens up as exclusive.... Is there a reason why the code wont "share" ?
I have a spreadsheet which users access through a shared workspace but often close excel without updating the shared workspace. Is there any code to update the shared workspace from vb?
View 5 Replies View RelatedWhich 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 Related1. 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)?
I have a spreadsheet of order lines with non-unique order numbers in the first column. Each order line has a new row but will have the same order number in the first column as other order lines in the same order. I have a list of order numbers that I need to find the total of the order lines in these specific orders numbers.
View 4 Replies View Relatedcan we change the author of shared excel and will this allow the changed author to make changes in no. of days to track history
View 6 Replies View Relatedon one of the computers where shared file is opened, the info is refreshed only if closing/re-open file
View 1 Replies View Relatedhave had to try to automate a function which will transfer all rows of data that an operator "checks" in a check box, to a seperate sheet before printing. Written under pressure with boss at my elbow so I will tidy later.
My problem is that there is a command within the macro (after the filtered data is copied over to Priority sheet) which will not allow the macro to fully execute when the file is shared.
Copy file attached.
Is 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 RelatedI am trying to write some code to sum all values from a given range, where the Month matches i.e. = "Jan" and where another cell = "Yes".
Ideally the code would be flexible as to the Month value, as this list already exists on the spreadsheet, so it can be looped through. Here is an example of my spreadsheet data that I use:
Month
Stock Cost
Invoiced?
Jan
£60.00
Yes
Jan
£50.00
Yes
I'd like the code to loop through all rows where month = set value, and only sum those values if column Invoiced? = "Yes".
Sub CalcReleaseCost()
Dim StockCost As Range
Dim Month As Range
Dim Invoiced As Range
Dim MonthTotal As Variant
Dim x As Range
[Code] ........
Whenever I share a workbook, I experience problems running macros that are problem-free otherwise. I’ve read the help menu about restrictions of using shared workbooks, but I did not run across anything that might explain this behavior. I’ve experimented with a few spreadsheets and I run across this problem in all cases. Can anyone help with an explanation or past experience? I’m assuming that by sharing a workbook, the workbook is automatically protected for both windows and structure. If this is what is causing the problem, is there code I can use to still allow my macros to run trouble free in a shared workbook?
View 9 Replies View RelatedOur 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'.
The shared worksheet resides on a secure network drive, so I can access it from anywhere. For years, I've had my secretary cut and paste out separate reports on individual salesman booking/commission performance. I thought taking advantage of Excel's native Pivot Table features, would save an awful lot of work, and probably lessen the chance of errors. Then I discovered that Pivot tables don't work with shared workbooks.
So I tried un-sharing the file. I discovered that the Pivot tables worked fine, but that I had to
re-create them ( seven sales guys, 4 independent geographical territories) each reporting period. The Pivot tables weren't dynamically updated each time additional orders were added to the main list (entry worksheet). I was very careful in laying out a new version of the "entry worksheet", and eliminating any unecessary column and rows. My immediate thought is that for the Pivot tables to work dynamically, I have to have dynamic ranges in the entry worksheet.