Run Macro After Leaving Shape Editing Mode?
May 14, 2014
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?
View 2 Replies
ADVERTISEMENT
Mar 13, 2007
I'm doing the vba programming for excel where I added a button to the excel, and when user click on the button, it will trigger my function.
My question is: if currently there is a cell been focused, how can I know whether that is in the 'editing mode' (ie, focus on that cell and double click on that) or merely 'focus' on that cell? Any of the excel object property for this?
View 9 Replies
View Related
Jan 13, 2010
I'm in eager need of a command to run a macro on leaving a userform textbox;
View 2 Replies
View Related
Mar 15, 2007
How can I have my macro run in its cell and still allow the cell to be blank? Say I have a number, 0.99, in the cell and I want to delete it and leave it blank. I can either push "Backspace" or the delete key and it still leaves zeros.
If I push the space bar it stops the macro (which I do not want to happen) and leaves the cell blank because I added a space.
View 12 Replies
View Related
Dec 28, 2005
Macro that will strip out all the numbers in 1 particular cell column and and leave the text.
View 9 Replies
View Related
Jul 25, 2014
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 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
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
Sep 11, 2006
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
Call Debug_Msg("Opening WBS File")
Application.EnableEvents = False
Set wbkThis_WkBk = Application.Workbooks.Open(strWBS_Files(intCounter), 0, False)
Application.EnableEvents = True
Call Debug_Msg(("Opened WBS File:" & Chr(13) & strWBS_Files(intCounter)))
If (wbkThis_WkBk Is Nothing) Then
Call Write_Error_Record("File Open Failed", strWBS_Files(intCounter))
Else
Code continues.....
View 6 Replies
View Related
Dec 1, 2006
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.
View 4 Replies
View Related
Oct 19, 2007
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?
View 6 Replies
View Related
Apr 3, 2008
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?
View 2 Replies
View Related
Feb 17, 2010
I'm trying to extend the range of a summation formula with a macro.
I've attached a dummy worksheet. I've named each cell in row 8 as well as the summation ranges they are calculating.
I am looking for the result of the macro to have each total's formula range to extend to to row 7 of its respective column.
View 9 Replies
View Related
Nov 11, 2009
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
View 10 Replies
View Related
Apr 7, 2008
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
View 9 Replies
View Related
Nov 5, 2008
adds the file name into column A if it could also hyperlink it to that file. 2nd, Change it so it doesn't start a new workbook and worksheet. I would like it to just run in the Workbook it is in and each time the macro runs or the workbook opens it updates any info that has changed.
View 5 Replies
View Related
Oct 31, 2011
having trouble editing the code to allow for dynamic ranges. I have tried to research online, but am having a hard time with it. I think I would understand better if I could see a practical example.
Here is the sample macro I recorded in Excel that I am working with:
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Select
ActiveSheet.Range("$A$1:$C$87").RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlNo
End Sub
How can I edit this to allow for dynamic rows and columns?
View 2 Replies
View Related
Nov 24, 2006
i currently have created a macro for copy a selection of cells from one work book to another. The file were i am copying the cells from is Track_&_Trend_00.xls. In this there is a password to open and modify. The sheet that the cells are being pasted to is TRACK & TREND.xls. There is no password on this. My macro runs fine only that midway through it asks for the password to open and the password to modify for the sheet Track_&_Trend_00.xls.
I wonder ifany one would know about entering input lines of code below to automatically put these passwords in and not having the two password dialog boxes pop up midway through running the macro.
Range("A8").Select
ChDir "T:Track & Trend"
Workbooks.Open Filename:= _
"T:Track & TrendTrack_&_Trend_00.xls"
ActiveWindow.SmallScroll Down:=-102
Range("A8:S115").Select
Selection.Copy
Windows("TRACK & TREND.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
View 9 Replies
View Related
Mar 4, 2009
when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies
View Related
Aug 6, 2009
i have a list of about 20,000 phone numbers that I need to edit.
all start
01 123456
01 123457
01 123458 etc
I am trying to create a macro that will change them to
+001123456
+001123457
+001123458 Etc
However, no matter what I try it always comes out as
+001123456
+001123456
+001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost.
I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
View 9 Replies
View Related
Sep 30, 2012
I am making a spreadsheet file for our football offcials group to review rules.
I tried to record a macro to copy a shape and put it into the spreadsheet, but the shape does not copy.
How do I get the shape to copy into the spreadsheet?
What I want is for them to "think" what they would do and then click the macro and it will show them where the ball would be after the foul/fouls.
View 2 Replies
View Related
Dec 5, 2008
I posted similar yesterday. No one helped...sniff sniff (where's the crying smily)
Now verified to be occur on other machines running various versions of Office 2007...
In Excel 2007, write this simple macro and assign it to a shape:
View 6 Replies
View Related
Aug 7, 2013
I have to create an identical button on 20 sheets.
I have a shape creation macro but it creates in the default format.
Can I either change the default format so they are created in the way I want or code to add formatting? I need no shadow, can colour (pref with gradient fill), bevelled edges and text.
Sounds like the first option will be easiest if it works!
View 1 Replies
View Related
Jan 2, 2014
I created a shape and named it Cheque and created a macro to print the shape as follows:
ActiveSheet.Shapes("Cheque").PrintOut
But the macro is not printing!
View 4 Replies
View Related
Dec 16, 2009
I am trying to write a set of code, part of which involves moving a shape around an excel spreadsheet in a square shape within a certain range. It is based on a random number generator. The shape moves a number of cells to the left/right/up/down depending on the random number (d) multiplied by 16. If the shape reaches a row or column border point, it will change direction but continue moving in the new direction until it has gone as far as was determined by the random number (*16). I have some code which works fine... to a point! The shape starts out, reaches a right hand border, changes direction to go up, reaches another border, changes again and goes left, but then when it comes to change dorection the 3rd time and go down, it fails. The direction of the shape is determined by a word in cell A1 - left/right/up/down:
Sub MoveRed()
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer
d = Range("AZ1").Value
With ActiveSheet.Shapes("RedDot")
For i = 1 To 16 * d
Select Case Range("A1").Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
View 9 Replies
View Related
Apr 9, 2014
I am changing command buttons over to shapes for aesthetic purposes. I went to assign a macro to call useform4 using a rectangle with the following code:
[Code] .....
It throws me some kind of file path error which makes no sense. Do I have to dim the rectangle2 for active sheet or something?
View 11 Replies
View Related
Aug 14, 2014
I've got a macro that I can rund when I click on a shape. It's called "Shape_Click".
Basically, I want this macro to be automatically assigned to any shape I create, as I create it.
I know the line will be something like: [Code] ........
View 6 Replies
View Related
Aug 14, 2014
I have a macro that runs when I click on a shape. This macro is called "Shape_Click"
I can apply that to a shape by selecting it and manually running this macro:
[Code] .....
How do I get the subroutine to run as soon as the new shape has been selected, rather than haviong to manually run it.
Something like "Sub Worksheet_ShapeAdded" or something like that? I can't work it out.
View 2 Replies
View Related
Nov 19, 2008
I have map shape in my excel sheet. each country is a shapes on its own. If i Select one of the country i want to display a pop up Box. For examle if i select Americas I need it to display another box with some data and when i deselect it it should hide the box. Tried some code over here but does not work.
Sub HideShowRectangle()
With ActiveSheet.Shapes("Data1")
If ActiveSheet.Shapes("Americas").Select = "True" Then
.Visible = True
Else
.Visible = False
End If
End With
End Sub
View 5 Replies
View Related
Sep 19, 2013
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
View 1 Replies
View Related
