Pause Macro, Allow Format, Resume Macro
Mar 14, 2007
how to interrupt VBA code, allow user modifications to a chart, and then continue the code where it left off with a click of a button. I currently have the code in place where a UserForm pops up at a specific point and tells the user to make adjustments and press OK when done. It works fine but the UserForm displays and the macro resumes when OK is clicked, but I can't figure out how to activate the chart or the spreadsheet to allow editing while that UserForm is on the screen. If only I can get excel to allow me to sleect the spreadsheet and make changes while the UserForm is on the screen, I would be set. Can anyone please help me with any options to allow editing while the UserForm is on the screen so I can edit and then click the OK when done?
I posted something similar to this over two years ago. The way I got around it at the time was to break the code into two separate subs. This time around, the code is making 17 graphs and I want to ask for editing for each one before continuing. That would be a lot of individual sections of code.
View 9 Replies
ADVERTISEMENT
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Nov 11, 2012
Is there a way a macro can be paused then resume running the macro by a keystroke, not a time delay.
View 3 Replies
View Related
Jul 27, 2007
I have a workbook with several hundred row the VBA code loops through. It takes about an hour to run all the calculations. Whenever I need to pause the script I hit ESC, which works well for me, but for other users it would be nice to have a command button that halts code. Even better would be another button to resume code.
View 9 Replies
View Related
Dec 18, 2007
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.
ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")
View 4 Replies
View Related
Oct 23, 2012
is there a code i can have where i can a pause a macro and then run it after 10 mins from that point?
View 5 Replies
View Related
Jun 24, 2006
I am running a macro that populates the same chart with variable data. I have approximately 40 sets of data and wish to pause or stop the macro to view one of the sets of data more closely. The pause option would be better but Stop option is acceptable
View 2 Replies
View Related
Jul 3, 2014
I have macros that often pause for the user to take some action -- for instance, to confirm that the correct cells are selected, or to delete something. Usually I use the "Stop" command, which opens the VBA editor; when I finish doing the manual action, I just resume the macro.
When writing macros for actual users, I'd like to do the same thing without their having to deal with the VBA window. Is there a way to pause and resume action and remain in the Excel window?
View 3 Replies
View Related
Mar 19, 2014
I've written a macro for work that will work with our billing process. After part of it has run, I'd like to pause it for the user to check a column for errors and possibly update a table to get rid of those errors, then after those errors are fixed, have the ability to continue with the rest of the code. I tried to make a message box to remind the user to look for the errors, but it wouldn't allow me to click anywhere except for on the buttons.
View 4 Replies
View Related
Mar 21, 2013
I would like to add a pause to the following macro so that it inserts the word CANCEL 1 second after triggering. I know I need to use the Application.Ontime function but cannot work out how to integrate it.
Code:
Sub Cancel() Application.ScreenUpdating = False
Sheets("Sheet1").Range("T5") = "CANCEL"
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Nov 17, 2006
I'm recording a Macro but I want to test certain section before I complete it, so can I pause the recording and then restart the recording again?
View 9 Replies
View Related
Nov 21, 2006
I am running a Showcase query from inside excel. I want the query to update, drop the data in excel save it and then close. The problem is that the save command is executing before the data is dropped. The Wait command pauses all processes so it won't work. Is there a way to have a minute of filler until the data is dropped so it will save?
View 5 Replies
View Related
Aug 15, 2014
I am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.
[Code].....
View 3 Replies
View Related
Feb 18, 2008
I would like to Pause a macro to allow the user to select a range on the spreadsheet and then click OK to continue with Macro.
I tried to use Msbbox, but I can't figure out how to allow user to access the spreadsheet to highlight the desired range.
View 9 Replies
View Related
Aug 4, 2007
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.
Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
View 6 Replies
View Related
Oct 26, 2012
Every month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
View 2 Replies
View Related
Oct 17, 2012
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies
View Related
Jul 26, 2014
I have tried to write code to format numbers to zero decimal places as well as to justify the format as the zeroes appears as 00000000 when imported.
I need the macro to do this on the first 7 sheets.
I also need ------- lines and ) to be cleared on the first 7 sheets.
I have attached sample data and my code below
Code:
Sub Format_Data()
Dim Cnt As Long, i As Long
Cnt = Sheets.Count
Application.DisplayAlerts = False
For i = Cnt To 7
Range("F:H").Select
With Sheets(i)
[code]....
View 1 Replies
View Related
Nov 15, 2009
In a macro, I calculate fees (Bold Blue format) on a sheet "Fees" then copy one value to sheet "All Vertical". The values come out regular and black. How can I copy the format aswell?
View 2 Replies
View Related
Jan 30, 2014
Below is a macro which i got from another thread.
Sub foo()
Select Case Weekday(Date, vbMonday)
Case 1: Range("B3").Value = Date - 3
Case Else: Range("B3").Value = Date - 1
End Select
End Sub
change the date format as mm-dd-yyyy.
View 9 Replies
View Related
Dec 20, 2007
I am recording a macro to help me with formatting a document. I know do this manually, but realized that if I record a macro and run it the process would be faster. I have tried this and it works really well. However the problem is that although the columns are always the same, the rows increase and decrease everytime I download the raw report.
When I record the macro and then run it, it only selects the number of rows that I choose when I record the macro. I am sure there is a way to have the macro look for date and then stop after there is no more rows, but I can't find it in help.
I have tried selecting the whole document, but that slows the automated formatting down significantly.
View 9 Replies
View Related
Aug 24, 2009
I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.
Is there a way to delete the last couple of lines of data of a spreadsheet?
View 9 Replies
View Related
Oct 11, 2009
I'm watching an educational D V D on my computer, and take notes in excel. I repeatedly use the print screen command, then go to excel and paste in the picture, though it is too large. I resize it to 50% and I'm done. What kind of code do I need to use to paste and shrink the picture to 50%? Also, how do I attach a shortcut key to this action (for example Ctrl+M)
View 14 Replies
View Related
Nov 20, 2009
i am getting is it only formats H11:AE12 & H14:AE15, It does not format the first range or any others. Am i doing something wrong? I recorded the first one H8:AE9 then just added the ranges for the others, have i missed something?
View 3 Replies
View Related
Mar 22, 2012
I've successfully managed to run a macro to format a .xls file on opening it. How can I do the same with a .csv file?
View 1 Replies
View Related
Jun 28, 2012
check and format some data with some code.
I have 100,000+ rows that look like this
Sheet1 *ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC
After208/01/201218:36:2008/01/201218:40:00T**308/01/201218:45:1208/01/201218:50:44T**408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C4681608/01/201221:48:4108/01/201222:02:36C8187708/01/201222:06:4608/01/201222:11:49C8787808/01/201222:15:5008/01/201223:02:00C87100909/01/201208:16:5109/01/201208:44:08T**1009/01/201208:45:1109/01/201208:49:55T**
I'm trying to merge all the "C" types in Column E into one row like this
Sheet1 *ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After1508/01/201218:36:2008/01/201218:40:00T**1608/01/201218:45:1208/01/201218:50:44T**1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T**1909/01/201208:45:1109/01/201208:49:55T**
So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.
I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.
View 2 Replies
View Related
Jul 5, 2012
I have a spreadsheet that uses a macro to format it. It’s quite simple and the code is shown below:
Code:
Range("A1:D2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
[Code] ...........
The spreadsheet will have info constantly added on (downloaded from a CSV).
What I would like the spreadsheet to do is look at the cells and only format those that have data in them but are not already formatted – otherwise I’ll just end up re-formatting everything every time and as the spreadsheet gets bigger it'll take longer and longer.
View 6 Replies
View Related
Jul 22, 2012
Macro or formula to reformat some data.
I have like data like this:
Column A = Date
Column B = Single Distance
Column C = Total Distance for that date
Sheet1 ABC1DateIndividual Distance Total distance 204/01/2011919304/01/20111019405/01/2011
212505/01/20111012606/01/2011926706/01/2011326806/01/20111026906/01/20112261006/01/2011226
It would be really useful if I could format it like this
Sheet1 FGHIJ1Total Individual 219910 312210 42631022
So basically, it puts all the information on one row for a particular date.
View 7 Replies
View Related
Sep 12, 2007
Im trying to get a macro that will set every sheet in the workbook i am usine, to match sheet1.
Sub LOOPY
Dim WS As Worksheet
'Loop through all worksheets
For Each WS In ActiveWorkbook.Worksheets
'Do this for all sheets except these sheets
If WS.Name = "Pcard Statement" Or WS.Name = "namedranges" Then
'Do Nothing
Else
'copy "pcard statement" page setups/print magins
Sheets(WS.Name).Range("A1").PasteSpecial Paste:=print margins?
End If
Next WS
Application.CutCopyMode = False
End Sub
View 9 Replies
View Related
Nov 27, 2008
From an accounting system I download txt files that then are read into Excel in order to be workable. The numbers come out like this 1.234,00 (ie decimal as comma separator and dot as thousand separator). This does not work in Sweden so I need to get rid of the dot.
I have built a macro that apart from taking out the dot makes some other changes to the data so that one wont have to do the same thing everytime ones downloads the same type of file. The problem is that when creating the macro or just doing the text to column, number format, replacing . with nothing it all works well but when I run the macro some of the numbers become text, others are ok and for some of them the macro does not only take out the dot but also the comma (which makes a huge difference). I have both Excel 2003 and 2007 and the same thing happens with both.....
View 9 Replies
View Related
