• 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.

Activate cell the control sits in

idahodave

New Member
Hello. In VBA, how do I activate the cell in which the macro control (in this case a button) sits? I'm sure this is something simple but I'm banging my head trying to figure it out. For a bigger picture, I'm trying to delete the current column, when I click a control in THAT column. If a cell somewhere else in the worksheet is active, then I need to move to the current column upon clicking the button, before I delete it. I can solve for the deleting, I just need it to get to that spot in the first place. I appreciate any help. Thanks.
 
I think there is no simple way to find out which row an object (button, text box or shape) sits in. Objects do not really belong to any cells, rather they float on cell lay out. So you only object.top, object.left. There is a way to figure out the row number from object.top by looping thru worksheet rows and check which row's top matches with object's top. But I would not recommend it, for following reasons:


- Object (in your case button) may span more than one row

- Object may not start exactly at row start due to poor alignment


Given all this, I suggest not trying to do this and instead figuring out some other way to remove the row. May be you can ask user to select the rows they want to delete or mark a check box against that row before clicking on delete button. This is not only user friendly, but also more robust solution.
 
Hi Dave ,


Are you looking for this ? This will work if your Command Button is an ActiveX control.


CommandButton1.TopLeftCell.Select


This selects the cell in which the top left corner of the Command Button is positioned.


Narayan
 
@NARAYANK991


Hi!


Trying to upload an example file just for aiding idhodave, created a new workbook, added an ActiveX command button control in first sheet 'Hoja1' and tried this code but didn't work, it printed blank instead of "B3".

-----

[pre]
Code:
Option Explicit

Sub x()
Debug.Print Worksheets("Hoja1").OLEObjects("CommandButton1").TopLeftCell
End Sub
[/pre]
-----


Am I missing something?


Link to the file:

https://dl.dropbox.com/u/60558749/Activate%20cell%20the%20control%20sits%20in%20%28for%20idahodave%20at%20chandoo.org%29.xlsm


Regards!
 
Hi SirJB7 ,


Insert this in the sheet section where your CommandButton is :

[pre]
Code:
Private Sub CommandButton1_Click()
CommandButton1.TopLeftCell.Select
End Sub
[/pre]
Click on the CommandButton and see what happens.


Narayan
 
@NARAYANK991


Hi!


Thanks for the tip, it worked fine, and in my previous example it was only missing a .Address at the end of the statement.


The annoying thing is that TopLeftCell is almost undocumented for OLE objects within Excel help.


Regards!
 
Back
Top