Macro relative to button position

W

wesandem

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
 
T

Tom Ogilvy

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
 
R

Rob van Gelder

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

Rob
 
W

wesandem

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!
 
W

wesandem

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!
 
B

Bob Phillips

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)
 
R

Rob van Gelder

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
 
T

Tom Ogilvy

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()
 
W

wesandem

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?
 
T

Tom Ogilvy

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

Rob van Gelder

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
 
T

Tom Ogilvy

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 said:
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
Click to expand...
 
R

Rob van Gelder

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 said:
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.
Click to expand...
 
Top