| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use shape location to clear cells
I would like to use a series of shapes to clear cells on a worksheet. For
example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
Hi
Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
I had tried to use some similar code previously, but in both cases get a Type
mismatch error. I'm using Excel 2003 if that matters. Any ideas? Thanks. "Ron de Bruin" wrote: Hi Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
Do you use controls from the Forms or Control toolbox
-- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I had tried to use some similar code previously, but in both cases get a Type mismatch error. I'm using Excel 2003 if that matters. Any ideas? Thanks. "Ron de Bruin" wrote: Hi Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
Actually neither. I was hoping to link the code via a macro to an autoshape
drawing object. If I must use controls, I would prefer to use the Control toolbox. Thanks. "Ron de Bruin" wrote: Do you use controls from the Forms or Control toolbox -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I had tried to use some similar code previously, but in both cases get a Type mismatch error. I'm using Excel 2003 if that matters. Any ideas? Thanks. "Ron de Bruin" wrote: Hi Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
It is working with a autoshape drawing and forms controls
Add a few autoshapes on your sheet Assign this macro to all of them Sub test() Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select End Sub -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... Actually neither. I was hoping to link the code via a macro to an autoshape drawing object. If I must use controls, I would prefer to use the Control toolbox. Thanks. "Ron de Bruin" wrote: Do you use controls from the Forms or Control toolbox -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I had tried to use some similar code previously, but in both cases get a Type mismatch error. I'm using Excel 2003 if that matters. Any ideas? Thanks. "Ron de Bruin" wrote: Hi Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use shape location to clear cells
Or
rw = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Row Ron de Bruin wrote: Hi Try this Dim rw As Long rw = Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address).Row Range(Cells(rw, "A"), Cells(rw, "D")).Select -- Regards Ron De Bruin http://www.rondebruin.nl "Newt" wrote in message ... I would like to use a series of shapes to clear cells on a worksheet. For example: Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1 Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2 and so on. I need the references to be relative because I regularly re-sort the sheet and the order of the shapes changes with all the cells. Basically, I want a sub to figure out where the image is located and then clear the two adjacent cells. Any ideas? Thanks! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserted Shape Prints in Different Location on Sheet | Excel Discussion (Misc queries) | |||
| Want to prevent users from changing location of shape textboxes | Excel Programming | |||
| Shape Name and Location Report - an example | Excel Programming | |||
| Is it Possible to Find Shape at a Location (#find) for XL97? | Excel Programming | |||
| Lock shape location to axis values? | Charts and Charting in Excel | |||
Hybrid Mode
