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
ADVERTISEMENT
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
Mar 26, 2007
How do I search for and remove the vertical lines in the above example from the column D? These things came when I imported a data file into Excel....
View 9 Replies
View Related
Sep 7, 2007
I have created a questionnaire with multiple answers in a userform with multipage.
Everything works fine except on about 10 out of my 30 questions, one of the four answers (all opition buttuns) has a fine dotted line around it.
I really have no clue as to where this line comes from. I have tried deleting the option button and replacing it with a new one. This helped for the concerned button but now the dotted line appeared on another answer of the same question! I have done it several times and each time the dotted line simply shifts from one answer to another but never dissapears! The odd thing is that this only concerns 10 questions, the other 20 are fine...
View 5 Replies
View Related
Mar 27, 2008
I have a exel file which has been exported from Access to exel. There are many cells which shows the data as in Cell A1 with the Linefeed character in between data in a cell. Is there any way (may be using a macro) where I can remove the character and get it to display as B1 in the same cell(A1). The file is attached herewith.
View 5 Replies
View Related
Feb 16, 2014
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
View 9 Replies
View Related
Dec 22, 2011
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
View 4 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
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
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
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
Feb 23, 2013
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies
View Related
Nov 17, 2012
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
View 5 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Jun 10, 2007
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or
b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
example:
Data loaded into the sheet before the macro runs:
Row: Date:
1 06/09/07 *
2 06/10/07 *
3 06/11/07 *
4 06/09/07
5 06/10/07
6 06/12/07 *
7 06/13/07 *
8 06/09/07
9 06/11/07
10 06/14/07 *
* The rows with asterisks are the ones that I would like to have pulled to the second set of data (below)
Data in the sheet after the macro runs:
Row: Date:
1 06/09/07
2 06/10/07
3 06/11/07
4 06/12/07
5 06/13/07
6 06/14/07
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
View 6 Replies
View Related
Aug 5, 2014
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
View 7 Replies
View Related
