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.
Activate cell the control sits in
- Thread starter idahodave
- Start date
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.
- 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.
NARAYANK991
Excel Ninja
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
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
SirJB7
Excel Rōnin
@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]
[/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!
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-----
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!
NARAYANK991
Excel Ninja
Hi SirJB7 ,
Insert this in the sheet section where your CommandButton is :
[pre]
[/pre]
Click on the CommandButton and see what happens.
Narayan
Insert this in the sheet section where your CommandButton is :
[pre]
Code:
Private Sub CommandButton1_Click()
CommandButton1.TopLeftCell.Select
End SubClick on the CommandButton and see what happens.
Narayan