AutoShape Won't Go Away?

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


Chanage Autoshape Colour According To Autoshape Colour In Another Worksheet

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

Hiding An Autoshape

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

Export An Autoshape As A Gif

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

Remove Autoshape Line

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

Locking Autoshape Position

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

Colour Change Autoshape

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
End Sub

View 6 Replies View Related

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 View Related

VBA Autoshape Arrows To Show Performance

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

Placing AutoShape Text Into A Variable?

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

Aligning Autoshape In The Center Of Cell

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

Finding The Cell References Of Autoshape ...

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

Limit Number Of Characters In Autoshape

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:


but I want the count to relate to the active shape. So I tried:


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

Hide/unhide Autoshape Object

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

Autoshape Resizes According To Amount Of Data In Column

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

Change Colour Of Autoshape Based On Cell Value

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.


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

Dynamic Hyperlink In Autoshape Based On Cell Value?

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

Link Autoshape/textbox To Cell Content

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

Create Autoshape Based On Formula Result

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

Change Color Of Autoshape Based On Comparision Of Cells

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

Macro Recording 2007: Record A Macro To Insert An Autoshape

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

Copyrights 2005-15, All rights reserved