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?
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
I do a weekly report for my employer. We have our tabs conditionally formatted. We then insert the corresponding arrows to show a trend to the weekly variance. After the week is up I will delete a column and add a column to the end (making it a trended view) and move the arrows by selecting and dragging all of them into its respected week. I then go on to delete several arrows throughout the worksheet. For some reason my file continues to grow in size however I am only keeping a 12 week trend. The arrows are not deleting properly and are sitting in the background, where I cannot see them. I know this bc I did "find & Select" and chose selection pane. I was wondering how I can resolve this problem or maybe delete just certain columns of arrows (objects)? I had 61,000 arrows and shouldn't have more than a couple 1,000.
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
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.
I have created this Excel schedule that is attached. The point of the schedule is to organize Jobs and Crew members. I have VBA code in, so when I calculate how long the job should take, the arrows next to the job will automatically extend. (This should all make sense if you take a look at the attachment.) The only problem is that my arrows do not extend the correct distance. In the attached file I have scheduled "James Lorenz" to do a job starting on tuesday, towards the bottom of the page I did my calculation that figures he should be on that job for 9.5 days. I would like the Arrow next to James' job to extend 9.5 days on the calender instead of 9.5 centimeters or whatever it is doing now.
From the Excel Toolbar - Data/Validation - I'm successfully using a validation list [drop down arrow] to be able to choose from a list and have whatever value I choose entered into the cell. So far so good. Everything working fine.
Here's the thing though, the list I'm referencing is a column with 50 rows. Sometimes there are many items in the column - up to 50, and sometimes only 3 items depending on other variables in the spreadsheet.
Because the list is referencing the entire 50 rows in the column (only way I know how to do it), during times when the column has only 3 items (3 rows), the drop down still shows a bunch of blank spaces (like 47 of them - tons of white space with a scroll bar window that runs down beyond where one can see), which is awkward (strange looking).
How do I program the validation list to only show as many cells within a range that have actual values within the cell, or where <> "" ? Or, in other words, to leave all blank spaces found in the list = NOT SHOWN.
I have a table A-AP 4-499 (row 4 being the column headers and 5 being the first row of data).
I'd like to remove some of the filter drop-down arrows as they aren't needed for some column headers and they also look unsightly.
This code works to remove all of the arrows:
Sub HideArrows() Dim c As Range Dim i As Integer i = Cells(1, 1).End(xlToRight).Column Application.ScreenUpdating = False For Each c In Range(Cells(1, 1), Cells(1, i)) c.AutoFilter Field:=c.Column, visibledropdown:=False Next Application.ScreenUpdating = True End Sub
However, I want to only remove arrows in the following column headers: A, G, I, J, K, M, N, O, P. I want to keep the rest.
I found this code for hiding an autofilter arrow for column 2. I would also like to hide the arrows for columns 35 through 50. Does anybody know how to do that? Thanks.
Private Sub Workbook_Open() 'hides all arrows except column 2 Dim c As Range Dim i As Integer i = Cells(1, 1).End(xlToRight).Column Application.ScreenUpdating = False For Each c In Range(Cells(1, 1), Cells(1, i)) If c.Column 25 Then c.AutoFilter Field:=c.Column, _ Visibledropdown:=False End If Next Application.ScreenUpdating = True End Sub
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.
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.
I am trying to make different colour arrows appear based on a set criteria, I am having issues with the hide/show amber part, it doesn't seem to be doing the "AND" part.
'Hide/Shows Green' Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value >= 0 Then ActiveSheet.Shapes("Arrow1").Visible = False Else ActiveSheet.Shapes("Arrow1").Visible = True End If 'Hide/show Red' If Range("A1").Value = 0 And Range("A1").Value
The sheet was working fine, then all of a sudden (i dont know what i have done) the up and down arrows (on my kayboard) wont work when the sheet is protected, when i unprotect the sheet they work fine, i can use left and right. i have checked the scroll lock isnt pressed/illuminated on my keyboard.
I'm trying to create a VBA macro that will compare two worksheets. If an item has "moved" then I want to draw an arrow on the new worksheet of where (the cell range) it was on the previous worksheet, to where it is now. Using Record Macro, I've noticed the coding looks like: ActiveSheet.Shapes.AddLine(276#, 60.75, 309.75, 60.75).Select
How do I make sense of these numerical values? Is there a way to input cell ranges? Is there anything besides drawing arrows I can represent this phyiscally? (as opposed to with values)
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.
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.
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.
Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.
I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
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
I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want
For i = 1 To N For j = 1 To M A(i,j) = Cells(j, i).Value Next j Next i
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.
I am looking to show Month Wise Trend Up/Down/Nuetral Arrows for the Achievement % for each month.
I want to show the Increase or Decrease trend of the % in the next month.
I have Baseline figures (count) and i have count of Mar, Apr, May months. (Column A)
I have calculated the Achievement % by 100%-(Actual/Target) (Column D, G, J for Mar, Apr, May Months)
I want to show the Increase or Decrease Trend Up/Down/Nuetral buttons either by the Achievement % or by the Total Incidents Each month compared to the Baseline Figures (Column A).
I tried to use Icons Set in Conditional Formatting but i was not able to do that.
I have a table with a filter applied. I don't want all of the column headers to be filtered because the data in those column is not suitable filter criteria. Is there anyway I can remove the individual arrows on the right hand side of the cells in question?
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes 0 - No left blank - n/a
B3 counts the number of entries in the column B2 counts the number of 1's in the column B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999 Fee Range 210000-19999 Fee Range 320000-29999 Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
