Prevent Workbook Opening If Other Workbook Open & VBE
Aug 29, 2006how to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.
View 8 Replies
ADVERTISEMENT
Prevent A User From Opening Another Workbook
Feb 12, 2009How 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?
If Macros Are Diabled At Security Prompt, How To Prevent Workbook From Opening
Dec 15, 2008I am relying on macros for a lot of functionality and security.
Is there a way to stop a spreadsheet from being opened/accessed if a user disables macros at the security prompt?
Prevent Closing Of One Workbook When Two Are Open
Sep 23, 2009I'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)
Prevent Calculations Upon Workbook Open
Jan 16, 2007I 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...
Workbooks.Open Not Opening File Or Workbook
Oct 27, 2006I was wondering if anybody could catch an error in the following code. I have a file, let's call it a "Guide" which is opened by user and at this time this file has only one purpose - to help user choose the program. After clicking on the "Choose Program" command button, an Inputbox appears where the user types in a program. Then the code should open the file with this program and this is pretty much it. But something is not working with the code below, and I do not see anything wrong with it after looking at it for so long. It just does not do antthing. how to handle the "Choosing" option
Private Sub CmdBttn_ChooseProgram_Click()
Dim sFilename As String
On Error Goto ErrorHandler
sFilename = "Approval_" & Application.InputBox("Input Program")
Workbooks.Open Filename:="C:Documents and SettingsjsmithDesktop" & sFilename & ".xls"
ErrorHandler:
'if file name does not exist error message should display. Not defined yet
End Sub
Prevent Workbook Being Left Open Stopping Others Accessing
Feb 20, 2008I 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!
Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook
Apr 6, 2013I 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
Opening New Workbook And Pasting Values Into Current Workbook?
May 13, 2014I am using the below code (i have combined different bits of code which i found on these forums) to open a new workbook, copy all the data in it and paste it into Sheet2 in the active workbook . Also, i want the data to be pasted on the next empty row as there is already data in Sheet2. The data that is being copied and pasted has the same format and it is going to be repeated many times.
The problem i am having is that it is not offsetting the data to the next empty row - i think it is a simple change but i can't seem to get there :s
Also, the code is currently selecting all of the data from the workbook that i open - is there a way i can select all of the data, excluding the first row (the first row contains the headings which i already have in Sheet2 in the active workbook).
[Code].....
Copying Data From Userform To Another Workbook Without Opening Second Workbook
May 16, 2014I have created userform and it works fine. Following code assigned to 'SUBMIT' button in userform - works fine. I am trying to include code where certain data from userform is also copied to workbook2 ( of course without opening it)- as marked in red...below
[Code].....
Make Workbook Open- Open Correct Month Workbook
Jul 8, 2006I 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?
Getting Data Form Workbook Without Opening The Workbook
Apr 6, 2014Can I search a document without opening it?
If not how do I open a file if not already opened without using "ON ERROR"
Opening A Workbook Then Switching Back To First Workbook
Apr 25, 2009I am trying to make one of my macros open a file then switch back to the orignal file and then running the macro.
here is my
Save Data From One Workbook To Another Without Opening Workbook
Mar 1, 2013I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?
View 1 Replies View RelatedAutomatically Open New Workbook After 1st Workbook Open 4 Seconds
Nov 6, 2009I 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.
Copy Data From Workbook / Open Existing Workbook - Select Range And Paste
Mar 26, 2012Copy 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?
Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook
Jan 23, 2014I 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).
Making Backup Copy Of Active Workbook While Workbook Is Open
Jun 30, 2014I 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..
Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?
Dec 7, 2012Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
Open Workbook, Find Sheet That Contains Cell Value From Active Workbook
Jun 20, 2008I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
Open Workbook 2 From Hyperlink In Workbook 1 Breaks References?
Sep 17, 2012I have set up a workbook (wb2) that has external references to another workbook (wb1). All is good and works fine when you open the 2 files from windows explorer. I have then put a hyperlink to wb2 from wb1. When you click on this link the file opens but throws up errors saying some of the named ranges cannot be found?
View 3 Replies View RelatedCopying EMF Object From Closed Workbook Into Open Workbook
May 27, 2014Please see attached files.
Book1.xlsx
test.xlsm
I am trying to copy the emf image object from "Book1" into the "test" workbook whilst the test workbook is open and Book1 is closed. The code I have put together currently sort of works, I mean that it copies the text data over but does not copy the object which is what I require.
Transfer Data From Open Workbook To Closed Workbook
Dec 16, 2008im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"
to
closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6
Automatic Open Others Workbook When We Opened The Master Workbook
Aug 26, 2009How to Automatic open others Workbook when we opened the Master Workbook.
Examp.: I have a workbook (Master.xls), and when this file be opened, and then others workbook (Transactions.xls, Order.xls, etc.) is opened too.
Run Macro In Open Workbook When Workbook Becomes Activated Or Deactivated?
Apr 8, 2013Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
View 2 Replies View RelatedOpen Workbook Then Save As Different Workbook Dependant On A1
Feb 14, 2009is it possible to work on a workbook lets call it 123
once macro button is pressed it opens workbook abc
then saves it as what ever is in cell a1 to a directory?
Open Workbook Via Another Workbook And Not Display Userform
Mar 3, 2009I have a workbook ("Time") that when opened displays a userform. I also have another workbook ("Master") that opens the "Time" workbook to retrieve information. Is there a way to use the Master workbook to open the Time workbook WITHOUT displaying the userform.
View 2 Replies View RelatedAbort Workbook Open If Workbook Is Read Only.
Dec 12, 2009I've got several workbooks that users will be using to keep track of individual projects. These workbooks will talk back to a master summary sheet that has the status of all projects on it. The possibility exists that someone else on the network will have the summary book open when the program tries to update it, which would cause an error when it opens as read only. So I want to do something like this.
View 2 Replies View RelatedCopy From Closed Workbook To Open Workbook
Apr 25, 2007I'm trying to write a macro that accomplishes the following:
" Book 1" is already open. The user runs a macro that lists all .xls files in directory "d:measurements" The user selects the desired file from the list or box the macro copies from this "book 2" " sheet 3", " range A6:I107 and pastes (values only) into "book 1", "sheet 5", "range A6" End of macro.
I want to accomplish this without opening the selected file (book 2)
Opening A File Without Opening A Workbook
Apr 17, 2007I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
[url]
So the command is this:
Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.