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.
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].....
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.
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")
I have a protected worksheet with a mixture of locked and unlocked cells, and I'd like to provide an option to allow the user to select a large range of mixed (locked and unlocked) cells and, in the unlocked cells only, pre-populate with a standard formula.
I've got most of it working, but what I need is the specific code that allows the user to specify the range of cells they want the macro to act on.
Using a script but when I use the given Range it is still using the complete WorSheet.
I cant seem to get to work together in the same macro but that work great seperated. I need them to be in the same macro. The first just simply copy's text from one workbook to another:
Sub Test2()
ActiveSheet. Range("a1").Copy _
Destination:=Workbooks("punchlist.xls"). Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1,0)
End Sub
That worked ok but I needed to change it to the "active cell" instead of cell "a1". So then this line of code was made:
Dim userInputCell As Range
On Error Resume Next
Set userInputCell = Application.InputBox("Use the mouse to select a cell on any sheet", type:=8)
On Error Goto 0
If userInputCell Is Nothing Then
MsgBox "Cancel pressed"
Else
Msgbox "You selected " & userInputCell.Address(,,,True)
End If
The second code works just the way I want it but it doesnt copy over the text to the other workbook. I assume the 2 codes need to be together but I cant get it to work without errors. I also need the text to copy over without changing the borders on either workbook.
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
I'm trying to get a users' selected range to display in a cell as stored text.
therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7"
w/o the quotes.
I've attached an example workbook.
Here's the formula thus far:
Sub SelectRangeBox()
Dim rnBody As Range
Dim vaMsg As Variant
Do
vaMsg = Application.InputBox( _
Prompt:="Please enter the message-text:", _
Title:="Message", _
Type:=2)
Loop While vaMsg = ""................................
Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
******** ******************** ************************************************************************>Microsoft Excel - Rod Extract Test Master.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutCR1=CRCSCTCUCV1Loan NumberCollect Tax EscCollect Oth EscPay Int on EscRE Tax Monthly Const2304100830NNY$0.003304100830NNN$0.00Borrower,Master,ARM [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I need to have happen is that the user decides what duplicate row to delete. The reason that there is a duplicate is that Row 1 has something different from Row 2. In my example above, CU1 is different from CU2. This is what caused this loan number from CR to be duplicated. I need to delete at my discretion one of the 2 rows.
What I would like is to have the user Select Range CR1:CR2 (this is relative in the sheet), run the macro, have it select the 2 row range, compare (relative) B1:B2 are they equal, compare C1:C2, are they equal, etc through the rest of the range. I would like the screen to update and stop with the range highlighted that is and and a MsgBox to click ok to Continue, then continue through the rest of the range. Once it has finished cheking the range, can the user use the mouse to select the range from CR that needs to be deleted, have the appropriate range selected and then the MsgBox Is this the range you want to delete? Y/N........................
I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?
View 2 Replies View RelatedI need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.
What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.
This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.
I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.
I have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
[Code] .......
I was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run.
This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable.
What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there?
To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
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 RelatedI 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 RelatedI 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?
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 RelatedIs there a way a macro can be paused then resume running the macro by a keystroke, not a time delay.
View 3 Replies View RelatedI 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
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 RelatedI 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 RelatedI 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 Relatedhow 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.
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
View 5 Replies View Relatedrange("A1").select
can I replace A1 with l where
l = worksheets("List").cells(3,2)
?
it doesnt like it, is there a better solution?
I'm in charge of taking a report; streamlining its functions; and automating future reporting.
I'm using a database and some of the reporting function Access has. For another variation I'm forced to export the query to excel and transpose the vertical layout horizontally.
I'm making the entire process automated via a macro. The code is too long to post in its entirety but here is an example of what I'm trying to accomplish:
I am looking for a macro that will allow me to select the range of A5 to the last column with data in row 5
View 9 Replies View RelatedI have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
I have made a sheet where the user can insert/delete rows in a certain intervall of rows. The upper row is 17, that is I have named the cell "First" and then the last row "Last" and then I am using integers:
Dim intStartrow As Integer
Dim intLastrow As Integer
intStartrow = ActiveSheet.Range("First").Row + 1
intLastrow = ActiveSheet.Range("Last").Row - 1
So it is only possible to add/delete rows if the markerer is in between the above rows.
Now I would like to select the rows, from first to last. Since the rows are never the same, users add and delete rows all the time, I can not use
Range("A17:Z49").Select
Can I somehow make a range selection using the Integers, somethimg like
With ActiveSheet
Selection."intStartrow:intLastrow"
End With