VBA Macro To Move Shape Around Spreadsheet
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:
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer
d = Range("AZ1").Value
For i = 1 To 16 * d
Select Case Range("A1").Value
View Complete Thread with Replies
Related Forum Messages:
Move Shape Across Screen
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
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)?
Move A Shape In A Straight Line Between 2 Cells
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.
Find And Move VBA Macro
find and move VBA macro - funky when WhatToFind in more than one row
Making a program that will find a value input by the user, and move the row of the cell that value is found in to the top of the list.
Works perfectly if there is only one row containing the search string. If more than one row contains the search string, it moves things to odd places.
Dim FirstCell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
'Window prompt allowing user to define WhatToFind
WhatToFind = Application.InputBox("What are you looking for?", "Search", , 100, 100, , , 2)
'If WhatToFind is a value and not blank, move on
If WhatToFind <> "" And Not WhatToFind = False Then
'Start with first worksheet
'Start at first cell
'Find the first cell containing WhatToFind (specified by user)
Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'If FirstCell exists, move on
If Not FirstCell Is Nothing Then
'Ok, First Cell is set
VBA Macro To Move & Insert Rows And Average
I would like to have a VBA code to the following:Please note that the rows are dynamic and I need the results row for every customer.Currently it is totalling and displaying the results column at the botton of every customer but I need it at the start of every customer
1.When the new customer number starts the cells are shifted and moved one row.
2.Insert the results word and it displays the results thats displayed currently at the bottom,shoudl be displayed at the top where the row gets inserted in (1)
Customer Date of POD Del date Diff Percent
123456 03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0
(The result is that if all the rows are 100% ,the result row is 100% else it is 0%)
Customer Date of POD Del date Diff Percent
123456 Result 0
03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0
VBA Macro To Move Data To Summary Sheet Based On ID's
I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error.
Dim counter As Integer
Dim counter2 As Integer
Dim id As Double
Dim id2 As Double
Dim palletnum As Integer
ActiveSheet. Name = "Pallet Detail"
ActiveSheet.Name = "Pallet Summary"
Move Spreadsheet To End Of Workbook
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)
Positioning Shape Objects In VBA
I am trying to work out how to pisition a new object in some VBA code, I can do this if I copy an object then click on a cell, e.g. H74 then paste, but the properties for an object want this in points.
ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 26.25, 26.25).Select
What I want to do is select the cell H74, then find the coordinates for that cell then use these when creating the shape, I appreciate these coordinates will need to be assigned to variables, but how do you get then from a given cell?
Move Specific Data To Another Spreadsheet In The Same Workbook
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?
Dynamically Add A Hyperlink To A Shape In VBA
I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.
What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?
Here is how I create the shapes:
With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight)
.Fill.ForeColor.RGB = RGB(204, 255, 204)
.Line.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame.Characters.Text = StrRowName
.TextFrame.HorizontalAlignment = xlHAlignLeft
.TextFrame.Characters.Font.ColorIndex = xlAutomatic
.TextFrame.Characters.Font.FontStyle = "Bold"
.Locked = True
Changing Auto-shape Text Vba
This it possible to change an auto-shape text say Rectangle 200 based on what cell is selected. For Example Peps: -If i select cells(3,3) then the text in Rectangle 200 will change to Hello!!!, but if i select cells (3,4) then the text will change to Bye Bye!!! Do you understand me babes
Assigning Macro To Shape In 2007
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:
Determine Shape Clicked For Running Macro
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.
If ActiveSheet.Shapes("Americas").Select = "True" Then
.Visible = True
.Visible = False
VBA To Move A Row Up When Value Is Not Zero
I have 8 rows (B345:B352) that have a formula similar to this:=IF(Site1!B53>0,CONCATENATE(Site1!$B$53," Scansys 942 @ ","$",Site1!D53,".00"),""). By the way, can the formula be shortened? I tried formatting the merged cell but nothing changes.
When Site1!B53=0, then there's nothing in the cell.That's how I want it. But say, row 3, Site3!$B$53 >0 how do I programatically move (copy/paste?) row 3 to row 1 (since row 1 and row 2 are blank)? In other words, Since rows 1 & 2 is blank, I don't want the two empty rows there, so I would like to move row 3 up to row1.
Move Files Into Directories Using VBA?
In my Excel file, I have three columns: A, B and C. Column A holds the folder names. column B holds the files hash names. And in column C, i keep the files real names, i.e:
A1.value = Contract folder
B1.value = 23ffryu567894lkgj090
C1.value = picture1.jpeg
On my hard drive, I have all the folders (column A data) already created inside a folder called directories: E:directories... There is another directory on my drive in which I keep all the hash files: E:infiles
And all I would like to do now, is to write a piece of code to read all three columns row by row and based on the data in that row, moves the hashed file into their proper directories and rename them to their real name. In the example above, the program should read the data found in the first row: A1, B1 and C1 and move the hash file named 23ffryu567894lkgj090 to E:directoriesContract folder and rename the hash file to picture1.jpeg
VBA To Move Data To Other Worksheets
I have a file which contains multiple worksheets. The applicable worksheets for my question are: "NJSS", "NJSS2","NJSS3 and SHORT FORM." NJSS is pictured below. NJSS2 and NJSS3 look identical.
******** ******************** ************************************************************************>Microsoft Excel - Trial5.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutK15L15M15N15O15P15Q15R15S15=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
VBA - Deleting Rows And Move Up
I need to look into a particular cell on a specific column.
if this cell does not contain a number, then I need to delete the entire row, not column.
and then of course do this for 30K+ rows.
if sheets("sheet1").cells(x,7) is not a number ( dont know how to write this )
then delete row and move up.
Vba To Copy Sheet And Move To The End
i am trying to get a vba procdeure to copy Sheet "Template" and move to the end, but when i run the code, the code copies the template but does not move to the end, how can the below be modifed?
VBA Code To Move Or Copy Data
I would like to copy or move data from one worksheet to another automatically. I have one sheet (Data) with multiple pieces of data for specific individuals. I have several other sheets titled their last name. Can data for example from John Doe be copied to the sheet Doe and data for John Smith be copied to sheet Smith. In essence I would like the sheet to paste everything below John Doe to Doe's sheet and recognize the next name and past that into the next sheet and so on. It may be several different codes, Im ok with that.
One other item all cells do not have data I do need the blank cells copied as well at to not lose the formatting. There is also a blank 2 rows between each person.
If you need a copy of what the data looks like I can send it.
Macro Fixes Macro: Copies Tabs From The Spreadsheet
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.
Vba On Shared Spreadsheet
I have a 4 MB Excel file containing some very basic VBA that is shared on a server (it is a large file because it contains LOTS of "VLOOKUP" functions). But the VBA only works a part of the time. I assume the reason it might not work is because the server at that time was under high traffic and thus the VBA failures can be traced to poor server performance at that time.
Trying To Move My VBA Programming To The Next Level And Use More Efficient Code
I'm trying to move my VBA programming to the next level and use more efficient code.
I'm wondering if there is a faster way to run the loop below, perhaps removing the 'For c = 2 to LastRow' as it seems a little clunky to me.
For c = 2 to LastRow
If Cells(c, Range("Product_Type").Column) = "" Then _
Cells(c, Range("Product_Type").Column).Interior.ColorIndex = 41
I've seen a similar thing somewhere where it was all done in one statement without the loop.
Vba: Transfer Spreadsheet Method
Im trying to use the transferspreadsheet method to load some data in an access table, but using my current code, it gives me an error, saying that the file is already opened by another user, or that i need permission to view it.
that it is open, is true, since I'm loading from that spreadsheet.
this is the code I have now:
Dim accappl As Access.Application
Dim strpathdb As String
Dim strpathxls As String
Dim myrange As Range
Set myrange = Range("a32").CurrentRegion
'path van de database
strpathdb = "X:credepSTRAT_PLANStratPlan.mdb"
'path van de upload file
strpathxls = ActiveWorkbook.FullName
Set accappl = New Access.Application
DoCmd.TransferSpreadsheet acImport, 3, "Tbl_Growth_Metric", strpathxls, False, "myrange"
Spreadsheet That Incorporates A VBA Calendar
I have been trying to complete a excel spreadsheet that incorporates a VBA calendar. You will know the sort of thing that I mean, the user clicks on a cell and a calendar appears then the user clicks a date and the date is added to the active cell. The reason why I have to do this in VBA is because the finished file is going to be stored on my works server and it doesn't have the calendar activex add-in installed.
Well I have made a little progress. However, I have now become stuck. the link below will give you the file that I got so far. If you extract the file then click on cell A1 a calendar will appear. However, you will see that when you try to change the month it is stuck on January and I do not know how to cure the problem.
I should add that the code that is written within this spreadsheet was not written by me. It was originally written by another person whom I am no longer able to contact. Unfortunately my knowledge of VBA is not that of the original author.
Vba To Create A New File From Spreadsheet
I have the following spreadsheet:
File No.-----PRODUCT TYPE
What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456.
If Formula: =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good Shape","Bad Shape"))
I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.
VBA Stuff- Weight Watcher Spreadsheet
I have a Weight Watcher spreadsheet. I enter the Calories, Fat and Fiber and I get the # of points for that food. Then I keep put those points into the lower part of the spreadsheet where I track what I eat, and Excel does my simple math for me. What I want to do is keep a list of my foods, and their point value in my spreadsheet. I would like to be able to put the food name in Cell A1, the WW points in BI, and then be able to click a button with a macro that takes those two pieces of data and add them to bottom of my food list. I don't have a food list yet, I would assume I can just put it on sheet 2. I then want to be able to use lists in my cells to choose the food in the future. I need to be reminded how to make "dynamic named ranges. Today, I just need to know how to write the macro to copy and paste those two cells in the first open row at the bottom of my food list.
Adjusting VBA Code From A Spreadsheet List
below is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code
Vba To Isolate Items In Spreadsheet And Compose A List
I have the following spreadsheet:
A----------- B----------------- C
Invoice------ Product Code------Classification
I'd like to extract the rows with blank cells in Column C and product codes in Column B and create a list of product codes which don't have classifications on a separate list.
This new list would need the ability to be revised as other codes will be added once a macro is run.
#NAME- Error Results When Writing Formula To Spreadsheet From VBA
When I am writing a formula that includes a lookup, it returns the # NAME? error in the spreadsheet. If I delete the trailing ), retype it, and hit enter then the formula returns the correct value. How do I get the formula to work correctly as it is written to the sheet from VBA!
Here's the line of code that writes the formula to the worksheet:
TBox.Range("A3").Offset(i, 3).FormulaR1C1 = "= ROUNDUP((8-(VLOOKUP(RC[-3],K:L,2,FALSE)/1.2))/RC[-1]*1.2,0)"
I have a workbook with 52 worksheets.
6 are hidden, 46 are visible.
I am trying to move a worksheet to the end of the workbook and rename as the next sheet.
It works the first time I run it (it renames the worksheet to Client51, the new last worksheet) but after that it gives me a 1004 error 'cannot rename a sheet to the same name as another sheet...."
Here is the macro I am using:
Dim NewName As String
NewName = "Client" & Sheets.Count - 2
ActiveSheet.Name = NewName
Move Function In Macro
I have a piece of my macro that creates a chart on the second sheet in my workbook.
However, it tends to appear way off to the right, and then I have to readjust it every time to be where I'm working (columns A-D approx.)
Can I improve the move to specify it to place the graph in a certain
All I'm currently doing is:
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
Macro To Move Columns And
I started a project where I want to export data from Access (only 2 columns but # of rows vary every day). When the data that is copied to the first tab is a week old and is a Tuesday I want to copy and paste these values in the next tab.
Macro To Move Text
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 .
Macro To Move To A Column
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.
Macro To Move Rows And Organize
I have been trying to do this for 3 days using "Record Macro", but something always seems to be messed up. The main problem is that the worksheets are protected and the macro asks for the password.
Here is a calendar sheet that I've been working on. It contains the calendar on sheet 1(not important) and a client roster on sheet 2. The client roster gets filled quickly, and obviously gets unorganized. What I'd like to do is create a macro so that when a date is entered into column AA (when a client is discharged), that entire row is moved onto sheet 3 (Discharged). At the same time, I'd like all of the names in column A alphabetized.
I even tried to create an "Organize" button so that the users could just click it and alphabetize. The problem I ran into with that was that the users are using Office 2000, and the names get alphabetized but all of their information no longer matches up.
Macro To Find And Move A Column
I regularly receive a file with a column called ‘Project Key’ which for some unknown reason can be located anywhere in the worksheet i.e. in any column from A: to BZ and beyond.
What I would like to have is a macro that finds the column ‘Project Key’ and moves it from it’s current position and moves it directly after a column call ‘XXX’ and delete the blank column where it orginally was. There would be no need to check if the columns exceed Excels maximum as my file never gets that big
Adapting Mouse Move Macro
Is it possible to display a name on a chart that is not the X or Y value?
I've been sent this macro which displays the school name on a chart when the mouse is on the data point - its all well and good but on the graph I am working on, the X and Y value are both values and I want the school name to come up as well.
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'macro that gets chart to show user details of a point on the chart when the mouse is moved over it
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX, myY As Variant
.GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then 'checks to see which part of the chart the mouse is over
Macro To Move Row To Another Worksheet
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
Macro To Move Files Across A Network
I would like to include code to move or copy various csv files from one computer across a network to other computers.
To date the best I have come up with is a method of running a batch file that executes the action.
Call Shell("C:MoveTKRS.bat", vbNormalFocus)
I would rather a more direct code that does not require a batch file.
Move Cell Locatin In A Macro
Below is my the macro that I recorded. I want to copy and paste the same information to a new location everytime. The macro tells it to go to cell IV1, end left, then I want it to go right one cell regardles of the cell location. Here it tells it to got to ("K1"). It will do this everytime and I do not want it to go to K1, just move right one time and then paste the information.
Macro To Move Entire Column
I have Excel 2002. I need to create a formula that will move an entire row to another page within the same workbook if a cell in that row has a certain value. Example would be if A1 cell in the row has a value of 111 I need to move the entire row to another page. I could use the sort then cut function in a macro but the spread sheet I'm working with changes daily. One day there will be 10 A1 cells with 111 the next 30 cells with 111 in A1 and some days no cells with 111.
Macro To Move Values Into Columns
I'm creating a mailing list by pasting text into a spreadsheet, then spending hours cutting and pasting the company name into column c, address into column d, city into e...and so on. Then move down 5 rows and do it again.
I'm not sure where to start, I recorded several macros but none have worked.
Importing Data From Multiple Word Forms Into A Single Spreadsheet With Vba
I would like to use a vba procedure/procedures to achieve the following:
I have a folder with many Word2003 forms in and I want to save just the data from each form and then import the data into an Excel spreadsheet.
Currently I am opening each .doc file in turn, saving just the data to a new plain text (comma separated file) in a different folder and am unable code searching that folder for all the text files and importing them into the spreadsheet.
I have a two part question to my current approach:
1) I am 99% there with the first part (opening and converting the forms) with the following code having followed advice from another thread but I need Word open and not showing an open document. Is it possible to add code to take care of opening Word in the background and close it again after so the process is fully automated?: