Pause VBA Code For User Interaction

Oct 27, 2008

I have a situation where I have an msg box pop up that asks the user to identify whether or not the file they have chosen meets the standards needed to continue running the macro. Specifically, I need them to answer "Yes" if the file has a list of data in the first column, if there are no blanks in that list and if the first row of data begins on Row 4. If they answer yes, the macro continues ahead and does what it needs to. If the person selects "no," another input box pops up that asks the user to make the edits. This msg box asks the user to click "ok" when they have made those edits. I want the macro to pause after the person has selected "no" from the first msg box, so that the person can make those corrections. Then, I want the macro to resume when the user selects "OK" on the second msg box. How might I do this? I have included the piece of my code below.

strresponse2 = MsgBox("Please confirm that the file you have selected meets the following standards:" & vbNewLine & "1. The information in the first column of this file is all of the job titles or job codes associated with this profile." & vbNewLine & "2. From the first job code or title to the last, there are no blank rows in this first column of data." & vbNewLine & "3. The first job title or code appears on Row 4, Column 1." & vbNewLine & "If the file you selected meets these standards, select Yes. If the file you selected does not meet these requirements, select No.", vbYesNo, "Yes/No")
If strresponse = 6 Then
Call getjobtitle
End If
If strresponse = 7 Then
'pause macro
strresponse2 = MsgBox("Please make the necessary edits to this file. When you are done, select OK to continue generating your job profile.", vbOKOnly, "OK")
If strresponse = 0 Then
'resume macro
Call getjobtitle
End If
End If

ADVERTISEMENT

User Form Interaction

Jun 22, 2009

I have created a multi utility user form that user can initialize by a shortcut key. The problem if user want to see the content on sheet or want to change the active cell, he has to close the form and then do any action. This form is initialized by a command and default included in excel as a Add-in. Is there is any way to make the form floating i.e. user can manipulate the data and can see the form as well.

View 4 Replies View Related

Using MsgBox Without User Interaction

Nov 25, 2009

Is there a way to use MsgBox without any necessary user interaction to continue on with the macro? For example, I want to occasionally display a MsgBox for a few seconds containing relevant data and then have it disappear and the macro continue on without the user having to click the OK button, etc.

View 3 Replies View Related

How To Check For User Interaction Whenever Change Detected

Mar 9, 2013

I was told that the following code would enable my macro to execute every time the user interacts with anything Excel:

Code:
' Repeatedly check for user interaction whenever a change is detected
Private Sub Worksheet_Change(ByVal Target As Range)
Call TestMacro
End Sub

It doesn't work...

To put it into context, he is my entire program:

Code:
Sub TestMacro()'
' TestMacro Macro
'
' Create string variable
Dim undoText As String
' Assign the text to string variable

[Code] ......

My being new to VBA doesn't work in the situation as my programming instincts tell me to set up an infinite loop to continually check for interaction something like the following:

Code:
while( x == 1 )
{
do MY_CODE;
if( USER_CLOSES_EXCEL )
x == 0;
} // end while

However, Excel is apparently 'Event Driven' so I'd love to know how to use this to my advantage in keeping my macro running.

View 2 Replies View Related

How To Enable User Interaction While Speak Command Is Running

May 22, 2014

I have a cell (B1) in the second Sheet. It contains two sentences of text, which will be spoken when entering that Sheet (by a click in the previous Sheet).

Code:
Sub ButStart_Click()
Sheets(2).Select
Range("B1").Speak
End Sub

When the Speak command is running no other user interaction can be made. As the text is quite long not all the users want to wait until it is finished. So I need the users to be able to click on the "Next" button which takes them to the next Sheet (and start speaking the text there).

View 1 Replies View Related

Large File Taking Long Time To Close - No Interaction From User

Apr 18, 2012

I have a large file that takes a long time to close.

I would like to display a message that says " SAVING - PLEASE WAIT".

But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.

How can I do this?

View 7 Replies View Related

Pause Macro To Allow User To Select A Cell?

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

Want Macro To Pause, Allow User To Select A Range

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

Macro To Pause Code, Another To Resume

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

Pause Vba Code To Verify Calculations

Oct 10, 2007

I need to PAUSE my VBA code (about halfway through) in order to verify some calculations before I can allow the macro to finish running. If the numbers are correct, then I'd like the macro to finish running accordingly. However, if the numbers are incorrect, I need to be able to search my Excel file for the error (the error would be due to a format issue with the data that I pulled in), fix the error, and then let the macro finish running from the point it was paused.

Right now, I've got a Message Box that pops up asking me if the numbers are right and I have to answer YES or NO. I always answer NO so that the macro crashes -- this allows me to check the excel file without having to stop the macro. Once I've fixed the data problem, then I go back into my code and let it run to its conclusion.

View 6 Replies View Related

Pause Code To Send Keystrokes

Dec 6, 2007

I have searched and not found a solution. I have a project that requires the item to be printed on two different types of paper from a printer. The only way i have found to change trays is the sendkeys option. But it looks like sendkeys cannot send, then pause, then send. ex:

' first select the correct printer
Application.ActivePrinter = "\hagenCopy Room Savin 4045 PCL6 on Ne05:"
' next set change printing options, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}~%c3~", True
' next set change printing options to other color tray, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}{DOWN}~%c1~", True

How would one insert a working pause inbetween the two sendkeys (a wait just waits before the beginning of the first sendkey. and then the 2 try to execute at the same time calcelling both)

View 6 Replies View Related

Pause Macro And Wait For User To Click On Cell To Resume Macro?

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

Web Page Interaction

Dec 6, 2006

I would like to be able to turn the following worksheet in to an inspection scheduler. It already displays job information pertaining to the job & lot number entered into cells C2 & C3 in cells F2:F14. Now, I would like to write a script that will do the following...

If lets say I click on "Groundwork", it will know that I am trying to schedule a groundwork inspection for the permit number in cell F11, and it will know it needs to be scheduled through Fairfax County's website, because it says "FFX" in cell F2. (the only two counties I really deal with online are Loudoun & Fairfax...I'm not worried about the others). So it will then automatically jump to Fairfax County's website (links to the websites are in cells E22:E26), conduct the appropriate steps to schedule an inspection through that county, then kick back some sort of confirmation that it was scheduled. The same steps apply each time for each inspection type, but each county has their own steps....

View 14 Replies View Related

Interaction With Internet Explorer Using VBA

Nov 9, 2008

I want to write a piece of code to go to some webpage(URL in some excel cell) and then copy all the text on the page and paste to some other cell in the same worksheet.

I know I can open IE using VBA(Excel 2003),but is it possible to get everything on the IE window into the clipboard and then paste into any of the excel cells?

View 2 Replies View Related

How To Stop Interaction With Excel

Aug 27, 2012

While running one of my application, excel will be opened at the middle. I need to minimize the excel and stop interacting with it, which means even if I click the excel task bar it should not open excel. It should be always in minimized state.

I have an code which works in XP and is not working in Windows7.

Code Snippet: ( which works in XP )

ExcelObject.WindowState = xlMinimized
ExcelObject.Interactive = False

View 5 Replies View Related

VBA Calculate Pause

Mar 16, 2009

Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

View 7 Replies View Related

ListBox Code In User Form?

Nov 14, 2008

I'm playing with simple UserForms to get the hang of VBA. I was trying an example for the help files (Using xl2K), and this isn't working. My first challenge was figuring out that I needed to 'create' a UserForm to begin with.

View 2 Replies View Related

Code To Verify The User Name And Password

Apr 18, 2009

I have a code to verify the user name and password, what I want is counting the times the user enters wrong password and show a msgbox when he enters three continuous wrong passwords:

I have created the following lines which are part of a long code, the code is actually running without errors, but it is not showing the msgbox,

View 5 Replies View Related

Protect Source Code From User

Aug 26, 2009

how to protect source code from user or give the password when the user view code, right click on a sheet.

View 4 Replies View Related

VBA Code So User Cannot Save Workbook

Oct 11, 2013

I have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.

View 2 Replies View Related

Small Code Works For One User But Not Another

Oct 5, 2009

This snippet of code works fine when I run it. When another user uses this same workbook and runs it it gives an error:

Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
This is the line highlighted by excel:

ActiveSheet.Paste
I have tried changing it to:

Sheets("Data").Paste

View 9 Replies View Related

Pass Value From User Form To Code

Jun 24, 2006

I have created a custome User Form with three option buttons inside a frame. How do I set the value for each button and then how do I pass that value back to the code that opened the form (after closing the form upon a selection?

Sub Test2()
UserForm1.Show
Select Case List
Case 1
Range("b1").Value = "List 1"
Case 2
Range("B1").Value = "List 2"
Case 3
Range("B1").Value = "List 3"
End Select
End Sub

in the form I have the following code associated with each of the butons (but I do not think I have it right). Isn't there a way (becasue I have them in a frame) to shorten the code?.................

View 8 Replies View Related

Allow User To Disable Event Code

Dec 29, 2007

I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well.
The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file.
The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place.

The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mylogoff
Set mylogoff = Sheet1. Range("A65536").End(xlUp).Offset(0, 1)
mylogoff.FormulaR1C1 = "=NOW()"
mylogoff.Copy
mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = Fals
mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
Application.DisplayAlerts = False
Sheets("Start Here").Select
ThisWorkbook.Save
ThisWorkbook.Close
End Sub......................

View 9 Replies View Related

Add Pause Between Count And Boxes?

Feb 22, 2014

How can I add the pause between count & boxes?

VB:
s.Speak count & "boxes for scanning!"

View 6 Replies View Related

Pause Other Timers When Another Is Started?

Jul 31, 2014

I've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.

View 2 Replies View Related

Pause For 0.5 Seconds (half A Second)

Jun 3, 2009

I need to pause for 0.5 seconds (half a second). How can I do that. applicatiom.Wait can only be used for whole seconds and a FOR ... NEXT loop is CPU dependant.

View 3 Replies View Related

Pause Macro And Then Run It After 10 Minutes?

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

Pause Within A Loop Execution

Jun 10, 2007

I want to establish a loop in VB and within the loop I would like to pause execution of the loop and wait for input from a cell then resume execution of the loop once input has been entered.

View 9 Replies View Related

Pause Or Stop Macro

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

VB Code To Open A File That User Chooses

Mar 11, 2008

I'm trying to write a macro that will bring up the "File Open" dialog box, and allow the user to navigate to the directory where the file is located. It won't be the same file every time, and the files will likely have different names.

View 14 Replies View Related

VBA Code For Fliping Images In A User Form

Jun 9, 2009

find a VBA Code for fliping images in a User Form. What I want is that, I have a user form in which I have a command button "Next" & "Previous". I want whenever I click "Next" button my image control should show the next image & whenever I click "Previous" button my image control should show the previous image. I tried many logics but failed. Might be you guys will crack this hard nut.

View 7 Replies View Related

Code For Simple Checkbox On User Form.

Jan 21, 2010

I really don't know how to code a checkbox correctly. On the sheet, I have a form with CheckBox1 and Checkbox2. I would like Checkbox2 disabled until Checkbox1 is checked. Also, when Checkbox1 is checked, display Image1, otherwise Image1 is hidden. When CheckBox2 is enabled and checked, display Image2, otherwise Image2 is hidden. I know this is real beginner stuff, I'm still a rookie.

View 4 Replies View Related

Make Sure User Is Within Certain Rows Before Code Executes

Aug 14, 2007

I have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.

Here is the
Sub DropDown7_Change()
'Right here I want: If ActiveCell. is in Row 6-29 Then
Application.ActiveCell.Value = Range("D3").Value
Application.ActiveCell.Offset(, 1).Value = Range("F3").Value
Else Msg = "Move into the proper rows"
End Sub
How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.

View 9 Replies View Related

Pause In VBA Loop Then Restart From Where It Stopped

Jun 17, 2013

I'm looking for a simple solution to pause a VBA loop and then restart it from where it stopped.

Is it with a boolean value, i dont know the code.

View 9 Replies View Related

Excel Looping With Bloomberg And Pause

Apr 30, 2014

I have an excel workbook right now with a 75x75 correlation matrix. It has 75 stock prices and uses bloomberg to pull in the correlation between each of the holdings. There is a lot of data so it takes roughly 35 seconds for it all to get pulled in from bloomberg. The input value that I can change is the date. The output value is a specific correlation coefficient. Thus, if I input today's date into a cell in the file, it will run the correlation matrix and tell me the correlation between all the holdings using the past year's data. Then it will produce a single output value.

I am now looking to use VBA to make this more robust. What I have been trying to do is have a macro which will -

(1) input the current date into the input cell
(2) have the code wait 35 seconds (and since it is using bloomberg pull in the traditional wait methods do not work because they actually stop the data pull in also)
(3) copy the output value
(4) paste special that value in a different cell
(5) repeat the process but this time use the date from a day before and have the output value copied and pasted to a cell below the previous one.

This loop would continue over 20 times.

Below I have some code that allows me to do this process once but is not looped and I have been stuck trying to make this work with a loop given the bloomberg delay. When I add a loop, it does not wait the 35 seconds I need before doing the whole process again.

[Code] .....

View 2 Replies View Related

Pause Macro Execution Without Using Stop?

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

Can Pause A Running Macro Then Restart It

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

Macro Pause Then Resume By Keystroke

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

Adding Pause To Existing Macro?

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

Can I Pause And Then Start Recording Macro Again

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

Showcase Query: Pause Macro

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

ADVERTISEMENT