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............
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
I have Form Button in Excel Sheet which Calculates by reading other excel sheet. Now the problem is when user enters data in a Cell by double Clicking on it and after entering data if he directly clicks on a button then Macro that needs to be invoked is NOT executed. The only way is that he needs to press EnterKey OR Click on any other Cell and then click on the button.
I have users who don't press Enter (or move to a new cell) after entering text. They then try to click a form button - and of course - the macro does not run.
Is there an xl/vba solution to force them out of edit mode before clicking the button?
In an Excel worksheet all command buttons are disabled while a cell is in edit mode or while the cursor is active in the formula bar. Is there a way in VBA to save the cell's contents and execute a command button if clicked while a cell is in edit mode?
I am using a lot of double click routines in my sheet. In order for the sheet to be more user friendly I have used the comments of cells to tell the user what to do. For instance "Double click to choose type" or some such. Everything works great except that after the user selects what they want from the userform and it hides and the focus comes back to the sheet, the comment is in edit mode. While this is not horrible it is not as nice as I would like. I have tried to have my code send the active cell to another cell and then back, but that doesn't seem to work. Does anyone have any ideas on how to keep the comments from going into edit mode?
I would like my custom menus to act like builtin menus - specifically I would like them to be disabled (grayed out or even entirely removed) when the application is in Cell Edit Mode (I cant believe I am the first to ask about this but I cant find the answer elsewhere).
It sure seems confusing to offer users access to a seemingly active command when it does not run (in edit mode I mean).
I send huge lumps of Karma to any willing to share ideas or solutions.
A client has sent me an Excel workbook that appears on the screen with the message adivising that it is Protected View as a result of having been downloaded from the internet. If I click on the Enable Editing button the workbook is completely deactivated. I have played around with my Trust Centre settings but nothing seems to work.
When a user clicks a cell in Column A, VBA will enter the date in the cell (in the form MMDDYY) and will position the insertion pointer at the end of the date. The user can then enter a four digit number following the MMDDYY. In this worksheet, Column A holds a transaction number, which is always in the form MMDDYYXXXX. I am trying to save the user the trouble of entering the MMDDYY, since it will always be today's date.
I have a peice of code which opens up a number of Workbooks (200+) and updates a series of control tables in each. Given our security etc environment, links and external references etc are not possible. The target files must all be standalone and self-sufficient.
The problem I have is that when I run the procedure in question in the usual way, the procedure simply terminates after the first file is opened. No error messages, no distress - it just stops. I have put debugging calls all around the "Workbook.Open" call, and it simply doesn't make it out of the call. There is no Auto_Open code in the workbooks being opened and (as I indicated above) no external links, references or function calls.
What's weird is that if I step through the procedure in the Command Debugger, everything works fine. Even weider, if I complete step-by-step processing on the first target file then I can press "Play" and the code will complete execution and process all the remaining files without error.
I have rebuilt/recreated/re-ordered the target files and even moved them to a different volume.
If anyone has seen this before, or has any idea what the cause may be, or even just a wild idea, I'd love to hear it.
Code segment attached.
* '*** Process each file in the list provided '* For intCounter = 1 To intWBS_Files
I've got a macro function which calculates the area of a polygon freeform using the 'shoelace'-methode. I would like to excute this function after i leave the shape editing mode.
For example: I have a square, freeform shape. By right-mouseclick I can edit the shape nodes. If i drag one of the nodes of the square to another position and click outside of the shape, the new shape is updated. Right, on that moment i would like to trigger my macro to recalculate the area.
Is there an event in excel to aim for, like the Worksheet.change-event or something similar?
Ive got a problem with a work book im working on at the moment, my company has various different documents created in excel, like a price list, cost price list & stock levels (all Independant) I have had the idea to join them all into a single shared workbook using an idea I found and modified over a year ago on this site, that had the promise of allowing multiple users to see the nessesary parts of the same document. the advantage of this being any new parts or prices or suppliers added would update all users at once. So ive got this workbook that when you open you get asked for a username & password this then hides/unhides, protects or unprotects to suit the users needs. This works great when unshared but when I share it the login box wont display
The Administrator username is jamie and the password is joshua
I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too
sub open file (FileName as string)
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object Dim RowNo As Integer Set xlx = CreateObject("Excel.Application") xlx.Visible = True 'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)
Workbooks.Open FileName:=SourceFolder + "" + FileName ' Columns("F:G").Select ' Selection.Delete Shift:=xlToLeft Save_in_WDrive (FileName) Set xlx = Nothing end sub
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
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.
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????
I accidently put the Application.Quit command in the wrong place. Now Excel closes immediately when the macro executes. I can get into Excel if I disable the macro, but then it won't let me edit the macro. Is there a way to edit my macro without executing it?
I'm writing a macro that opens another Excel file, opens a userform within that file, and manipulates the data. I've never tried to do this before. I keep getting "Object Required" error. My macro fails on the "frmMain.Show" where "frmMain" is the userform I am interested in. Here's the
Excel.Application.Workbooks.Open ("C:DataCalculations.xls") 'Open file frmMain.Show 'Bring-up UserForm frmMain.cbUser.Value = "Guest" 'Sign as Guest frmMain.cbVariables.Value = var 'number of variables
I used the macro editor to create a "simple" macro to edit 7 rows of text. The steps were basic and simple - F2 key, backspace about 25 times, and down arrow then repeat these 3 steps 6 more times. The macro I got gave me the result of the very first cell as I was recording the macro. I think I remember the old Lotus 1-2-3 macro text - {edit}{backspace}{down}. Wish it were still that simple.
I need to query a column and find the cells that have been incorrectly coded. For example the coding standard is #<reference number>/. Sometimes people forget the "/". It is not as easy as using search and replace because the string may have more contents than the above.
For example it may show: ATM/45678/#789876 dt. 1-2-14/ or it something like CHQ/44384/#78987600.
There are many permutations. So I need to find the cells that don't have a "/" following the #<reference number>, then allow a user to enter the slash. At this stage, I don't need code to find cells containing errors, I think I can figure that out. I do need to know how to allow the user to edit the cell as part of the macro. Once the user corrects the error, the macro should then continue. Is this possible?
I have a macro that sends a custom email through lotus notes. It works, but will only generate 1 email instead of looping through my list. If I have "next r" at end I get email of 1st record. If I put "next r" before "On Error GoTo Audi"
I get last recorded emailed but not 1st 2. I don't know how to get it to send all. It seems the for next loop is jacked up.
For you to see macro work you need to have lotus notes and put email address in column B.
