I have a workbook with numerous macros in it and they are assigned to buttons in the different worksheets. I am trying to record a new macro and when I start recording and click on one of the other macros nothing happens. This wasn't a problem in excel 2003.
View 2 RepliesI have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.
However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:
Code:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkBook.PrecisionAsDisplayed = False
It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!
Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't work and anyway I loose things, like data validation, when I save it in Excel 2010.
Nothing else?
What settings do I need to change as it's handy to amend recorded code rather than write it all out.
I am using Excel2010.
My sheet displays values collected from a database via an AddIn. The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute. When the values exceed a certain limit, a sound is played from a custom Alarm function.
This all works as long as the excel window is active.
But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.
I currently use an Excel program (with macros) that run weather forecast data. It is compatible with versions XP, 2003, 2007 and 2010. However, I am trying to figure out if I can run it on a Mac. My plan is to run windows on my Mac through Parellels or VMWare Fusion.
Here is a link to the Excel Program: TX Tornado Forecast Worksheet
I have a macro that imports a report. In the header I'd like to display which day I imported the report, so I know I'm not looking at old data. I know I can use =TODAY() manually but I'd like to include it automatically in my macro.
View 7 Replies View RelatedI have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
I have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.
View 9 Replies View RelatedI have an excel file emailed to me everyday. It is a daily tracking of our fleet and I manipulate it before printing it and giving it to management. I dont know why when I try to record the macro and then use it it hides all columns.
The report has columns A-N. I hide B, F-G, I, M-N. When recording I hide multiple columns across.
I have done similar macros with other reports, no problem.
Is it possible to display both the current code line number and the total line count of a macro while it's running?
I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.
I Need to Understand Running Total Sum Formulas. I'm using Excel 2010. and I'm typing in the basic correct formula to arrive at a running total for each ajacent cell to the left of it.
I put in the correct formula in Cell (B1) of Sum=(A1)
I then put in the correct formula in Cell (B2) Sum=(A1:A2) I then highlight the A1 part of the formula with an F4 Key to lock it in.
I then drag the B2 Cell all the way down the excel page to capture all of my running total coming from the (A) Column just to the left of my formula.
Why even though I'm getting the answers I want in the running total does it put an error message in each of the correct answers in each of the cells in the (B) column where I put my formulas.
I tryed to delete the error message spot but I don't know how to delete all of the error spots without going into each cell one by one.
How can I get rid of all the error message out of each cell without having to click on each cell one by one to do it when I might have over 500 or more cells to click on.
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
[Code] .....
Untitled.png
I have a 'dashboard' worksheet in 2010 and want to be able to click on a cell in this sheet and have it take me to the assigned worksheet. They are all in the same workbook. e.g if i click on Leadership i want it to take me to the Leadership tab, Skills to take me to the Skills tab etc The 'dashboard' will be the only viewable sheet in the workbook until one of the items on the page has been clicked.
I have a very basic understanding of macros and I'm not sure if this would work or if i should use a formula instead?
I developed a tool in Excel 2010 using macros and launched it to a group of dozens of users. Turns out one of them uses a mac and says it is incompatible. I have never used Excel for Mac or tested my tool in it. Next I plan to test it on a mac but how to make this work?
View 4 Replies View RelatedHow/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
I am using excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
Excel is not responding to Shift - F9.
I'm using excel 2010 and have inherited an old workbook that has seen many version updates over the years. The "view macros" list displays approx 25 macros and I know that not all are currently being used or necessary.
The main tab contains macro control buttons. Any macro not assigned to one of these controls is not necessary (it's probably old and was just never removed).
How can I determine which macros are "unassigned" to a control or otherwise invalid?
formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7
View 2 Replies View RelatedIm trying to copy multiple workbooks and just save it into only one worksheet. I have 2000 diffrent workbooks with the diffrent amount of rows, The ammount of cells is the same and it dosent change.
im working on a excel 2010
This is what i got for the moment..
Sub LoopThroughDirectory()
Dim MyFile As String[code].....
I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.
Long story short, I will be saving several variations of this workbook every so often when I get new data.
The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.
Is it possible to assign relative macros to custom ribbons?
From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?
Using Excel 2010.
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String)
Dim rowctr As Long
Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
[Code] ..........
I have a 5K Excel 2010 workbook with VBA code that until yesterday, took about 30 sec. to run.
Today, after installation of WIndows7 Home Edition automatic updates, the running time has increased to about 4 min.
I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …
2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.
This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.
Is there a way for an administrator to lock out the Trust Center options for disabling all macros?
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedI have userform with date pickers and have text boxes overlaid on these, when I select todays date from the date picker it does not display the current date in the text box (I have 8 date pickers on the userform). If I select another date then reselect the current date it works. It has occasionally worked but why.
Below is the code for populating the text box from the Date Picker.
Private Sub DTPicker1_Change()
TextBox1.Value = DTPicker1.Value
End Sub
The initialize userform code uses the following to format and set the textbox
Code:
TextBox1.Value = Format(Date, "dd-mmm-yy")
TextBox1.Value = ""
Windows 7 with Excel 2010
I have a list of hyperlinks in excel with check boxes next to each, does anyone know how I can do a macro that will run another macro if the check box is checked and ignore the macro if it isnt checked???
View 3 Replies View RelatedI have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
does anyone know how this can be accomplished?
here is the ....