How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 RepliesI would like to place a command in a macro to have a workbook replicate itself WITHOUT replicating its macros. Is that possible? Is there a command? I am using Excel 2003.
View 9 Replies View RelatedIn a workbook with several worksheets i would like (as part of a backup function) make a copy of one of the worksheets.
The code of the complete project is distributed over a module (module1) and several Microsoft excel objects ("thisWorkbook", but also in a number of the worksheets (e.g. in "sheet3 (Risk Management Plan)").
The backup copy of the worksheet (in this example also containing e.g. the Microsoft excel object "sheet6 (Backup of Risk Management Plan)" should be free of any macro code. this to avoid unintended problems by inexperienced users, but also to keep the file size limited.
Probably it would be possible to copy the worksheet without of copying also the code. Or it would be possible to strip the code only from this particular worksheet without influencing any other code, forms, ... . Or, perhaps, there would even a totally different approach.
I use Windows XP pro Sp2, and Excel 2000 Sp3.
I prtected several sheets with the xlVeryHidden command I got from you. But when the user does not allow Macros to be executed, also these protections are not executed. Does there exist any solution to this problem? Something like "when macros are not allowed do NOT open file"?
Sub workbook_open()
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Application.AskToUpdateLinks = False
Windows("rechenfile.xls").Activate
Sheets("Daten").Visible = xlVeryHidden
Sheets("Koeffizient").Visible = xlVeryHidden
Sheets("Investitionen").Visible = xlVeryHidden
Sheets("Konsum").Visible = xlVeryHidden
Sheets("Zinssatz_permanent").Visible = xlVeryHidden
Sheets("Preise").Visible = xlVeryHidden
Sheets("Nettotransfers").Visible = xlVeryHidden
Sheets("Beschäftigung").Visible = xlVeryHidden
Sheets("Löhne").Visible = xlVeryHidden
Sheets("Nettoexporte").Visible = xlVeryHidden
End Sub
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook.
Sub Import_Macro()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
strRet = .Lines(1, .CountOfLines)
End With
Set wkb = Workbooks("Test Workbook.xls")
wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas")
wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas")
With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
.InsertLines 1, strRet '// Write Code
End With
End Sub
I need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.
View 5 Replies View RelatedAny way that two or more people can share and make changes on a excel workbook at the same time.
View 1 Replies View RelatedI have several workbooks that contain the same worksheets. The worksheets within the several workbooks are structurally the same but have a different number of elements within the structure of the worksheets. I've written Macros that accommodate the difference in the number of elements in the worksheets and will perform the same appropriate tasks within all workbooks.
I use one of the workbooks for development and as I make changes in the Macros within this development workbook, I wish to update all the macros in the other workbooks.
I've been Exporting and Importing to accomplish the update. Is there a utility available that will accomplish this task? Something like "Copy all Modules from "Workbook1.xls"
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
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?
What is the commands or script for deleting a macro automatically using
another workbook macro.
I need to create a macros to open up 4 other workbooks and run each individual workbooks macro. The work books can be called book 1 to 4 and the macros is call run in each sheet.
When I have tried it manages to open the sheet but not run the macro.
is it possible to protect a workbook but still run various macros, i have tried this and the macros wont run
View 10 Replies View RelatedI want to delete all macros from my workbook (so it will work well in Mac 2008). I went to Alt-F11 and deleted all Macros there and check every sheet and found no references. I did all that and when I try to open it in Excel for Mac it says there are still Macros.
How can I ensure my workbook has no macros?
I want to copy some macro's from one workbook to another one. But I don't want to copy all macro's to the new workbook. For example: I have 20 macro's, and only want to copy 2 of them every time.
View 7 Replies View RelatedI have made 7 macros for inserting a blank row above certain text, which are located in single column but in different row. 3 macros are for one sheet and 4 are for other. I am able to run them at once but the changes are getting done in only one sheet (which is active when I run macro)
Below is the code of macro:
Sub Space1()
Dim c As Range
For Each c In Range("D1:D60")
[Code]......
In addition to the code below that hides data if macros not enabled, prevents copy and cut (I know its still possible) I would like to as well as protect the sheets (which works fine) also protect the workbook to prevent moves etc. Unfortunately while the protection may be easily applied, the veryhidden bit to ensure macros are selected then conks out. Does anyone out there know of a way where say the workbook protection may remain and at the same time allow the veryhidden etc bits do their stuff as normal as if the workbook protection was off?
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cancel = True Or bIsClosing = False Then Run "HideAll"
If SaveAsUI = True Then Cancel = True
End Sub
Private Sub Workbook_Activate()
Dim oCtrl As Office.CommandBarControl
'Disable all Cut menus
For Each oCtrl In Application. CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
'oCtrl.Enabled = True
Next oCtrl...................
I want to run a recorded macro on the whole workbook at once, not just on the active worksheet.
View 3 Replies View RelatedI use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedIs there a way to prevent a workbook from opening unless macros are allowed?
View 2 Replies View RelatedOur department receives weekly and fortnighly updated workbooks from a number of different departments. Each workbook contains 10s of worksheets. Our department analyzes the data and generates reports from those worksheets.
I have developed a few macros to automate the report generation for making the computing of my colleagues easier.
Now, the problem is that all the macros are developed behind individual sheets and some modules. For this reason, my colleagues have to copy each updated sheet and paste over the older ones individually, each and every time. It is really quite time-consuming task - deleting older data from all the worksheets and then copying fresh data on each of them, one by one.
They want to make their life easier by just replacing the entire workbooks with the new ones and still want the macros functioning, as usual.
My concern is how I can put all the code in a separate workbook and how to reference data from the other workbook.
I have created an Excel Workbook and this Workbook has a VBA code that enforces an expiry date of the workbook (TimeBomb from cpearson.com). So, if the expiry date has passed, the workbook will not open. But since it is VBA related, the expiry date will work only if one has enabled macros. If macros are disabled, the workbook will open regardless of the date.
So, is there a way for Excel to check if macros are enabled? and is there a way for Excel not to open the workbook, if the macros are disabled?
I've used excel for awhile now but never really tapped into the tough stuff. I'm trying to create a workbook for work for Purchase Orders. I figured excel would be workable. The big thing I need is that for each worksheet that is created, I need it to automatically have the next P.O. number on the sheet. I have no idea if that is possible though. Each worksheet will be the same style but the P.O. would automatically change from say 105 to 106 when the new sheet is made. I was hoping maybe the date could automatically be entered as well. That doesn't have to happen but it could be helpful. Working with Excel 2003.
View 14 Replies View RelatedI'm trying to open a workbook that someone sends me once a week, and suddenly it's timing out (hourglass) and not opening.
I know for a fact that he put in some formulas (but no macros) that reference a file only he has access to.
And, auto calculation, he leaves on.
I see the file open on one of the 3 sheets in it, but the hourglass never goes away, and when I Ctrl Alt Del, it says Excel is Not Responding.
Is there any way I can get this file to open so that I can fix it or transfer the raw data to a new workbook?
First of I have a workbook, that runs several macros. All works fine. However I need to better secure the workbook. I realize that there is no guarantee on the security with excel but am hoping for something to solve the problem stated below.
When a user (with high security settings) opens the workbook in question, I would like the workbook to CLOSE out if the user chooses to disable macros when the asked by excel.
I have a "template" that runs a few processes and then saves the results in a new workbook...is there any way that I can have my vba code save the workbook without saving any of the vba code in the new workbook?
I would also like to exclude one of the worksheets from the new workbook if possible (but the first part is more important).
The current code I have is: ...
A macro to automaticlly run macros in my workbook when I schedule it to open through scheduler at a given time.
My son just upgraded my computer from 2000 to xp - a new application on a new hard drive. in the process, Excel was upgraded from 97 SR-1 to 97 SR-2.
My checkbook is on excel, and I really need to open it! We just moved to a new location, with the typical problems with email, internet etc.
I can open workbooks without macros. But I cannot open any with macros.
I get this message:
"Could not register the Visual Basic environment."
When that window is closed, the next one says:
"An error occured when initializing the VBA libraries (126)"
Is it possible to open a workbook via vba, but disable the macros? I know this sounds strange, but the workbook is coded to prevent users from saving it, and the only way to save it is to either open it with macros disabled or change the variable OKtoSave to true, but the variable is global only to the sheet and I can't find a way to change it through a macro written in my personal.xls workbook.
View 5 Replies View Related