AutoShape Name That Ran Macro Code
Apr 3, 2008
I have a large map of a region with which I want to create autoshapes to allow certain areas to be zoomed into. So far i have made an autoshape, assigned a macro, and used
ActiveWindow.Zoom = True
To zoom into it. At least that is the theory. What I have discovered is that this only works when a shape is selected, which does not occur when you click on it. Is there a way to get the name of the autoshape being clicked, so that i may select it within the macro and zoom in on it. Or is there a better way of doing this? I am trying to avoid making explicit macros for each autoshape as I will have many of them by the time this is complete.
View 2 Replies
ADVERTISEMENT
Feb 18, 2007
I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...
View 7 Replies
View Related
Oct 23, 2008
I am trying to automatically change the colour of an oval in worksheet1 to the colour of oval3 in worksheet 2.
Currently oval3 (Worksheet 2) has the a vba code that changes its colour when you click on it.
View 9 Replies
View Related
Apr 21, 2013
I have a range of cells that, when ever I try to click on, bring up the AutoShape rectangle. I don't know how I managed to turn this off, but I'd love to know how to turn it off.
View 4 Replies
View Related
May 14, 2008
Seems like this should be pretty cut-n-dry to me...I thought I could "hide" an Autoshape (AutoShape 19) using some formula [I.E. =IF(B12="","",SUM(C37:C45)) ] but I cannot get it to work...
Basically I dont want to see the shape if there is no data D49.
View 9 Replies
View Related
Aug 4, 2006
I want to save an autoshape as a .gif file and then reference it to an image box in my excel spread sheet. I can't seem to do this through excel or using VBA code.
View 8 Replies
View Related
Jul 1, 2008
I have a shared document I created. In has been in use for 4 months with no problems. This last month, 3 other individuals not very cognizant with Excel were using the document. One of these individuals placed a 'line' in the document. It appears that they used Autoshapes to draw the line.
I want to remove the line they have drawn. It sounds like a simple problem, but I've tried:
* Edit, Clear, All
* Edit, Cut
*Right Click the 'line', Format AutoShape, under Colors and Lines, change Line Color to No Line
* Delete a series of rows with the line in the middle row,
Everything I’ve tried to eliminate this line is not working and this is very frustrating. This type of 'line formatting' is rampant in the department and has appeared in multiple documents, which I've had to recreate.
I'm not sure what they did, so I'm not sure how to correct this issue. I'm presuming they used AutoShapes. There are two small white circles - above and below the line. And a small green circle above the top white circle that has circular arrows surround it....perhaps a rotate feature.
View 9 Replies
View Related
Dec 7, 2009
Can I lock an autoshapes position relative to the screen size?
i.e. I have a disclaimer that appears at the bottom of my screen in the middle, however when the workbook is sent to another user and their monitor has different aspect ratio's the autoshape is positioned a couple of inches from the bottom of the screen.
I have looked at locking to cell references but this has the same effect.
View 9 Replies
View Related
Mar 29, 2007
I have been trying to make it so that an autoshape changes colour according to the value of a particular cell but every time I run the macro the shape is black and I cannot work out why. The code is -
Private Sub CommandButton1_Click()
Dim X As Integer
X = Range("H5").Value
With Sheets("Sheet 1").Shapes("Rectangle 1").Select
Select Case X
Case Is > 0:
Selection.ShapeRange.Fill.ForeColor.RGB = 2
Case Is < 0:
Selection.ShapeRange.Fill.ForeColor.RGB = 3
Case Else:
Selection.ShapeRange.Fill.ForeColor.RGB = 1
End Select
End With
UserForm2.Hide
End Sub
View 6 Replies
View Related
Oct 2, 2008
Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.
Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).
The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.
Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?
View 7 Replies
View Related
Mar 28, 2013
If you know the autoshape number, it is easy to retrieve any caption text but is there a way to retrieve the text from any autoshape without knowing its number.
Dim ASText As String
ActiveSheet.Shapes("AutoShape 43").Select
ASText = Selection.Characters.Text
This will place the text in AutoShape 43 into variable ASText.
I need the code to get the text from any autoshape I click on reguardless of what its number is.
I use a lot of autoshapes on a worksheet as command buttons. Sometimes, the macro code is identical for many buttons with the exception of what the autoshapes text is. This text is the variable the macro needs. It would alow me to use one macro instead of sometimes 30 or more.
View 7 Replies
View Related
Jul 2, 2013
Any way to vertically and horizontally center an autoshape in a cell without VBA. I created a shape and placed it in a cell but don't think it is centered. Also, if I add shapes to adjacent cells, I would want them all to be centered directly in the middle of the cells they are in. Using the align feature only seems to make sure the shapes are aligned to each other but doesn't seem to put them in the center of their respective cells.
View 1 Replies
View Related
Aug 15, 2007
I have a sheet that contains several autoshape objects. What I need to do is find all the cell addresses & then format the same cell addresses on another sheet with a solid colour. So for instance if my first autoshape is in Sheet1!A8, I need to fill say red or blue the same cell on sheet 2 ...
View 7 Replies
View Related
Sep 28, 2009
I am using a diamond shape that is copied into the active sheet when the user runs a macro. The user then selects the shape and enters some text. I want to limit the characters to a certain number or at least be able to give the user some advice when they enter too many characters.
I have found that, as long as I have manually named the shape "Diamond1", I can get the count in my immediate window with the following:
?ActiveSheet.Shapes("Diamond1").TextFrame.Characters.Count
but I want the count to relate to the active shape. So I tried:
?selection.shapeRange.TextFrame.Characters.Count
and I get a "runtime error 438 Object doesn't support this property or method".
How should I count the characters in the selected shape, and then if it is over say 12, require the user to shorten it or at least present yield a message Box?
View 9 Replies
View Related
Jun 20, 2007
I have drawn an arrow on a worksheet using the Autoshape menu on the Drawing Toolbar. Now I'd like to write some code that will hide this arrow (make it not visible). Later on in my code the arrow should become visible again. The idea is that users will not see this arrow untill certain actions have been performed.
View 2 Replies
View Related
Feb 14, 2009
I type in a "O" into the little grid I made and then the locations are placed into cells A34, A35, A36 and so on. The arrow I placed into column B. It works if I manually enter anything into A34,A35,A36 and it stays with the data. If I let the grid automatically populate A34, A35 and so on, it will not grow as the cells in A34, A35 are automatically populated. How can I make it do that one little thing? Sorry if I am confusing.
View 11 Replies
View Related
Mar 21, 2012
I've seen examples of VBA that will colour autoshapes based on cell values. I have a map of the UK made up of autoshapes that have been edited to create a shape of each postcode area such as EX, GL, NR etc. Also I have a list of all these postcode areas and I have given them a zone number (1-7) depending where in the UK they are.
PcodeZoneDH3DL3DN2DT1DY2E4EC4EH5
At the moment I have set the colours using the fill option so anything that is a particular zone is a certain colour (i.e. zone 2 is green).
What I would like to do is to set a zone to a colour, so the map will update all the zones with the right colours.
I need to be able to read the zone number that is assigned to a postcode area and change the relevant autoshape to the correct colour so that any postcode area can be one of 7 colours.
I can't attach a spreadsheet with my map and postcodes otherwise I would have.
Is there an easy way i can read the zone number of each postcode area in the list and update that autoshapes colour? Would the autshape need to have a name so it know which one to update or is this not possible?
View 9 Replies
View Related
Jun 7, 2014
I have created an employee schedule with 26 pay periods. Each pay period has its' own worksheet for scheduling employees.
To navigate to the worksheets I created a home page on one of the worksheets and would like to create an AutoShape for navigating with a hyperlink based on the pay period which is selected in cell B3. When the autoshape is clicked the user would go to the correct pay period worksheet.
For example Home Page:
Cell B3 contains drop down list of the 26 pay periods which the user selects the proper pay period (ie. 8Jun14-21Jun14)
Cell E3 is a cell reference on the sheet A1
Cell B6 contains hyperlink formula =HYPERLINK("#"&"'" & B3 & "'!" & E3)
Autoshape has formula =B6
When I click on the autoshape the hyperlink does not work.
View 2 Replies
View Related
Jul 7, 2006
Let's say I place a rectagle/textbox on a worksheet. If I select it and go to the formula bar and write =A1 the content of cell A1 will be displayed in the rectangle (and of course if i change the content of A1 cell, the content of the rectangle/textbox will be updated). How can I do this from a VBA macro? How can I link the content of a rectangle/textbox to a cell?
View 2 Replies
View Related
Aug 1, 2006
how to have a cell equate to an autoshape based on the results of an "if" statement?
View 4 Replies
View Related
Mar 7, 2008
I am atempting to do some autoshape conditional formating. I am about above average when it comes to working with Excel, and I know little to some Visual Basic. Here are the two things I am attempting to accomplish.
1. I am attempting to use VBA to change the color of a Large Pyramid Autoshape called "JanPyramid". The thought behind this is, I wish to have the Pyramid show vbGreen when it is <= my target (which in this case is a user inputed cell called "RECTARGET", so instead of using a number in my VB code I would like to have it refer to this cell in E58) I want the Pyramid to show vbRed when the total Recordables (this number is generated by using a COUNTIF function, so it counts all entrys in the table that have REC entered for its status, in this case the cell is called "RECCOUNT" and is located in I54) is > my target, E58 "RECTARGET"
2. I have already sort of gotten this to work by using values instead of using cells. But it is still is not perfect. I would prefer to use the actual cells because my targets and the actual counts can be different depending on what happens during that month. Here is the code I have in so far, it is with the values instead of it being the cells because I do not know how to make it read the cells instead of values:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RECCOUNT")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.BackColor.RGB = vbGreen
ElseIf Target.Value > 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub
This code is a hybrid from this Ozgrid thread: Changing fill color of autoshapes based on cell numeric values.
View 5 Replies
View Related
Dec 17, 2008
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.
Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"
I need a macro to "execute this code", as if it were in the macro itself.
I have assigned the above to a variable, but am not sure how to execute it.
EG.
Dim the_calc
the_calc = Range("B5").value
Now, how do I run the_calc ?
View 9 Replies
View Related
Sep 9, 2007
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.
1) Macro Recorder Code to Copy One Sheet to Another
Sheets("Data Apple").Select
Cells.Select
Selection.Copy
Sheets("Banana").Select
Cells.Select
ActiveSheet.Paste
2) Macro Recorder Code for Replacement Purposes......................
View 9 Replies
View Related
Feb 22, 2008
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub
View 3 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Aug 10, 2014
Refer to attached file.
I have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.
[Code] ....
Test Macro_Botton.xlsm‎
View 3 Replies
View Related
Feb 7, 2008
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.
My worksheet runs a lot of code, then deletes all macro code and saves itself.
The problem is the next time i open I still get the macro security warning!
I've checked thoroughly and there is definatley no vba left anywhere.
If I open the document, enable macros, and save it, then open it again, I no longer get the warning.
View 9 Replies
View Related
Sep 11, 2006
I written VBA code to create a new sheet and embed a command button on it. I want to assign a macro to this button but when I click on the button the assign macro button is greyed out. I have plenty of macros saved and the VBA code has finished running so why should this be?
View 7 Replies
View Related
Jan 27, 2009
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies
View Related
