Edit Checking Before Running Macro (with Message Box)
Jan 22, 2010
I'm trying to control the number of ways people can mess-up this worksheet. I have several buttons that copy one object from a worksheet "FISH PARTS" and paste them onto another "THE JUMPER FISHBONE"
My question is can I prevent people from copying objects from the first worksheet if they already exist on the second? Specific to the code below: how do I make sure "BONE_1" doesn't already exist on the worksheet "THE JUMPER FISHBONE" before allowing it to be copied from the other worksheet. And can I generate a message box that tells the user the object already exists on the page.
I have a sheet that I have added protection to (Tools / Protection / Protect Sheet) with a password (PostItNote). Before I protect the sheet I have allowed users to edit ranges (Tools / Protection / Allow Users To Edit Ranges). I have made my selection of the cells that users are allowed to edit and protected the sheet. I now want to run a macro that will un-protect the sheet (in order to allow the macro to edit the protected cells) make some changes and then re-protect the sheet. The macro runs as it should do but when it stops I am unable to edit the cells that I allowed users to edit? For some reason the areas that I enabled users to edit have been changed allowing users to only edit far less cells that I originally set!
Is it possible to have a confirmation message box if you click a button to run a macro, eg "Are you sure you want to Insert a new row?" with "Yes" and "Cancel".
For purpose of this question my current macro is
Code: Sub Button3_Click() Rows(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row + 2).Insert Shift:=xlDown End Sub
When clicking the button on my userform, it goes through a quite complex process of changing the views in several pivot tables that are linked to an "report" sheet, which has all the figures the user needs in a neat format.
Changing these pivot tables takes up to a minute, so I wanted a userform, called "frmwait", to pop when clicking the command button. It would say "Generating Your Report - Please Wait". At the same time, the initial userform, which is called "frmroutedashboard", would be hidden.
This does not happen - the "frmwait" userform shows, however the "frmroutedashboard" does not hide.
Is this because the code I composed uses the values on the "frmroutedashboard" to generate the view, and it cannot hide until the report is completed? If so, there must be a way around this. Anyone know how?
- I have some buttons set-up to insert pre-set diagrams from sheet “FISH PARTS” onto sheet “THE JUMPER FISHBONE”
- However, before inserting a new diagram onto sheet “THE JUMPER FISHBONE” I need to make sure the default diagram components are all gone.
- There are seven components all with structure Like “BONE_*”
- If one or more of these components are present I want to display a message to confirm the user wishes to delete the default diagram and insert new chart (or abort is they do not wish to delete the default)
- some explaination of structure below - spreadsheet also attached
I'm running a macro that can take a while so I want to let the user know that the macro is running OK. I've put the following line in my code
MsgBox "Searching BOM's", vbInformation
The problem is that it requires the user to press OK before it runs the macro, then the box goes off and the macro runs. What I really want to do is display a box that doesn't require user input, and stays on the screen while the macro is running and goes off automatically when the macro is finished. When the macro is finished it will either show a page of search results, or another message box saying Not Found, which requires an OK response from the user.
I have 10 workbooks which have various calculations and have several links to each other. We use Excel 2003.
For each of these files, I have the following code in the Auto_Open module Range Range(“TodayComp”) is a date taken form a link in another spreadsheet. It is on Column 3, but the row changes every day as more rows are inserted before it
This sub looks at Range(“TodayComp”) and checks the date on the cell directly above.
If say Range(“TodayComp”) is 6/25/2008, and the cell above is 6/22/2008, it will insert three rows right above Range(“TodayComp”). These rows need to be copied with formulas from the current row above Range(“TodayComp”)
Issue One: The following code works (it copies the rows), but it takes forever to run, as you can see in bold, I copy and paste each cell, instead of the entire row. Is there a way to copy and paste the entire row? Remember that I have only a named range to refer to, no cell address as it changes every time.
Public Sub Auto_open() Update_Dates() End Sub
Public Sub Update_Dates() Dim tdy, prev As Date Dim index, i, j, yr, no_inserts As Integer
I have code to open another workbook and run a specific macro. I plan to then take that and copy it over to my workbook.
The problem I am encountering is when I run this macro, at the end of the code, a form/msg pops up stating it is complete. This then automatically stops my code from running (from my original workbook). How do I close this form in order to keep my code running?
I have checked the following reference whithin Excel VBA's references Manually:
Microsoft Visual Basic for Applications Extensibility 5.3
BUT when I run the following Macro, once checked the prior reference:
Sub AddModuleToProject() On Error Resume Next Debug.Print Err ' I GET 0 Dim VBProj As VBIDE.VBProject: Set VBProj = ActiveWorkbook.VBProject Debug.Print Err ' I GET 1004 Dim VBComp As VBIDE.VBComponent: Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule) Debug.Print Err ' I GET 91 VBComp.Name = "NewModule" Debug.Print Err ' I GET 91 End Sub
I have a huge list of entries everyday (around 50,000) that needs a time stamp. Each of them has a time difference of 5 seconds in between. That is between two entries there is a time difference of 5 seconds. I use now() function to put the time stamp. The only problem is that I cannot have any value after 4:29:59 PM. That is, I need a macro that would check for the values in the C column, and if they are after 4:29:59 PM, put the value 4:29:59 PM, instead of the real time.
In cell C1: IF((TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))) >(TIME(16,29,59)),(TIME(16,29,59)),(TIME(HOUR(NOW( )),MINUTE(NOW()),SECOND(NOW())+5)))
In cells C2:C9999999: IF((TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))) >(TIME(16,29,59)),(TIME(16,29,59)),(TIME(HOUR(C1), MINUTE(C1),SECOND(C1)+5)))
However, the problem is I have so many entries that after a 10000 or so the time passes 4:30 PM. I think the only way to fix this would be to add a macro.
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
I am creating an excel application and have come a bit stuck. I would like to able create a macro button for printing a blank version of the workbook...when I say blank I mean the cells that the end user is able to type into.
I think I am able to do this providing all the cells are on the same sheet as the button using this ...
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts() 'Insure all shift entries are completed If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then Range("G6").Value = Range("F6").Value Range("I6").Value = Range("F6").Value Range("K6").Value = Range("F6").Value ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then Range("I6").Value = Range("G6").Value Range("K6").Value = Range("G6").Value ElseIf Range("K6").Value < "1" Then Range("K6").Value = Range("I6").Value End If If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
I am writing a macro that will allow me to copy all the data in a set range (A2 and below) after checking that B1 contains the text "Year_id".
Right now, I am able to copy all the information, and paste it onto "Sheet 4". I am unable to code for the part where the macro would check for the text. The code that I have (for copying-pasting the date) is below.
Any code that would check the information in B1 into this macro code below:
Code: Sub Copy_Allinfo()
Dim Sht As Worksheet Dim Rng As Range
For Each Sht In Sheets If Sht.Name = "Sheet4" Then
I would like to have a macro designed. I have 2 workbooks, one having latest information and the other having the information in database already. both workbooks have 26 columns each. I would like to check if a value that exists in Column A of the "latest-information" workbook also exists in the "already-in-database" workbook. If so, I would like to update all 26 columns of that row in the "already-in-database" workbook with information from the "latest-information" workbook. If the value does not exist, I would like to add all 26 fields in that row into a new sheet on the "already-in-database" workbook.
I have a macro that allows me to type numbers in cells and it auto adds to the previous value... what would be nice is if when i hit enter the cell focus stays at the current cell and doesnt move the next cell down... that way if i wanted to enter multiple values into one cell i wouldnt have to touch the arrow keys to get back to the cell
e.g: i enter a value into A1 and hit enter, instead of the box focus moving to A2, it stays at A1
Heres my current Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("DATA")) Is Nothing Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Application.EnableEvents = False Target.Value = Target.Value + oldVal Application.EnableEvents = True
Sub Delete_0_Rows() 'Assumes the list has a heading. Dim i As Integer For i = Cells(65536, 1).End(xlUp).Row To 5 Step -1 If Cells(i, 1) = "0" Then Range(Cells(i, 1), Cells(i, 1).Offset(-2, 0)).EntireRow.Delete End If Next End Sub
I have data in the first 50 rows. Then in row 51 it column A it starts 0's. I run the macro, it deletes all the rows with 0's, and it deletes row 50 also some reason. I do not understand the above macro very good to edit it, and the fact it contains 5 1's doesnt help me lol
Sub AddContextMenu() On Error Resume Next Application. CommandBars("formula bar").FindControl(msoControlButton, 0, "MYMENU").Delete On Error Goto 0 Dim objTemp As CommandBarButton With Application.CommandBars("formula bar") Set objTemp = .Controls.Add(msoControlButton, , , .Controls.Count + 1, True) With objTemp .Caption = "Formula Menu" .BeginGroup = True .OnAction = "TestMe" .Tag = "MYMENU" End With............
I was wondering if it was possible for a macro to edit a file by opening each .txt file, searching for the word "Reference", and then replace it with the word "Ref". Then save and close the text file.
So that it searches for any date and replaces with the new ones, but obviously the use of quotation marks with the wildcards makes this difficult in VBA.
How to link the cell values in with the "Replace with".
Code:
Sub findrep() Dim target, cell As Range Dim i As String Dim k As String i = "Input_Sheet!$A$2:$A$1000>=DATEVALUE("??/??/????")"
How would my macro below be edited to keep the column sizes the same as they are in the original sheet?
Sub NewUploadFile() Workbooks.Add ThisWorkbook.Worksheets(3).Range("A2:K300").Copy Range("A3").PasteSpecial Paste:=xlPasteValues Range("A3").PasteSpecial Paste:=xlPasteFormats Columns.AutoFit End Sub
how to control either the X or Y axis error bars for a scatter chart.
I'm building something like a gantt chart and I only want to use the X axis error bars and delete the Y axis bars. I've been able to add error bars by using "ActiveChart.SeriesCollection(1).HasErrorBars = True" but when i select the error bars using "ActiveChart.SeriesCollection(1).ErrorBars.Select" the Y axis (Vertical Error Bars) are automatically selected and any formatting I apply is added to the Y axis and left off of the X axis.
Does anyone know how to delete the Y axis error bars and/or choose only the X axis error bars?
I've provided below a simplified version of the worksheets I'm trying to sort and the Macro I've recorded to sort the worksheet, however, I need to edit/update the macro for use on all the worksheets in the workbooks I'm sorting and specifically to REFINE the sort criteria for Column D and Column E.
The sort is run simultaneously and runs hierarchically A,B,D,E,F,G.
In column D I'd like the macro to sort numerically as it is doing but to IGNORE the prefix c. before the year when it occurs in the column.
In column E I'd like the macro to sort alphabetically as it is doing but to IGNORE any inverted commas: ' ' around the text as these are messing up the alphabetical sort for this column.
A B C D E F G
[Code]....
Below is the recorded Macro which I've called MANUAL_SORT2 this was recorded on a worksheet called BBCO with 103 rows in the column. I like the macro to be usable on all worksheets in the workbook also to add the worksheets all have varying numbers of rows!
I have a list of interest rates and durations and was hoping to be able to add in a third dimension of data which would either increase the size of the plotted points or change their colour depending on the rating. So have the normal scatter plot then save me from editing each point manually.
Is it possible to write code to paste a string in text select (double click in a cell) mode rather than cell select (single click a cell) mode? I need a string to paste in as if someone had double clicked then hit paste (so as to utilize special delimiters.) Here is the code that reads the workbook, finds the next available row, then pastes:
Dim strCopyToLog As String strCopyToLog = Range("Q2") Range("Q2").Select Selection.Copy Range("b1.b5000").Find("").Select ActiveCell.Value = strCopyToLog
The way i have been creating macros is by going to the tools menu.....macro....then..... record new macro.
I have a file which I have re-formatted using a macro as described above however because i receives files every month to do updates every time i open a new file and try to perform that same macro it either wont work or it wont format the correct rows.......is VBA the solution to this????
