Resetting Shape Locations
Apr 12, 2012
I have many spreadsheets that use shapes such as arrows and other symbols. The process for these spreadsheets is to move the shape (arrow for Ex) to indicate a specific location of pain or something like that.
Once the arrow is in position, the spreadsheet is printed to PDF and and a snapshot (in adobe) is "taken" and the picture (with the arrow) is pasted into our application.
My questions is how can I move the shapes back into position when you want to do it all over again.
When I have a few shapes I use
PHP Code:
Set sShape = ActiveSheet.Shapes("Right Arrow 6") 2
    With sShape
       .Top = 220
       .Left = 91
       .Rotation = 20
   End WithÂ
For each shape
But if I have lots of shapes, 50+, it's a bit more cumbersome.
Can I loop through the shapes when I enable the macros to pick up the current positions and use that information when I do a reset (loop back again to move any shape that were touched back to where it was when the spreadsheet was opened?
Currently I have them close the spreadsheet without saving and just reopen.
View 3 Replies
ADVERTISEMENT
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
May 29, 2009
my excel is no longer showing any tool bars and I can not get them back, is there any short cuts to bring back the menus? I can’t even do a right mouse click on the sells for some reason. The only tool bar that is showing is the formula bar.
View 10 Replies
View Related
Aug 13, 2009
Code runs rather slow. Is there a better way to achieve the same results?
View 5 Replies
View Related
Nov 1, 2009
If I change the country in cell B2, I would like to have that the cells D2, E2 and F2 show the same name as cell R2. (This is always the first option of the drop menu's)
Cells D2, E2 and F2 are made by using the validate option in order to get drop down menu's.
The source information is in the hidden columns I-Z.
So for example:
if I change cell B2 to Belgium, I would like to see that D2, E2 and F2 also show Belgium immediately. I can then select the place I want to move from Belgium to by using the drop menu in D2 (for exampla Belarus).
As I selected for D2 Belarus, this has to be shown in E2 and F2 also.
I can now select E2 (for example Austria).
If Austria is selected in E2, I want to see this also in F2.
To recapitulate:
If I select Belgium as starting place, I want to see in movement 1,2 and 3 also Belgium, as there hasn't been anymovement yet.
If I let my army go from Belgium to Belarus, I want to see for the two remaining movements Belarus (as this means that the army remains in Belarus)
If I let my army go forward after Belarus to Austria, I want to see in the last movement phase Austria (no movement). If I still want my army to move I can still adapt it.
View 2 Replies
View Related
Mar 25, 2005
I have a spreadsheet to keep track of purchase orders, and I use groups of option buttons to run macros to filter the info by category using auto filters. Not sure if I'm explaining this well, but as an example, I have one set of option buttons for whether the items are late, and one for whether I have flagged the order for some reason, and a couple of others. It does everything I want, basically.
I have a button which resets all the autofilters in one go, but the problem is it doesn't put the option buttons back to the "Show all" option for their categories. I tried recording a macro and clicking in the "Show all" option so I could look in the code to see how it refers to it, but it just says to run the macro, nothing about selecting "Show all".
Also, the way I reset all the autofilters is to have a macro that goes to every autofilter in turn and selects "(All)". Is there a better way of doing this?
View 9 Replies
View Related
Feb 25, 2013
I am creating scorecards for our company and ratings are inputted using radio buttons (form control). I have a "clear" macro that clears all fields and radio button values (none is ticket). This macro is called throughout different sheets, as different roles in the company have different KPIs and should be on different sheets. I need a code that detects all radio buttons within a certain sheet and clear their values. I cannot put specific radio button names, as I have a lot.
View 2 Replies
View Related
Feb 26, 2008
I have a column (L8 down) of cells formatted as $'s
At the end of the column is a sum formula.
At the end of each month the client wants to 'reset' all the $ amounts back to $0.00
Is there a macro that would reset the column of $'s back to $0.00 without removing the formulas like ClearContents does?
View 9 Replies
View Related
Dec 19, 2011
When I open Excel 2010, the formula bar appears about 6 times as tall as normal. I have not been able to find any way to reset it to normal size.
View 11 Replies
View Related
May 24, 2013
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
View 5 Replies
View Related
Jun 13, 2014
VBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.
Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.
[Code] ......
View 2 Replies
View Related
Jun 9, 2009
Is there a way for me to have combo boxes, labels, and command buttons to be moved to a set position rather then just moving by intervals? What I need this for is every time one of my worksheets opens it refreshes the data and does this via lookup formulas and a macro that searches for values and deletes the rows with the matching values, it also adds rows to the top after it is done deleting. This causes all my controls to move randomly and I need some code to reset them to their original sizes and positions after the code that adds and deletes rows is done running.
View 2 Replies
View Related
Apr 1, 2008
Every time I re-define a named range, either through the UI or via macro, the permissions I had set for that range are seeminly deleted from the allowEditRanges collection. (When I go to view them under tools>protection>allow users to edit ranges>users allowed to make changes without password ends up blank)
I would like to re-define the range in a VB macro without this undesired effect. Alternatively, a script that reads the permissions for that range before my code and writes those same permissions after would do fine.
View 5 Replies
View Related
Feb 15, 2007
I've recorded a macro that clears a worksheet, fetches data from an Access Table and then creates a new pivot table. The Pivot Table Fields are summarized by "sum" and this worked the first few cycles for the macro. Now it is returning the Pivot Table Fields summarized by "count". Can an option be inserted into the existing code to specify "sum"? Here is the
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'raw data'!R1C1:R205C12").CreatePivotTable TableDestination:="", TableName _
:="PivotTable14"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable14").SmallGrid = False
ActiveSheet.PivotTables("PivotTable14").AddFields RowFields:=Array("Name", _
"FIELD_ASM_USER_NAME", "Data")
With ActiveSheet.PivotTables("PivotTable14").PivotFields( _
"SumOfSumOfSumOfCYYTD_SHARE_QTY")
.Orientation = xlDataField
.Position = 1
End With..................................
View 2 Replies
View Related
Jan 24, 2014
On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm‎
View 5 Replies
View Related
Jun 23, 2014
I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.
In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.
If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.
If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.
This works perfect.
However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.
The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.
So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.
Calculator 23-06-14.xlsx
View 6 Replies
View Related
Dec 5, 2007
I have to manually cut/paste each excel doc which belongs to a specific Store folder, and copy then into their own shared directory, so for example:
Lets say my company has 10 Stores, and I know their online folder locations, the first step I guess would be for me to map the locations of each store, I am not sure what the best way to do this is, would I use the Case select ? to make the set = ?
So for example, Stores 1 - 10.
Set 1 = Dir("Z:Northwest1")
Set 2 = Dir("Z:Northwest2")
Set 3 = Dir("Z:Northwest3")
Set 4 = Dir("Z:Northwest4")
Set 5 = Dir("Z:Northwest5")
Set 6 = Dir("Z:Northwest6")
Set 7 = Dir("Z:Northwest7")
Set 8 = Dir("Z:Northwest8")
Set 9 = Dir("Z:Northwest9")
Set 10 = Dir("Z:Northwest10")
I am not an expert on Case Select, but was thinking:
Select Case myStores
Case Is = 1.......................
View 9 Replies
View Related
Dec 15, 2012
The code below works. Now I need that while it does the backup file in the same location, we also took into C: Backup_Contabilidade. Finally also put the date and time.
VB:
Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application
.CommandBars("Cell").Reset
End With
Application.DisplayAlerts = False
[Code] .....
View 3 Replies
View Related
Dec 14, 2011
i am counting hotels in different area of 1 city
city area hotel_name hotel_name hotel_name
NY st-1 A001 B001 C001
NY st-2 B001 C001 D001
NY st-3 D001 E001 A001
count by checking NY and see how many branches A001 is having in all these areas. (i should get answer 2)
View 6 Replies
View Related
May 13, 2014
I have a spreadsheet on a server, password protected, and certain columns, rows locked...
This is shared with 6 others and can only be accessed one at a time.
If It so that when the user saves the spreadsheet it saves over itself on the server it's located AND on the individual users computer as backup; how would I go about doing this!?
View 3 Replies
View Related
Dec 1, 2008
the attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
View 10 Replies
View Related
May 21, 2012
I made an analysis table that shows a list of prices on a certain company. I have a table on one worksheet and I made a chart (from that table) on another worksheet. I'm using an in-cell drop down list on the table worksheet to change to different companies. Is there a way that I can put the same drop down list on the chart worksheet? Essentially, I want to be able to switch the company on either worksheet and have it change on both worksheets. I'd like to keep the chart and table uniform and running together.
View 6 Replies
View Related
Oct 20, 2012
I'm trying to determine the net displacement between GPS locations and am wondering how I can easily code this in Excel. As far as I know, you need to calculate the distance between Point B to Point A, Point C to Point A, Point D to Point A, and so on. The following is the formula that I would use:
A
B
C
X_Coordinate
Y_Coordinate
123456
3700000
Point A
123460
3700010
Point B
Using the following formula (or setup), this is how I would calculate net displacement:
(123460-123456)^2+(3700010-3700000)^2 = (16)^2 + (10)^2 = 256 + 100 = 356
I assume if I want net squared displacement, I would square root this value SQRT(356) = 18.867
If my formula is correct above, I wonder why Excel gives me an answer for the following net displacement formula (same as above just using Excel formula): (A3-A2)^2 + (B3-B2)^2 = 116
Using this same formula, I could incorporate SQRT in as follows: =sqrt(((A3-A2)^2) + ((B3-B2)^2)), which results in 10.77. This value is clearly different than the value above (18.867) thus I wonder if I'm just mis-calculating something in Excel.
View 4 Replies
View Related
Sep 10, 2007
i have a User form that i'd like to add preset tabs command locations to.
For example,
on open i'd like the active cell to be on cell M3. and as the user presses the tab button jump to the following cells locations:
P3
Q5
P7
Q7
P9
View 9 Replies
View Related
Mar 5, 2010
I have to select about 200 odd files from a folder, and move them to a different folder. The problem is, I have to manually select the 200 files from a list of approx 10,000 (not in order).
I have a list of the filenames (with extension) in an Excel Spreadsheet, and I'm wondering if there's a way to automatically move the files using an Excel macro or something?
example:
File location = C:EBDumped
and I need to move the 200 files to C:EBSent
So I would need to move ONLY the files where the file name is in the Excel list.
The file type (extension) is always the same, but the name (and length of the name) is different.
Is this possible using Windows XP with Excel XP?
View 9 Replies
View Related
Mar 30, 2014
HTML Code:Â
=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))
I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?
In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.
I have probably over complicated?
View 3 Replies
View Related
Aug 21, 2014
I have 1 list with two columns, where column A is the location (a 3 digit code), and column B is the name (a 4 digit code).
I have a second list with two columns, again column A is the location, and column B is the name.
I want to make sure the locations and names in the second list match what I have in the first list. If they don't match, I somehow want to flag this in a third column.
View 4 Replies
View Related
Aug 4, 2009
I am looking to do find the latest date in a list of dates but have several problems:
1) The dates are all in one column on worksheet 1, but are sourced from other worksheets. So even though it looks like '8/7/09', the value of the cell is 'Worksheet2!B4'. This is making the MAX function not work
2) How to deal with the blank cells in the MAX function?
View 12 Replies
View Related
Mar 16, 2014
Distance Calculations Example 1.xlsx
I need quickly identifying multiple locations based on their proximity to an initial location and each subsequent location selected. I have attached an example spreadsheet that is structured in the following manner:
Cell H2 is the 'initial' location that will be used to identify all subsequent locations
Colums A through C are location specific details including latitude and longitude of all 450 possible locations
Column D is a distance calculation based on the difference betwen the location in column A to the 'initial' location in cell H2
Cell F2 is the necessary minimum distance between locations(in miles)
Cell G2 is the number of locations needed(this will vary)
What I am trying to accomplish is to fill column H with the number of locations specified in cell G2 that are atleast the minimum distance in F2 apart from each other, like drawing virtual circles around the locations on a map.
I am pretty sure this can be done with a Macro, but am not sure if that is the best solution or not. I am a novice Excel user, so even though I don't know the solution, I can already see two issues:
1. Whatever the solution is, the reference in column D will need to change to calculate the distance for each subsequent location instead of staying absolute with cell H2(H3 for second location, H4 for 3rd, etc...)
2. With each selection that is made, any location less than the specified distance in cell G2 should be removed from any future choices for subsequent locations.
View 4 Replies
View Related
Oct 27, 2008
I use the below code to specify where a database is:
View 2 Replies
View Related
