I have this code in my ThisWorkbook module:
Private Sub Workbook_Open()
With Application
.Calculation = xlManual
End With
End Sub
I want it to open the workbook without going through calculations (which take a long time)...
It sets to Manual, but still re-calcs upon opening...
how to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.
View 8 Replies View RelatedI'm trying to prevent a user from closing a perticular workbook.
Here is some important factors.
Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.
Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.
When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).
The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.
Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?
One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?
I have also included both workbooks so you can see all the code.
I have this code in Thisworkbook of workbook(B)
I have a spreadsheet on an intranet drive, and many people need to see it and enter information. Sometimes, we have the file opened on a computer, and the user walks away, and their station is locked. That prevents anyone from updating or using the file! locate a macro that will count how long the file is inactive, and then quit?
I'm not at all comfortable coding in VBA, and this sort of thing is out of my range!
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.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
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 have a large workbook 45MB with 40 sheets each containing a section that links to the server to update itself when the workbook calculates. With that being said it's impossible to have the workbook on Automatic calculate because the update process can take about 7-10 minutes so its set to manual. However, I would like to prevent the links from updating (its one link that is used to update the file) by possibly a macro that runs when the workbook opens and prevent the update link from running when I press F9 to manually calculate.
View 2 Replies View RelatedI am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.
I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.
I'm sure there is a function someplace for this that can be used in a macro.
Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
I need to stop the calculations when the workbook opens. I have tried
View 2 Replies View RelatedMy Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
View 5 Replies View RelatedI would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View Relatedam trying to create a workbook template which collects data from users with user forms and some macros. Users assign a project name when workbook opens and then they are able to save as the workbook with name of project name. But i don't want them to save on my template mistakenly. How do i prevent this? I tried workbook_before close sub but i can manage that it recommends to save workbook with project name and if you don't want, it still asks you if you want to save workbook before close
View 7 Replies View RelatedI display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.
In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.
I'm wondering if it is possible to prevent a user saving a workbook. If opened as a read-only, excel throws up an option to save with an alternative name. I'm wondering if I can put some code in the Workbook_BeforeSave event that prevents saving of the document unless the application.username is myself.
View 2 Replies View RelatedI want to prevent any Excel Workbook Theft through VBA
1. check the existence of any file in Windows directory ( which we do put for this security)
2. If it doesnt exist "pireted copy? close!" error message may apear
I know it is not fool proof. but it can gives 40% protection
I need to alter the Close command on the title bar (the 'X' on the top right corner on the blue bar of excel)
Is there any way to alter the close button on the title bar to have it call a sub I created instead of actually closing excel?
this is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -
View 2 Replies View RelatedCreate a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
View 9 Replies View RelatedI did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
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 have a workbook that contains macros. When the workbook is protected, the macro returns an error so I have unprotected the workbook but, if someone protects the workbook and saves it, it returns an error when closing and even if they choose cancel, it still screws up the macro and prevents it from working upon reopen.
Is there a way to prevent someone from protecting the workbook in vb code?
i am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
I've got a workbook that I want to keep intact in a central location on our local network, to make it easier for everybody to access, but don't want people to be able to save changes to the master workbook itself. I'd like to be able to disable the 'Save' feature while still allowing 'SaveAs'. The following code (installed in ThisWorkbook) results in the OPPOSITE of what I'm trying to accomplish (i.e., it allows 'Save' but disables 'SaveAs'):
Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
If SaveUI Then
MsgBox "The 'Save' function for this workbook has " & Chr(10) & "been disabled. Please use 'Save As'.", vbOKOnly + vbInformation, "Save Disabled"
Cancel = True
End If
End Sub
Obviously I'd also like to check the filename they're performing the 'SaveAs' under and disallow it if it matches the master filename.
I've thought about making the workbook into a template, which would sort of accomplish the same thing, but it would be much easier to just keep it as a workbook.
I have an Excel application in which I use the Workbook Open event to show a userform.
This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
[Code].....
I have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
Next
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub
The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..