The cell address of a button?
 

I have a worksheet with data from each month arranged in
rows. For example, Rows 1-5 have January data, row 6 is
blank, Rows 7-12 have February data, etc.

In the first row of each month's data (in Column E), I
have a button that allows the user to erase the data only
for the month associated with the button. Is there any
way to determine where (Row and Column address) this
button is? On different sheets, the buttons may be at Row
10, 20, 30... for example.

I would like my macro code to determine where the button
is and delete only the CurrentRegion.

Thank you so much for your help.

The cell address of a button?
 

Depends on what type of button.

If from the forms toolbar, then application.Caller will return the name of
the button and you can use

msgbox Activesheet.Buttons(application.Caller).TopLeftCel l.Address

--
Regards,
Tom Ogilvy

" wrote in message
...
I have a worksheet with data from each month arranged in
rows. For example, Rows 1-5 have January data, row 6 is
blank, Rows 7-12 have February data, etc.

In the first row of each month's data (in Column E), I
have a button that allows the user to erase the data only
for the month associated with the button. Is there any
way to determine where (Row and Column address) this
button is? On different sheets, the buttons may be at Row
10, 20, 30... for example.

I would like my macro code to determine where the button
is and delete only the CurrentRegion.

Thank you so much for your help.

The cell address of a button?
 

Thanks for your very quick reply.
My button is from the forms toolbar, and this does give
the correct address. How do I make this address into the
ActiveCell?

-----Original Message-----
Depends on what type of button.

If from the forms toolbar, then application.Caller will
return the name of
the button and you can use

msgbox Activesheet.Buttons
(application.Caller).TopLeftCell.Address

--
Regards,
Tom Ogilvy

"
wrote in message
...
I have a worksheet with data from each month arranged in
rows. For example, Rows 1-5 have January data, row 6 is
blank, Rows 7-12 have February data, etc.

In the first row of each month's data (in Column E), I
have a button that allows the user to erase the data
only
for the month associated with the button. Is there any
way to determine where (Row and Column address) this
button is? On different sheets, the buttons may be at
Row
10, 20, 30... for example.

I would like my macro code to determine where the button
is and delete only the CurrentRegion.

Thank you so much for your help.


.

The cell address of a button?
 

Range(ActiveSheet.Buttons(Application.Caller).TopL eftCell.Address).Activate

Doug

wrote in message
...
Thanks for your very quick reply.
My button is from the forms toolbar, and this does give
the correct address. How do I make this address into the
ActiveCell?

-----Original Message-----
Depends on what type of button.

If from the forms toolbar, then application.Caller will
return the name of
the button and you can use

msgbox Activesheet.Buttons
(application.Caller).TopLeftCell.Address

--
Regards,
Tom Ogilvy

"
wrote in message
...
I have a worksheet with data from each month arranged in
rows. For example, Rows 1-5 have January data, row 6 is
blank, Rows 7-12 have February data, etc.

In the first row of each month's data (in Column E), I
have a button that allows the user to erase the data
only
for the month associated with the button. Is there any
way to determine where (Row and Column address) this
button is? On different sheets, the buttons may be at
Row
10, 20, 30... for example.

I would like my macro code to determine where the button
is and delete only the CurrentRegion.

Thank you so much for your help.


.

The cell address of a button?
 

or do it directly:

ActiveSheet.Buttons(Application.Caller).TopLeftCel l.Activate

--
Regards,
Tom Ogilvy


"Doug Glancy" wrote in message
...

Range(ActiveSheet.Buttons(Application.Caller).TopL eftCell.Address).Activate

Doug

wrote in message
...
Thanks for your very quick reply.
My button is from the forms toolbar, and this does give
the correct address. How do I make this address into the
ActiveCell?

-----Original Message-----
Depends on what type of button.

If from the forms toolbar, then application.Caller will
return the name of
the button and you can use

msgbox Activesheet.Buttons
(application.Caller).TopLeftCell.Address

--
Regards,
Tom Ogilvy

"
wrote in message
...
I have a worksheet with data from each month arranged in
rows. For example, Rows 1-5 have January data, row 6 is
blank, Rows 7-12 have February data, etc.

In the first row of each month's data (in Column E), I
have a button that allows the user to erase the data
only
for the month associated with the button. Is there any
way to determine where (Row and Column address) this
button is? On different sheets, the buttons may be at
Row
10, 20, 30... for example.

I would like my macro code to determine where the button
is and delete only the CurrentRegion.

Thank you so much for your help.


.