VBA Macro To Move Shape Around Spreadsheet

Dec 16, 2009

I am trying to write a set of code, part of which involves moving a shape around an excel spreadsheet in a square shape within a certain range. It is based on a random number generator. The shape moves a number of cells to the left/right/up/down depending on the random number (d) multiplied by 16. If the shape reaches a row or column border point, it will change direction but continue moving in the new direction until it has gone as far as was determined by the random number (*16). I have some code which works fine... to a point! The shape starts out, reaches a right hand border, changes direction to go up, reaches another border, changes again and goes left, but then when it comes to change dorection the 3rd time and go down, it fails. The direction of the shape is determined by a word in cell A1 - left/right/up/down:


Sub MoveRed()
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer

d = Range("AZ1").Value

With ActiveSheet.Shapes("RedDot")
For i = 1 To 16 * d

Select Case Range("A1").Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



ADVERTISEMENT

Macro To Move Around Large Spreadsheet....

Jan 6, 2010

Need the code to put into a command button which jumps from the cell selected, X number of cells to the right. Also will need the code to jump back again when clicking on a different command button.

View 5 Replies View Related

How To Move A Pasted Shape

Mar 2, 2013

I Have several images on Sheet 1, I can copy the needed Images to sheet 2 as needed.

Once the image is pasted to sheet2 how do I assign it to a shape variable so that I can position it as desired.

I do not want to have to use Sheet2.(Range("B2").activate or similar

I'm looking to use

Shp.top = 25
Shp.left =15

Code:

Sub Macro1()
Dim Shp As Shape
Sheet1.Shapes("Picture 1").Copy
Sheet2.Paste

'To set the shape variable to the shape on sheet2

Set Shp = "WHAT GOES HERE"
Shp.Top = 25
Shp.Left = 15
End Sub

View 4 Replies View Related

Move Shape Across Screen

Dec 24, 2007

I'm trying to write some VB to draw a line shape and then move it across the screen. I have managed to draw the line and have used the following code to move it:

Set linetest = ActiveSheet.Shapes(1)
For iCount=1 To 100
linetest.Left = linetest.Left + 1
linetest.Top = linetest.Top + 1
Next

i) I can only actually see the line move if I run the code using the debugger and step through the loop. If I just run the code then I only see the end position of the line (note, this is not an issue with the calculation being too fast for Excel to display the line. I tried the code with a delay loop in it and it made no difference). I can get it to work by calculating the active sheet for each iteration of the loop, but this means I have no control over the speed of the movement; incidentally it moves very slowly).

ii) This seems a poor way to move the line. Is there anyway to move the start and end node of the line rather than using the left, top, width and height properties (i.e. can I get the old coordinates of the points and set them to different numbers)?

View 5 Replies View Related

Move A Shape In A Straight Line Between 2 Cells

Sep 24, 2008

if I wanted to slowly move a shape (small circle) in a straight line between cells R20 & W7.

The straightest line possible is by drawing an arrow line between the 2 cells.

Is there a way to then use this arrow as a guide for moving the shape?

In other words, would it make sense for the porgram to draw a transparent line between the cells, and use that line as a "path" along which to move the shape?

I just don't know if this is worth the trouble.

View 9 Replies View Related

VBA To Move Shape From Current Cell Position To Cell 1 Row Up

Nov 11, 2008

I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3

View 5 Replies View Related

Move Spreadsheet To End Of Workbook

Jun 4, 2009

I m moving a spreadsheet to the end of a workbook, however the amount of spreadsheets in the workbook increase each day.

Two files will be involved in this macro. 1) Headset Out Time and 2) Headset Out Time "Month". Where month is the current month.

Command Button 2 located on the file Headset Out Time will execute the macro. The macro will open Headset Out Time "Month" then move the first spreadsheet (different name on a daily basis), from Headset Out Time to the end of the workbook Headset Out Time "Month". Then it will reset the spreadsheet (this part i can handle)

View 3 Replies View Related

Highlight A Row And Move To The Bottom Of Spreadsheet

Nov 12, 2012

I'm working my way through a list of companies on an excel spreadsheet and making some notes.

After i've spoken to them its a simple keep them on the sheet in the current position or highlight the row red and move to the bottom or when they become a customer highlight green and move to the bottom.

I have a coloumn called customer (J) is it possible if i put a Y in that cell to make the row green and move to the bottom and also if i put a N in the column make the row green and move to bottom.

View 8 Replies View Related

Move Specific Data To Another Spreadsheet In The Same Workbook

Jul 5, 2007

What I want to know: Is is possible to have information from one spreadsheet move specific data to another spreadsheet in the same workbook?

for example: I have hundreds of listings of tool measurements.

Date Tool type Measurements


What I want to do is have all the different types of tools and the information from the above line seperated into other spreadsheets. I do not want to sort this information and manually move it I want a formula that will move each applicable tool into that sub sheet. I have tried to use if statements but find that it creates subsheets with the correct tool types but has the spaces in between the parts. how do I eliminate the spaces?

View 9 Replies View Related

Change Shape Text Without Selecting Shape

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

Macro To Enter Shape Or Picture?

Sep 30, 2012

I am making a spreadsheet file for our football offcials group to review rules.

I tried to record a macro to copy a shape and put it into the spreadsheet, but the shape does not copy.

How do I get the shape to copy into the spreadsheet?

What I want is for them to "think" what they would do and then click the macro and it will show them where the ball would be after the foul/fouls.

View 2 Replies View Related

Assigning Macro To Shape In 2007

Dec 5, 2008

I posted similar yesterday. No one helped...sniff sniff (where's the crying smily)

Now verified to be occur on other machines running various versions of Office 2007...

In Excel 2007, write this simple macro and assign it to a shape:

View 6 Replies View Related

How To Add Formatting To Shape Creation Macro

Aug 7, 2013

I have to create an identical button on 20 sheets.

I have a shape creation macro but it creates in the default format.

Can I either change the default format so they are created in the way I want or code to add formatting? I need no shadow, can colour (pref with gradient fill), bevelled edges and text.

Sounds like the first option will be easiest if it works!

View 1 Replies View Related

Macro To Select Shape To Print Off

Jan 2, 2014

I created a shape and named it Cheque and created a macro to print the shape as follows:

ActiveSheet.Shapes("Cheque").PrintOut

But the macro is not printing!

View 4 Replies View Related

Using Shape To Call Macro (Filepath Error)

Apr 9, 2014

I am changing command buttons over to shapes for aesthetic purposes. I went to assign a macro to call useform4 using a rectangle with the following code:

[Code] .....

It throws me some kind of file path error which makes no sense. Do I have to dim the rectangle2 for active sheet or something?

View 11 Replies View Related

Run Macro After Leaving Shape Editing Mode?

May 14, 2014

I've got a macro function which calculates the area of a polygon freeform using the 'shoelace'-methode. I would like to excute this function after i leave the shape editing mode.

For example: I have a square, freeform shape. By right-mouseclick I can edit the shape nodes. If i drag one of the nodes of the square to another position and click outside of the shape, the new shape is updated. Right, on that moment i would like to trigger my macro to recalculate the area.

Is there an event in excel to aim for, like the Worksheet.change-event or something similar?

View 2 Replies View Related

Automatically Assigning Macro To Any Shape Created

Aug 14, 2014

I've got a macro that I can rund when I click on a shape. It's called "Shape_Click".

Basically, I want this macro to be automatically assigned to any shape I create, as I create it.

I know the line will be something like: [Code] ........

View 6 Replies View Related

Automatically Assigning Macro To Created Shape?

Aug 14, 2014

I have a macro that runs when I click on a shape. This macro is called "Shape_Click"

I can apply that to a shape by selecting it and manually running this macro:

[Code] .....

How do I get the subroutine to run as soon as the new shape has been selected, rather than haviong to manually run it.

Something like "Sub Worksheet_ShapeAdded" or something like that? I can't work it out.

View 2 Replies View Related

Determine Shape Clicked For Running Macro

Nov 19, 2008

I have map shape in my excel sheet. each country is a shapes on its own. If i Select one of the country i want to display a pop up Box. For examle if i select Americas I need it to display another box with some data and when i deselect it it should hide the box. Tried some code over here but does not work.

Sub HideShowRectangle()
With ActiveSheet.Shapes("Data1")
If ActiveSheet.Shapes("Americas").Select = "True" Then
.Visible = True
Else
.Visible = False
End If
End With
End Sub

View 5 Replies View Related

Excel 2002 :: Activate Macro By Clicking A Shape

Feb 3, 2013

I found in an old file that i triggered the macro by clicking a button! I would post a pic but i don't know how. How do I activate a macro by clicking a shape on the excel 2002 page?

View 7 Replies View Related

Locate A Shape Within A Shape

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

Macro Request - If Macro Is Unable To Perform One Of Its Jobs Then Move Onto Next Line

Oct 3, 2012

I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.

However, if the user is not online, the query is unable to refresh and the macro just hangs.

Is there a code which will enable me to say " if unable to refresh then move on to the next line"?

here's the code below.

Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub

View 2 Replies View Related

Macro Fixes Macro: Copies Tabs From The Spreadsheet

Jun 26, 2007

I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.

View 2 Replies View Related

Macro To Move Between Worksheets

Apr 4, 2014

I have a work book with 6 worksheets, sheet2 is called Maersk, sheet3 Mariana, and so on ....

I need a macro so when I type Mariana in sheet1 g20 and hit enter it go's to Mariana sheet .....

View 5 Replies View Related

Macro To Move To New Row On A Form

Mar 26, 2014

I have a macro that is auto opening a form to be completed, but it currently opens on the first row of data that has been inputted.

How do I get the macro to move to a new row immediately to stop people from over-typing the first row of data?

View 5 Replies View Related

Macro To Move To A Column

May 14, 2009

I need a macro that I can assign to a button so that it will jump the screen to a particular cell. I want it to select the cell in a chosen column in the same row as the cursor already was. So, if I'm in M10 and I hit the AK button, it'll jump to AK10. If I'm in F54 and I hit the AK button it'll jump to AK54.

View 2 Replies View Related

Macro To Move Text

Oct 14, 2009

I am trying to create a macro to do the following (Microsoft Excel 2002 SP3):

1 - Select the text from the active/selected cell (i.e. B1)
2 - Cut the text from B1
3 - Move to the cell directly above the active/selected cell (i.e. A1)
4 - Add a space to the end of whatever text is in A1
5 - Paste the contents that were cut from B1 after the text and space in A1
6 - Delete the row where the text was cut from (i.e. entire row B)

I have never written code for macro's before, only done recording, but just can't seem to get this sequence to work .

View 8 Replies View Related

Macro To Cut And Move Columns

Feb 22, 2010

I've used the macro recorder to copy a cut and paste routine, but it won't play back correctly. Here's the macro I recorded:

View 7 Replies View Related

Cut And Move Rows - Macro

Nov 2, 2011

I need to cut 2 rows when i put "V" in E column, and then to paste them below row that have in column A, "OLD".

Ex:
I have many rows with data like this:

.......A..........B.........C.......D......E
1...subject
2...notes
3...subject............................."V"
4...notes
5...subject
6...notes
...

25..."old" row...............................

I need to cut the row with "V" in column E and the below row, and paste them below row with "old" in A column, like this:

.......A..........B.........C.......D......E
1...subject
2...notes

3...subject
4...notes
...

23..."old" row...............................
24...subject............................."V"
25...notes

View 9 Replies View Related

Using Macro To Move A Cell

Dec 19, 2013

I've been using the record macro function to accomplish a lot of my formatting tasks for my work reports.

I'm running into a snag that I'm not sure how to fix. Essentially I want the program to recognized the term "Grand Total" and the cell to the right of it. Then I want those two cells to be moved to the far left.

The # of rows will continuously change but he column shouldn't change.

I've tried naming the cell selection to have it moved but I can't seem to make it work.

When I use the record macro function and do a CTRL F search for "grand total", excel doesn't seem to recognize that I want that cell and the cell to the right to be the ones to move. Instead, whatever row of cells I create the macro with, it wants to duplicate the move with the same exact cell.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved