Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String)
Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
Open path For Binary As #1
text = Space(LOF(1))
Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)
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.
Is it possible to write code that can identify how a workbook has been opened i.e. has the workbook been opened by another workbook or has it received a double click event on the workbook itself.
If it is possible I would like to place the code in the open event of the workbook that is being opened.
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?
I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate() If application.worksheet.lastsaved = msolastweek Then range("RANGE").clearcontents End If End Sub
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
I 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?
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 written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then nh_stringToDate = nh_stringToDate - 1462 End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
i want a macro that that 1+2 and with "vlookup" out the cells in "total". i start to record the macro but ... it's not work at all. i want to put an input box that ask the user "what month and year" and then put the relevant file in the vlookup.
I am unable to add which user made the change to the specific cell. The code used is:
Dim vOldVal 'Must be at top of module Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean If Target.Cells.Count > 1 Then Exit Sub On Error Resume Next With Application . ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula With Sheet1 . Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", " DATE OF CHANGE") End If..................................................
How can I prevent a user from opening another workbook in an Excel session "from the outside"?
I'm fairly new to VB. I'm developing (in VB 2003) a simple but SECURE Excel environment which will allow a user to update a hidden Master_Records workbook. I need to keep the user's Excel session secure...for example, I've disabled all Excel Toolbars and Command Buttons, effectively preventing the user from doing anything except filling in some cell values and clicking on a few custom buttons in the worksheet. But how can I prevent the user from opening another workbook into the active session from his desktop and introducing some malicious code into the session via that route?
One respondent in another forum (the only one, in fact) suggested that I look into "instantiating workbook level events" so that I can detect when other workbooks are open. I'm not sure what that means, is there someone here who could give me some guidance into that solution?
I wrote a code that is suppose to save the workbook every time a change is made but so far I have been unsuccessful at getting it to work. The workbook contians 7 worksheets.
This is the code I wrote to try and accomplish this task ....
I have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.
Need a way of quickly telling whther a user as a workbook open without trying to open the workbook yourself? I have many individual workbooks which I need to regularly update, before I can update them I have to check whether anybody else has them open and then wait until they are out before I can do the update.
if it is possible to prevent a workbook from being saved other than by a macro (So no saves on Ctrl + S or File Saves). Basically, I want to allow users to use a template that I have set up, but I do not want to allow them to be able to save the document.
Have 2 workbooks very similar and need to copy a working user form to newer workbook. Exported and Imported the user form to the new VBA project but can't figure how to bring user form into the new worksheet.
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.
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select". 2) Is there a way to select the workbook from were to copy the column without needing it to actually open? 3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
I've been tasked at work to make an excel file to do more than what I think I can figure out on my own. What I need done is this:
1. Every Monday, check excel file for data 2. If data exists, send email with said data
Here's my issues:
How do I get the workbook to open while logged off the computer? The file is stored on a share network drive, not locally. How do I get it to send an email without someone logged on to click "send"?
I tried using windows scheduler, but I don't have sufficient privileges to run as batch (IT restrictions).
I have a worksheet that contains about six different tabs. When this form is completed some users might want to have certain tabs copied into a different workbook. Unfortunately each user might want to copy different tabs. I was wondering if there was a Macro that I could write that would ask the users which tabs they want to copy into a new workbook.
I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open .... if open then msgbox " Book opened by user" end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
I'm trying to limit the controls users have on a specific workbook. Problem is I would like for them to have this workbook open throughout the day and if I look all the controls then they will be locked out of these controls on other seperate workbooks they open. The only reason I ask is because of some awesome code I just found by Dave Hawley. The code is...
Private Sub Workbook_SheetActivate(ByVal Sh As Object) With Application . CommandBars(1).Enabled = Not Sh. Name = "Sheet1" .CommandBars(" Formatting").Enabled = Not Sh.Name = "Sheet1" .CommandBars("Standard").Enabled = Not Sh.Name = "Sheet1" .DisplayFormulaBar = Not Sh.Name = "Sheet1" End With ActiveWindow.DisplayHeadings = Not Sh.Name = "Sheet1" End Sub
This works great with one exception. It will only block the menus for one Sheet. In my workbook I have 8 sheets and would like to apply this to 4 of them. Is there any way to modify code to hide menus on mores than one sheet. Also while I'm in the neighborhood. Is there a way to hide the sheet tabs for this workbook only. So once again if a user opened another workbook they would be there?
how to create user logins and passwords for five users for one workbook. Also, preferrably to have a message to prompt for entering the user name and password at the first worksheet (e.g. the main page where there it is usually the company logo page) when the workbook is open.
E.g. there are five employees and we would like to create five different logins and passwords for them. So when they open the workbook, it would be the first sheet of the workbook to appear first and they would then be prompt to enter their username and password. If one of the employees leave the company, we can still quite easily change the name to the new employee.
