How Do I Get Past A Delete, Cancel Button During A Macro Loop?
Aug 12, 2009
I would choose to select the delete button. I've created a new worksheet within a workbook, insert text and formatting, and copied to a specific place in the active worksheet. Everything works well except 1. there is a LOT of coding because I recorded the macro and 2. the delete message pops up for every files the macro manipulates. I have to click "delete" about 60 times throughout this macro and would love to be able to let it click its own delete button!
I am running a macro to loop through a list of codes which takes the unique code, goes to a different spreadsheet, finds related data based on that code and if it meets certain criteria, and retursn the same code if it works. Pretty much a filtering process, but I have it returning the code because I do not want to do the calculations in the Macro itself.
Here is the code:
VB: On Error Goto Label1 Do Windows("Byron_MonthlyPDPWellList.xlsx").Activate Range("A" & I).Select
[Code] .....
"I" begins at 2 and counts up until it reaches Count, which is the number of total rows in the column.
However, when I step through the code, it continues to loop through the code where "I > Count".
I have tried "Until I > Count", "Until I = Count", "While I <= Count". And none of them seem to work. It gets stuck in an endless loop cycle.
I am trying create a macro which asks for the Sheet name as an input for a Sub. I've gotten it to work but there is just one problem: The Cancel button does not work on the MsgBox. Everything else works just as I want to: it shows a dialog box to enter a name for the worksheet and if the worksheet does not exist, it loops and shows a message saying that it does not exist.
However, the cancel button does the same thing as entering nothing in the box and it does not end the process. I would want it so that the cancel button kills the process. Here is the code:
Code: Option Explicit Function WorksheetExists(WSName As String) As Boolean On Error Resume Next WorksheetExists = Worksheets(WSName).Name = WSName On Error GoTo 0
Msg = "Enter a number" BOXTITLE = "Number" ANS = InputBox(Msg, BOXTITLE)
This generates 2 buttons. If i click the cancel button how would i direct the code to go to a certain line in my code. EG if cancel button clicked then goto 10
I would like some help on disabling the Esc key in a macro and to disable the Cancel button and X on message and input boxes.
My macro is to insert rows in a protected worksheet and another to run a spellcheck, if my users hit any of the above keys during the macro my worksheet is left unprotected and formulas could be deleted in error. I have pasted a copy of my code below,
Sub InsertRowAboveCopyFormulas() ActiveSheet.Unprotect ("password") Dim Rng, n As Long, k As Long Application.ScreenUpdating = False Rng = InputBox("Enter number of rows required.") If Rng = "" Then Exit Sub On Error Resume Next ActiveCell.EntireRow.Select Selection.Resize(rowsize:=Rng).Rows(1).EntireRow. _ Resize(rowsize:=Rng).Insert Shift:=xlUp Selection.Offset(Rng).AutoFill Selection.Resize( _ rowsize:=Rng + 1), xlFill Selection.Resize(Rng).EntireRow. _ SpecialCells(xlConstants).ClearContents ActiveSheet.Protect ("password") End Sub
I have a userform with two buttons : O.K. and Cancel When I use the O.K. Button, all is correct, but when I use the Cancel Button, it doesn't work. What line I have to add, to leave that userform ? (Cancel : CommandButton2_Click()
Sub CommandButton1_Click() Me.Hide End Sub Public Function userChosen() Me.Show userChosen = Format(DateSerial(cmbJaar, cmbMaand, cmbDag), "dd.mm.yyyy") Unload Me End Function
I have the code below. That code call some InputBoxes in sequence, that be filled with correctly information like name, cell phone, date of purchase, etc. (sheet is in Portuguese)
But I want to give a function for 'Cancel' Button, because actually if we click on cancel Button, the macro skip to the next inputbox.
I want to click in Cancel Button, and Exit Sub, I used this Tip for example
[Code] ....
But if we do not fill the Text field, the Macro Exit Sub Too
Some fields are optional, so for this i search for a solution
I have this macro code that can protect and unprotect, It will show the input box and ask for a password. my problem is when I choose cancel, it still protect the file, the same thing with my unprotect code. here is my code below
Why is the below code not valid? I am using a userform with a cancel button and i cant it to copy and paste some lines on sheet Chart_Data as it unloads. But i just get the error
"Runtime Error '1004': Method 'Select' of object '_Worksheet' Failed"
I have a macro that works perfectly to import a text file and parse it. Now i need it to do multiple ones. I import the txt file to a new workbook as i filter for certain data only and if found i copy that to the current workbook. I want to do the same just for many txt files:
I worte a procedure to protect the worksheet. When we clik on command button it asks the password but if I click on cancel button procedure is exiting.
Currently have a sheet that allows me to pick an item from a drop down list and show me the code for that item.
I already the macros for adding an item to the list via a add button. All this dose is if the item is not on the list i can simply be added into the two boxs and then click the add button.
What i want to do is add a delete button. This is in case someone enters the values wrong then can select it in the list and then click the delete button.
So all i want is to look at the item cell, match to a range and delete the row.
I have added a link to what it looks like as well as the coding i have used so far. [URL] ........
Sub Add_to_list() ' ' Add_to_list Macro ' Adds new item to list ' ' Range("F10").Select Selection.Copy
I need to be able to run a macro that will find the word “report“ within a cell in column A. Once found it I then need it to delete that row, the row above and then the 9 rows underneath it. Once it has deleted that data I need it to then move to the next and so on.
It’s a report I have dropped into excel which repeats the page header. E.g the following is repeated constantly which I need to be deleted:
¬¬¬¬¬¬¬¬¬¬¬¬¬ Report: xxx Company: xxx Turnover By C 01/04/2008 To **THIS REPORT Customer: 0 T ¬¬¬¬¬¬¬¬¬¬¬¬¬ Account Cust Num ¬¬¬¬¬¬¬¬¬¬¬¬¬
I created with code to copy a template, hide that template, and pop up a box to rename the copy, I noticed she clicked "Cancel" on the InputBox. When she did, she received an error (400). What I would like to do is when the "Cancel" button is clicked, the newly created copy would be deleted. Is this possible?
Here is the code for my full "Create New Project" sheet procedure:
Sub CreateNewProject() 'This code will copy the Project Data sheet, hide it and then 'rename the new copy to the MSA number. Code also prompts user for 'MSA Number and fills that in on the form. Dim RenameSheet As String Dim oSheet As Worksheet With Sheets("Project Data") .Visible = -1 .Copy After:=Sheets("FHWA Quarterly Report") End With...............
I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document
I'm trying to track information pertaining to employees across different worksheets in one excel file. I have one sheet that is the master list of employees. The first column of every worksheet is the same (employees names based on their location) but track different information.
I want to be able to create a macro button that will update all the worksheets if i insert or delete an employee from the master list. When a new row is inserted, the other worksheets should be updated as well with the new name and a blank row to be filled in. And when a name is deleted, the entire row should be deleted as well.
how i might create a macro to attach to a button so i can delete the contents of all VALUE! cells. Either this or have a script in the background so no cell will return VALUE! (or any other error if possible)
I know if i had all the formulae sorted they might not butit's quite a large spreadsheet. The reason for the VALUE! error is text in cells that the formal points.
I have an excel form with a command (submit) button that opens up Outlook when clicked. I am looking for a way to have this submit button disappear when the user selects a specific item in a drop down list to make sure they do not email the form when it is used for a promotion (Promotion would be selected in the drop down).
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I want my worksheet to require validation of entry data before printing, so I decided to use the "Workbook_BeforePrint" event, and display a simple dialog box with an "ok" or a "cancel" button as input options.
The problem is that, as coded, "cancel" button does not cancel the print.
the title should read... pressing cancel in inputbox, continues the macro... In theory, pressing the cancel button should stop the macros, but it continues as if pressing ok button...
macro listed below. Currenlty the macro pulls the last 12 months as long as the data that is pulled is current. However, if the data is 2 months old for example, it only pulls 10 months instead of 12. modify this macro so that it alway pulls the last 12 months. I'm assuming that the minimum date would have to be found first and then go backwards from there?
Code: Dim PI As PivotItem Dim d As Date Dim Twelve As Date
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
I'm using Excel 2002 on and XP machine. I am creating a spreadsheet to track a work plan.
Column A: Name Column B: Date Column C: Time Column D: YES Column E: No Column F: New Date Column G: New Time
What I'm looking to do is input click able buttons in Columns D: Yes and Column E: No.
When a user clicks on YES, a pop up window should appear with the data in columns A,B,C.
The p0p-up should contain CONFIRM and CANCEL buttons.
Asking the user if they want to confirm the date and Time.
If CONFIRM is clicked Excel should then send an email to me and the user to say that the work schedule has been confirmed.
If CANCEL is clicked the pop-up window closes.
Secondly if the user clicks NO, a pop-up window should open with two free text boxes. One for Proposed date and One for Proposed time. And again 2 buttons for Confirm and Cancel.
If Confirm is clicked then Excel should record the data entered in the free text boxes into Column G and Column H and send emails to me and the user with the Proposed date and Time Data.
