Is there a way to make a macro relative to a button position? I want t
have a macro input a set value into a cell that is beside the button.
The sheet I am desiring has hundreds of buttons that all do the sam
exact thing - place an "X" into the cell beside the button - I don'
feel like writing hundreds of individual macros, so I am hoping ther
is a way to make the action relative to the button, not absolute o
relative to the currently selected cell, which are the only two option
i know of.
Please help - this is driving me nuts! THANKS

--
Message posted from http://www.ExcelForum.com

if you are using buttons from the forms toolbar

Public ButtonClick()
Dim sName as String
Dim btn as Button
Dim rng as Range
sName = Application.Caller
set btn = Activesheet.Buttons(sName)
set rng = btn.TopLeftCell.Offset(0,1)
if rng.Value = "X" then
rng.clearcontents
else
rng.Value = "X"
end if
End Sub

--
Regards,
Tom Ogilvy

wesandem wrote in message
...
Is there a way to make a macro relative to a button position? I want to
have a macro input a set value into a cell that is beside the button.
The sheet I am desiring has hundreds of buttons that all do the same
exact thing - place an "X" into the cell beside the button - I don't
feel like writing hundreds of individual macros, so I am hoping there
is a way to make the action relative to the button, not absolute or
relative to the currently selected cell, which are the only two options
i know of.
Please help - this is driving me nuts! THANKS!


---
Message posted from http://www.ExcelForum.com/

Private Sub CommandButton1_Click()
With CommandButton1
Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1).Value = "X"
End With
End Sub

Rob

"wesandem " wrote in message
...
Is there a way to make a macro relative to a button position? I want to
have a macro input a set value into a cell that is beside the button.
The sheet I am desiring has hundreds of buttons that all do the same
exact thing - place an "X" into the cell beside the button - I don't
feel like writing hundreds of individual macros, so I am hoping there
is a way to make the action relative to the button, not absolute or
relative to the currently selected cell, which are the only two options
i know of.
Please help - this is driving me nuts! THANKS!


---
Message posted from http://www.ExcelForum.com/

I am using the buttons from the forms toolbar, but your post is a little
over my head I'm afraid. Do I just plug that into a module in VB?
This is the first time I've had to do more than just record macros -
seeing the code is freaking me out a little. THANKS AGAIN!


---
Message posted from http://www.ExcelForum.com/

Rob, I don't see that this solves my problem because I'd still have to
do a separate code for every button unless I'm missing something.
Tom, I am too dumb to understand your post, other than the part about
forms, which the answer is yes, I can. Do I just insert that into a
module? I tried and I get a compile error and "application.caller" is
highlighted. I am not great at VB stuff - I have mostly just recorded
macros - seeing the code is kind of freaking me out! Please help -
from what I can tell it looks like what you've got there is exactly
what I need!
THANKS!


---
Message posted from http://www.ExcelForum.com/

In that case use the code that Tom supplied. There was a small typo in that
code, the first line should read

Public Sub ButtonClick()

Right-click on the button, and 'Assign Macro...' to this macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"wesandem " wrote in message
...
I am using the buttons from the forms toolbar, but your post is a little
over my head I'm afraid. Do I just plug that into a module in VB?
This is the first time I've had to do more than just record macros -
seeing the code is freaking me out a little. THANKS AGAIN!


---
Message posted from http://www.ExcelForum.com/

Sorry - I should have paid more attention.
Learned that you're using Form buttons, not Control buttons.

Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1).Value = "X"
End With
End Sub

Then assign this macro to each button.

Rob



"wesandem " wrote in message
...
Rob, I don't see that this solves my problem because I'd still have to
do a separate code for every button unless I'm missing something.
Tom, I am too dumb to understand your post, other than the part about
forms, which the answer is yes, I can. Do I just insert that into a
module? I tried and I get a compile error and "application.caller" is
highlighted. I am not great at VB stuff - I have mostly just recorded
macros - seeing the code is kind of freaking me out! Please help -
from what I can tell it looks like what you've got there is exactly
what I need!
THANKS!


---
Message posted from http://www.ExcelForum.com/

Yes, put it in a general module and assign it to all your buttons.

You can't run it manually. But if you assign it to a button, then
application.caller won't cause a problem - it will return the name of the
button that called it.

As Bob said, the declaration should have been

Public Sub ButtonClick()

--
Regards,
Tom Ogilvy


wesandem wrote in message
...
Rob, I don't see that this solves my problem because I'd still have to
do a separate code for every button unless I'm missing something.
Tom, I am too dumb to understand your post, other than the part about
forms, which the answer is yes, I can. Do I just insert that into a
module? I tried and I get a compile error and "application.caller" is
highlighted. I am not great at VB stuff - I have mostly just recorded
macros - seeing the code is kind of freaking me out! Please help -
from what I can tell it looks like what you've got there is exactly
what I need!
THANKS!


---
Message posted from http://www.ExcelForum.com/

Thanks for the help guys - looks like it's working perfectly now. On
more thing I should have asked earlier - what would I change if I wan
this action to apply to not just the adjacent cell but to the next fiv
cells?

--
Message posted from http://www.ExcelForum.com

I guess it depends on whose code you are using.

But look at

..resize(1,5)

--
Regards,
Tom Ogilvy


wesandem wrote in message
...
Thanks for the help guys - looks like it's working perfectly now. One
more thing I should have asked earlier - what would I change if I want
this action to apply to not just the adjacent cell but to the next five
cells?


---
Message posted from http://www.ExcelForum.com/

I could look at it all day long, but I don't know what you mean by it.


---
Message posted from http://www.ExcelForum.com/

Why not just show the code you are using and you won't have to figure out
how to apply it.

--
Regards,
Tom Ogilvy

wesandem wrote in message
...
I could look at it all day long, but I don't know what you mean by it.


---
Message posted from http://www.ExcelForum.com/

Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
With Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1)
.Value = "X"
.AutoFill .Resize(1, 5), xlFillCopy
End With
End With
End Sub


"wesandem " wrote in message
...
Thanks for the help guys - looks like it's working perfectly now. One
more thing I should have asked earlier - what would I change if I want
this action to apply to not just the adjacent cell but to the next five
cells?


---
Message posted from http://www.ExcelForum.com/

Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
With Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1)
.Resize(1,5).Value = "X"
End With
End With
End Sub

Was more what I had in mind.

--
Regards,
Tom Ogilvy

Rob van Gelder wrote in message
...
Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
With Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1)
.Value = "X"
.AutoFill .Resize(1, 5), xlFillCopy
End With
End With
End Sub


"wesandem " wrote in message
...
Thanks for the help guys - looks like it's working perfectly now. One
more thing I should have asked earlier - what would I change if I want
this action to apply to not just the adjacent cell but to the next five
cells?


---
Message posted from http://www.ExcelForum.com/

Excellent - Resize(1, 5).Value sets all the cells at once! I had expected it
would only set the first cell. Learn something new every day... :)


"Tom Ogilvy" wrote in message
...
Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
With Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1)
.Resize(1,5).Value = "X"
End With
End With
End Sub

Was more what I had in mind.

--
Regards,
Tom Ogilvy

Rob van Gelder wrote in message
...
Public Sub Button_Click()
With ActiveSheet.Buttons(Application.Caller)
With Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1)
.Value = "X"
.AutoFill .Resize(1, 5), xlFillCopy
End With
End With
End Sub


"wesandem " wrote in message
...
Thanks for the help guys - looks like it's working perfectly now. One
more thing I should have asked earlier - what would I change if I want
this action to apply to not just the adjacent cell but to the next
five
cells?


---
Message posted from http://www.ExcelForum.com/