When I open up a new workbook it automatically contains 3 macros that I mistakenly added when I was experimenting a few months ago. How can I remove these macros from the default new workbook?
View 3 RepliesBasically, I want cell A1 to display the message "Macros must be enabled for this document to display properly", then have that text replaced with a picture (jpeg logo) if macros are enabled. I know I need to put the code in the Workbook_Open sub, but I keep getting the "error 1004, cannot modify visible property of picture object" when I try to tinker with "visible = true".
*EDIT* - The picture must be embedded in the spreadsheet somehow, and hidden from view when macros are disabled. (Having the picture crammed into a single cell at IV65536 is fine.)
Thoughts?
How can I change the default settings I get with a new Excel document?
What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.
I am not able to do these changes using settings in Option menu.
Currently, I have switched my referencing style back to the default mode; however, every time I restart Excel, it switches me back to the R1C1 Reference Style. Is there a method to prevent this, instead of unchecking the option every time?
View 8 Replies View RelatedHow to change these defaults...
It involved opening a new workbook and changing the number of worksheets in a new workbook, cell sizes, font, and font sizes... and then saving the workbook in one of the system folders under the name "workbook" to change the default workbook attributes, or "worksheet" to change default worksheet attributes.
I am trying to run multiple macros from multiple workbooks. I've figured out how to open each workbook, but I can't figure out how to run a certain macro in each workbook called "Main".
View 3 Replies View RelatedI have a series of 24 files (for non-profit grants), each of which contains a macro to create a consolidation file of 29 departments (for each grant). Instead of opening each file individually I would like to create a macro in a different file that would open each file and run the consolidation macro in each file. So far I have been unsuccessful in compiling a macro to complete this task.
View 7 Replies View RelatedI want to be able to run this many times looping through different file names. The only thing that changes between file names is the number.
Application.Run "'C:Documents and Settingspadi4545DesktopProjectRuns10.xls'!main"
ActiveWorkbook.Save
ActiveWindow.Close
The first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.
VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object
[Code].....
I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.
I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:
Application.Run "CodeWb.xlsm!DataFileOpen"
So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.
How can I use the code in CodeWb to handle all events occurring in DataWb?
Is there a way I can share the workbooks with macros built in the excel file?
View 1 Replies View RelatedIs there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
View 9 Replies View RelatedI have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedI have 4 different workbooks that I want to merge into one workbook. I tried moving the sheets from these workbooks into one workbook but to my surprise all the vba / macros didn't transfer over to the other workbook.
I searched here but all I can find were people that wanted to make a summary workbook or merge workbooks into a worksheet which isn't what i want to do.
I just want to simply merge these workbooks into one workbook without losing all the marco and vba stuff that are in each workbook.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
View 3 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
How can I force Excel to enter the time as PM instead of AM in a cell without typing PM or using military time? Is there a custom format that will do this?
View 9 Replies View RelatedI had set up a long time ago a macro enabled sheet in the XLSTART folder and Template folder. It used to work where any file I opened, if I clicked new sheet, the data from that file would be in sheet2. Now for some reason it's not showing up anymore and just a blank sheet appears.
View 2 Replies View RelatedHow can I set the value of a cell to give the current date when that cell is clicked or given focus. I want A5 to show as blank, nothing there, until I click the cell, or somehow select the cell, and then have it show the the current date. If I want that date I hit tab or enter and it will record it.
If I don't want to record anything i can arrow past it and it will not record anything. If I want to change it I double click or click again(If I'm in the cell already) and I can type over or just put in the date I want.
However If the user records a date in that cell and goes back to that cell do not record the default current date on tab or enter it most be double clicked in order to change it. This is a safety that they don't tab to it and past it and have it changed by mistake. Once a date is recorded they have to double click to change it.
This is not for just one cell but for the range of cells in colum A starting on A5.
I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?
View 9 Replies View RelatedNetworkdays on default takes saturday as a holiday. Is there a way I can undo this and have only sunday as a holiday while calculating net working days.
View 9 Replies View RelatedI want the combobox cmbBottle to show the second item in its list when the userform shows. The code below achieves this. However, after executing cmbbottle.listindex=1, the program jumps out of the initialization and goes to Private Sub cmbBottle_change() and then executes others subs called under it. I don't want this.
Is there a way to set the combobox to a default, startup display, without triggering the cmbBottle_change event?
(I have looked under the Properties window of combobox in the form view mode and can't see how to set it's value there.)
Private Sub UserForm_Initialize() ...
As per title, I have both Excel 2003 & 2007 installed - I need to set 2003 as the default for opening xls files when double-clicking but can't seem to override 2007 which wants to hog all the action. I've been through the File Types menu and selected the 2003 version of Excel as the default, but it doesn't make the slightest difference.
I have a combo box with a list of customers in a worksheet.
I would like to present "Please choose a customer" as a default once the user activate this sheet.