Repeat buttons

[xatomicx]

Hi everyone,

I've got this piece of VBA that seems to do what i want:

-------

Sub CreateFormsButton()
Dim btn As Button
Dim rng As Range
With Worksheets("hdcore1")
Set rng = .Range("h4")
Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.Caption = "Coaching"
.OnAction = "email sent"
End With
End With
End Sub

----------

It creates a button on the h4 cells with a "fit to cell size", can i modify this code to have this button create not only on h4, but i want this button to be on cell h4 to h1000. So i need to modify this code to do some sort of copy paste on multiple cell.

If i modify the range to h4;h1000, it will create 1 cell the size of my request, but i need 1000 buttons in 1 code.

Any help on how to loop or modify a quantity?

thanks!

[TMS]

You could do something like this:

Sub CreateMultipleFormsButton()
Dim btn As Button
Dim rng As Range
With Worksheets("hdcore1")
For i = 1 To 1000
    Set rng = .Range("h" & i)
    Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
    With btn
        .Caption = "Coaching "
        .OnAction = "email sent"
    End With
Next 'i
End With
End Sub

Regards

[xatomicx]

great idea! thank you very much,

But i get a VB error 400 when i apply the code, i can't find out why, anyone knows what doesnt work in this code? can't pinpoint it

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[royUK]

Add the code tags before I lock the post

[xatomicx]

here we go

Sorry!

[xatomicx]

i've found something,

This code works

Sub CreateMultipleFormsButton()
Dim btn As Button
Dim rng As Range
With Worksheets("hdcore1")
For i = 4 To 1000
    Set rng = .Range("h" & i)
    Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
    With btn
        .Caption = "Coaching "
    End With
Next i
End With
End Sub

i remove the onaction, but now i can't click on it to make it change from coaching, to email sent, anyone can help reintegrate the onaction so it change name when i click on it?

[TMS]

.OnAction determines the *macro* that is run when the button is clicked. Of of the things that macro could do is to change the caption.

I rather assumed that you had the rest of the code to go with the button you've(s) created.

The error comes from the fact that you have/had a space in the text.

Change it to: .OnAction = "eMailSent"

But, as stated above, you need a macro called "eMailSent" to send the email (?) and change the caption.

Regards

[xatomicx]

Thanks TMSchucks!

The macro for email sent is not created yet no,

I'll first figure out how i want my buttons to be displayed, and i'll give a shot at doing the macro for email, i've found pretty clear tutorial on how to do so,

I will get back with a post here if i'm lost

Thank you!