Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move AutoShape in Sync with Date
I am looking for a way to move an AutoShape, automatically, to be in sync
with the current date. Basically, I have a GanttChart and Id like to see the AutoShape moving to the right as time passes. Does anyone know of a good sample of such a thing? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move AutoShape in Sync with Date
You may be able to change the .left property of the shape, but it's tough to
know how much you want to move it based on the date. You'll have to figure that out yourself. "ryguy7272" wrote: I am looking for a way to move an AutoShape, automatically, to be in sync with the current date. Basically, I have a GanttChart and Id like to see the AutoShape moving to the right as time passes. Does anyone know of a good sample of such a thing? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move AutoShape in Sync with Date
Your question isn't entirely clear, but the following may get you
going in the right direction. First, put some shape on the worksheet and name it "TheShape". Then, name some cell "BaseDate" and put the date from which the shape should be offset, e.g,. 1-Jan-2009. Then, name another cell "CurrentDate". In that cell, put the value which, when offset from BaseDate, indicates how far to the right the shape should be moved. To set the vertical position of the shape, select a cell in column A in the row in which you want TheShape to appear and name that cell "ShapeAnchor". When moved, the top of the shape will be equal to the top of the ShapeAnchor cell. Now, right-click on the sheet tab, choose View Code, and paste the following in the code module. Private Sub Worksheet_Change(ByVal Target As Range) Dim N As Long Dim R As Range If StrComp(Target.Address, Range("CurrentDate").Address, _ vbBinaryCompare) = 0 Then N = Range("CurrentDate").Value - Range("BaseDate").Value Set R = Range("ShapeAnchor").Offset(0, N) With Me.Shapes("TheShape") .Top = R.Top .Left = R.Left End With End If End Sub Close the VBA editor and return to Excel. As an example, assume that BaseDate is cell A3 and contains the date 1-Jan-2009. Next, put 8-Jan-2009 in the CurrentDate cell. The code finds the difference between BaseDate and CurrentDate (7 days), and moves TheShape such that the left edge of the shape is that many columns to the right of BaseDate, in the same row as ShapeAnchor. If BaseDate is 1-Jan and CurrentDate is 8-Jan, the left edge of TheShape will be at the left edge of column H, 7 columns to the right of BaseDate. The top of TheShape will be equal to the top of the cell named "ShapeAnchor". Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 16 Aug 2009 16:58:02 -0700, ryguy7272 wrote: I am looking for a way to move an AutoShape, automatically, to be in sync with the current date. Basically, I have a GanttChart and I’d like to see the AutoShape moving to the right as time passes. Does anyone know of a good sample of such a thing? Thanks, Ryan-- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move AutoShape in Sync with Date
Perfect!!! Thanks for the help Chip!
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Chip Pearson" wrote: Your question isn't entirely clear, but the following may get you going in the right direction. First, put some shape on the worksheet and name it "TheShape". Then, name some cell "BaseDate" and put the date from which the shape should be offset, e.g,. 1-Jan-2009. Then, name another cell "CurrentDate". In that cell, put the value which, when offset from BaseDate, indicates how far to the right the shape should be moved. To set the vertical position of the shape, select a cell in column A in the row in which you want TheShape to appear and name that cell "ShapeAnchor". When moved, the top of the shape will be equal to the top of the ShapeAnchor cell. Now, right-click on the sheet tab, choose View Code, and paste the following in the code module. Private Sub Worksheet_Change(ByVal Target As Range) Dim N As Long Dim R As Range If StrComp(Target.Address, Range("CurrentDate").Address, _ vbBinaryCompare) = 0 Then N = Range("CurrentDate").Value - Range("BaseDate").Value Set R = Range("ShapeAnchor").Offset(0, N) With Me.Shapes("TheShape") .Top = R.Top .Left = R.Left End With End If End Sub Close the VBA editor and return to Excel. As an example, assume that BaseDate is cell A3 and contains the date 1-Jan-2009. Next, put 8-Jan-2009 in the CurrentDate cell. The code finds the difference between BaseDate and CurrentDate (7 days), and moves TheShape such that the left edge of the shape is that many columns to the right of BaseDate, in the same row as ShapeAnchor. If BaseDate is 1-Jan and CurrentDate is 8-Jan, the left edge of TheShape will be at the left edge of column H, 7 columns to the right of BaseDate. The top of TheShape will be equal to the top of the cell named "ShapeAnchor". Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 16 Aug 2009 16:58:02 -0700, ryguy7272 wrote: I am looking for a way to move an AutoShape, automatically, to be in sync with the current date. Basically, I have a GanttChart and Id like to see the AutoShape moving to the right as time passes. Does anyone know of a good sample of such a thing? Thanks, Ryan-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically change autoshape color to that of another autoshape | Excel Programming | |||
Sync Button | Excel Programming | |||
Sync | Excel Programming | |||
Sync - Bob Phillips | Excel Programming | |||
ProgressBar Out of Sync | Excel Programming |