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.
View 2 Replies
ADVERTISEMENT
Feb 14, 2013
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!
Here is my macro code that I am running:
[Code] ......
View 2 Replies
View Related
Feb 22, 2013
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
View 2 Replies
View Related
Aug 21, 2006
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?
The initial code so far looks like this:
frmroutedashboard.hide
Application.Cursor = xlWait
Application. ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
I thought if it looked like this, it would be sufficient. Unfortunately, the file is too large to attach, so I hope the code suffices.
frmroutedashboard.hide
frmwait.show
Application.Cursor = xlWait
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
View 8 Replies
View Related
Jan 22, 2010
- 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
View 2 Replies
View Related
Oct 12, 2008
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.
View 9 Replies
View Related
Jun 25, 2008
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
Sheets("Comparison Computation").Activate
View 3 Replies
View Related
Oct 4, 2008
I need a message box with information before the code run. Yes - code run, No - exit code.
View 9 Replies
View Related
Mar 1, 2008
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?
View 3 Replies
View Related
May 16, 2009
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
View 9 Replies
View Related
Aug 25, 2006
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.
View 6 Replies
View Related
Mar 6, 2009
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.
View 10 Replies
View Related
Oct 4, 2007
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 ...
View 10 Replies
View Related
Mar 6, 2009
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.
View 8 Replies
View Related
Oct 8, 2009
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........................
View 7 Replies
View Related
Aug 15, 2007
When I list my macros using alt+F8 the edit option is no longer available, any ideas what I have done?
View 9 Replies
View Related
Aug 1, 2012
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
[Code] ........
View 7 Replies
View Related
Aug 19, 2013
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.
View 3 Replies
View Related
Feb 4, 2008
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
End Sub
View 9 Replies
View Related
Mar 3, 2007
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
View 9 Replies
View Related
Jul 24, 2007
I cannot run code from the formula bar.
I have a context sensitive menu
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............
View 9 Replies
View Related
Jun 18, 2009
I have disabled edit directly in cell in the option menu so that when i double click it goes to source file.
View 3 Replies
View Related
Dec 7, 2007
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.
There are no delimiters in the text files.
View 9 Replies
View Related
Jan 27, 2012
I have an array formula i use to sum data by specific criteria:
=SUM(IF(Input_Sheet!$A$2:$A$1000>
=DATEVALUE("01/04/2011"),IF(Input_Sheet!$A$2:$A$1000
=DATEVALUE("??/??/????")
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("??/??/????")"
[Code]....
View 3 Replies
View Related
Mar 19, 2012
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
View 7 Replies
View Related
Oct 29, 2012
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?
View 5 Replies
View Related
Apr 27, 2013
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!
Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'
'
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("A2:A103"), _
[Code]....
View 1 Replies
View Related
May 15, 2014
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.
i.e.
A1
B1
C1
D1
Interest Rate
Duration
Rating
3%
3.5
A+
[Code] .........
View 1 Replies
View Related
Aug 18, 2008
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
View 2 Replies
View Related
Jul 25, 2006
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????
View 9 Replies
View Related
