Split 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
If I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.
I cannot cut and paste from my working computer to the internet access computer.
This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.
WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.
Code: Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean Dim check_sheet as Worksheet On Error Resume Next If( workbook_ref Is Nothing ) Then Is_Workbook_Open = False Else Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error
[code].....
During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.
If Windows(vCriteria & "ISPR.XLS"). = True Then MsgBox "true", vbCritical, "Ferguson Enterprises, Inc." Else MsgBox "false", vbCritical, "Ferguson Enterprises, Inc." End If
thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?
how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.
All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.
Function IsWorkBookOpen(ByVal name As String) As Boolean Dim wBook As Workbook Dim isOpen As Boolean
wBook = Workbooks(name)
If wBook Is Nothing Then isOpen = False Else isOpen = True End If
'I can't reutrn the value of is open? Return isOpen
End Function
I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook Copy range (c8,d69) Close work sheet Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
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.
I 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?
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"
I 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.
Which 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.
1. 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)?
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.
Our 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'.
I 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
Using 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.
Can 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).
SETUP: 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.
I'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)
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I 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.
I 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
