• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Button to know which cell its in

G

gumbles

New Member
Hello all,


Im working with a friend to try and create a generic macro to assign to a button that, when clicked, will move down one cell (from where the button is clicked) and insert a row, simple enough.


The problem we are having is that I dont know how to get the macro to know where it is when the button has been pressed. This is to save me creating a macro for each segment where I need an button in the list, as there a quite a few, and this would make updating the system very arduous.


My VBA skills are limited, but my pal's pretty hot on it, just stuck on this issue.


All comments welcome!


Regards,


Gumbles
 
Hi Gumbles,

This is for where the cursor (activecell) is NOT where the button is.


ActiveCell.Offset(1, 0).Insert Shift:=xlDown, _

CopyOrigin:=xlFormatFromLeftOrAbove


If you for sure you want the insert from where the button is, I don't know.

But I will mess around to see if I can make that work.


Regards,

Howard
 
Hi Howard,


Thanks for the speedy reply.


My button will be a seethrough box overlayed on the cells where I need this macro to operate. So really they will never actually select a cell, which is why we are stuck.


Im fairly sure this is possible as I saw it on a worksheet once but didnt manage to get a copy grrr.


Gumbles
 
Hi Gumbles ,


CommandButton1.TopLeftCell.Address


gives you the address of the cell in which the button is located. This applies to an ActiveX control.


In case your button is named differently , change CommandButton1 to its name.


Narayan
 
Thanks Narayan,


A very simple and elegant solution, Ill give this a try when Im home.


Do you know if its possible to apply this macro to a shape? as Ideally I would like to make the button invisible.


Gumbles
 
Hi Gumbles ,


It works for even shapes ; the syntax is :


Worksheets("Sheet Name").Shapes("Rectangle 1").TopLeftCell.Address


Replace Sheet Name and Rectangle 1 with the names of your worksheet and shape.


Narayan
 
You must log in or register to reply here.
Back
Top