I have a macro that runs when a spreadsheet is opened however i sometimes need it not to run. Rather then furiously hit escape i was hoping to get a dialog box that would prompt yes/no to run the macro and if an option was not chosen after a few second then to run the macro anyway (so basically only by clicking "no" would you stop the macro from running).
I found this code here that gives me the yes/no message box and the 5 second time-out to get rid of the message box, however if the message times out it doesn't run the macro. what i need to tweak to make it do so if no input comes from the user? [URL]
I use Excel 2007 and I have an .xls that I've had set to auto-open at startup and has worked fine for months now. Alll of a sudden a couple days ago it stopped auto-opening, and I have to now manually open the .xls. I looved in the Advanced>At Startup setting and it's set to the correct folder. What other setting in Excel 2007 do I need to check that would be causing this startup problem?
I am trying to insert a timer into a column cell range that will countdown in hours, mins, and sec, when I enter the time. example: when I enter the "time in hr,mins,sec" it will start to countdown to Zero automatically, when I hit the enter key.
There are two macros that i'd liek to add to my personal macro workbook upon startup of excel.
i tried to do them without coding but it appears that some actual coding is necessary.
1) i'd like to be able to select any # of cells anywhere on the page and hit ctrl+shift + n and have all of those selected cells read #N/A (the forumula is =na() )
2) i'd like to be able to select any number of columns (i do finance/econ research so all data is in chronological order from 1960-present along the columns. i.e. each variable is a column vector.) and be able to hit ctrl+shift+b and have VBA make a chart in a new sheet with my default settings
i've tried to do these but recording the macro hard codes the select cells for 1) and always runs into weird problems for 2).
So I am trying to make a file that when opened will act as a template does and change the file name on start-up.
Typically, the template changes the file name by appending one number (e.g. "Book Template REV 1.1.xltm' upon opening would change the file name to 'Book Template REV 1.11.xlsx'). What I want to do, however, is to custom the name change so I could use the file 'Book Template REV 1.1.xltm' while having it change the file name upon opening to 'Custom Name 567.xlsx', without a user being notified of the change (since typically when a template opens the file, the user has no notification that the file name has changed ever so slightly).
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows() Dim rCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Workbooks("latest.xls").Activate Range("formulas").Select Selection.Copy For Each rCell In Range("A3040:A5000") If Len(rCell) > 0 Then rCell.Activate ActiveCell.Offset(0, 2).Select ActiveSheet.Paste End If Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
I want the macro to run 20 seconds after data has been input to the worksheet. See below codes
Private Sub Worksheet_Change(ByVal Target As Excel.Range) ActiveSheet.Unprotect For Each cell In Range(Target.Address) If cell.Value "" Then cell.Locked = True
A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
What I am trying to do is have a macro that do a comparison between 2 cells every 100 milliseconds. One of the cell is constant updating from RTD (Real Time Data). And if its false, to nothing. However if true, I would want it to run some codes and exit the macro/procedure. I also need some way of stopping the macro at any time.
I have a worksheet that has macro events attached to command buttons. When a ‘Start’ command button is clicked, a timer begins counting in seconds and displays the value in cell B3 until a ‘Stop’ command button is clicked. The event works flawlessly as long as I don’t open up another workbook (to continue working in Excel).
When the second workbook is open…then time stops working in the workbook where the macro was created…and starts displaying the ‘timer count in seconds’ in the new active workbook--which overwrites/destroys the data in the newly opened workbook. When I go back to the original book that called the macro…it continues counting again.
I need this timer to continue running in the workbook with the command buttons (in the background) while I work on other worksheets in other books. Is there a way to keep the timer running no matter how many other workbooks are opened and prevent it from overwriting whatever sheet is active? This code in the Increment Sub below ‘overwrites’ the contents of cell B3 on whatever sheet is active---and stops the timer in the on the sheet with the ‘Start’ command button.
I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.
create a code which will measure this? I need to measure the time taken in 100ths of a second.
Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.
Dim Matlab As Object Dim MReal(10, 0) As Double Dim i As Integer Dim j As Integer Dim MImag() As Double Dim value As Double Dim RealValue As Double
I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Just a curios question is it possible to have a button on a macro that if you press it would run one set of instruction, but if the user was holding a key down say the shift key and pressed the same button, it would run a second set of instruction
EG If [Shift] key active then Run command 1 else Run command 2.
I finally completed a macro that would loop through selected workbooks in a folder and pull results to be shown in a new workbook.
This works fine by using a worksheet where the files are listed and in a1 of the worksheet, the path to the folder.
We have one workstation on the LAN with restricted access to the server, and therefore has mapped drives. Therefore the path to the folder containing the workbooks is different.
How could I instruct the macro when invoked - so in the case of that workstation - if the macro cannot find the files at the path stated in a1 of the workbook, it is to try a different location (The folder containing the workbooks is actually Drive:T)
I have done much searching, found some relevant, similar postings, but I have not been able to make it work for my code. I want to have multiple option buttons and have a macro determine which one is selected. One similar posting had macros run from the clicking on the option button, but I want the macro to determine which is clicked and then return some functions. From my research...this is my code.
Sub temp() If OptionButton1.Value = True Then Range("C1").Select Selection = "OB1" Else 'assuming two buttons and first is false Range("C1").Select Selection = "OB2" End If End Sub
This gives me an "Object Required" error on the first line of the code.
Im looking to create a macro that goes to a specific sheet on my workbook upon selecting an option. Example being I open the spreadsheet and a list of options come up such as:
BRIAN JOHN JAMES PAUL STEVEN
Upon selecting one of those names you are transferred to the sheet named "BRIAN", "JOHN", "JAMES" etc etc etc
I Have a tool which will be used online & offline.
I would like a box to appear on the opening of the spreadsheet which asks the user "Are you online?", with options "Yes" & "No", if the user selects "Yes" then a macro (which i have already written) refreshes the spreadsheet.
if the user selects "No", the sheet is not resfreshed.
The below code works well. However I would like to give the users an option to opt out of running the code when the message box appears.
Code: Sub delete_sheets() Beep MsgBox "This will delete All Sheets but the active sheet"
*****this is where I would like to use an additional message box or include it in the above message box an option for the user to cancel this code****
Range("XA1") = ActiveSheet.Name ActiveSheet.Name = ("Delete_Sheets") Dim ws As Worksheet Application.DisplayAlerts = False For Each ws In Worksheets If Not ws.Name = "Delete_Sheets" Then ws.Delete Next ws
A VB macro is created on a excel sheet. & assigne that macro to one button (by using Customise option) which is added on Menu bar.
So what i want to know is that is it possible to make the button works in all excel sheet.
Because when ever a new sheet is opened the button which i have added is visible. But when i click on that button the macro will not work or it opedns the original sheet on which macro was designed.
I'm done creating a macro. how can i do to compile it. I know there is an option in the tool bar, but i don't get any .exe file after clicking on that option. Do i need any compilator library or something?
I would like to create a pop up message that would automatically come up when someone opens one of my Excel workbooks. Essentially, I want to have some text regarding the confidentiality of the document, then require the user to click an "OK" button to continue.
How do I setup a STARTUP WIZARD in EXCEL which allows user can input information, and these information will be stored in the excel worksheet automatically.
The startup wizard is defined as a window which will pop up automatically when you open the Excel document. The wizard contains forms.
I have been modifying a workbook and the original macro will send to the sheet Results once the "Search" button is clicked, but I have found that this macro is displaying duplicate records. Can anyone help me put in an auto filter to find only unique records? My second workaround option is if someone can help me remove the go to/select sheet option from the "Search" macro so that a user will not automatically be sent to the Results tab and will instead hit the "Confirm Category Selection" button (which auto filters before sending the user to the Results tab).
I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.
The final thread is here: Message To Remind That Cell Is Mandatory
and the Macro used is:
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column = 11 Then If c.Value = "100 - Purchase Order In" Then MsgBox "Is the Month In correct?" End If End If If c.Column > 1 And c.Column < 18 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub
However...
We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!
Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
