Relative positions from a button
 

Hi,
I have a button (currently a form button but could be changed) on a sheet
that adds 1 to a value elsewhere on the sheet. This setup is replaicted for
other rows.
the macro currently increments the value by using the offset method relative
to A1.
I would like to know if there is a way to do increment the value using the
relative position to the button either by assigning the button to a cell or
another way?

Thanks

Relative positions from a button
 

Option Explicit
sub testme()
dim myBTN as button
set myBtn = activesheet.buttons(application.caller)

mybtn.topleftcell.offset(0,3).value = "hi there"
end sub

You can assign this kind of code to all the buttons (from the Forms toolbar).

Steve wrote:

Hi,
I have a button (currently a form button but could be changed) on a sheet
that adds 1 to a value elsewhere on the sheet. This setup is replaicted for
other rows.
the macro currently increments the value by using the offset method relative
to A1.
I would like to know if there is a way to do increment the value using the
relative position to the button either by assigning the button to a cell or
another way?

Thanks

--

Dave Peterson

Relative positions from a button
 

the button has both TopLeftCell and BottomRightCell properties.

You could use that

Sub Btn_click()
Dim btn as Button, rng as Range
set btn = Activesheet.Buttons(application.Caller)
set rng = btn.TopLeftCell
rng.offset(0,1).Value = rng.offset(0,1).Value + 1
End Sub

as an example, could be assigned to any such button designed to perform this
function.

--
Regards,
Tom Ogilvy


"Steve" wrote:

Hi,
I have a button (currently a form button but could be changed) on a sheet
that adds 1 to a value elsewhere on the sheet. This setup is replaicted for
other rows.
the macro currently increments the value by using the offset method relative
to A1.
I would like to know if there is a way to do increment the value using the
relative position to the button either by assigning the button to a cell or
another way?

Thanks

Relative positions from a button
 

Thanks for your answer, this is perfect.

Relative positions from a button
 

Sorry - guess I misunderstood the explanation you gave when I offered my
suggestion. I thought you wanted to increment the cell when you really
wanted to enter "Hi there" in it. Is that correct?

--
Regards,
Tom Ogilvy


"Steve" wrote:

Thanks for your answer, this is perfect.