Macro relative to button position

  • Thread starter Thread starter wesandem
  • Start date Start date
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
 
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
 
Private Sub CommandButton1_Click()
With CommandButton1
Cells(.TopLeftCell.Row, .BottomRightCell.Column + 1).Value = "X"
End With
End Sub

Rob
 
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!
 
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!
 
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)
 
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
 
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()
 
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?
 
Why not just show the code you are using and you won't have to figure out
how to apply it.
 
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
 
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
 
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.
 
Back
Top