CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

Which button selection to use in vba?

I am sure this is probably an easy question. I am trying to assign new macros to buttons that I put on a sheet. Here is my code for assigning the macros to the buttons.

    .Shapes("Atlanta MFG").OnAction = "Atlanta"
    .Shapes("Denver MFG").OnAction = "Denver"
    .Shapes("Louisville MFG").OnAction = "Louisville"

My code runs all the way through. Then this snippet above is the last part. It will assign the Atlanta macro to the Atlanta button, but then after that the code just STOPS. The other buttons do not get assigned and it looks like the code just completely stops. Then I can hit f8 to start it all over again. Why is my code stopping and not assigning the macros to the rest of the buttons after it does the first one? Thank you in advance! I am guessing I need to add some type of other code to make in continue assigning the macros to them.

UPDATE--------------------------------------------------------------

With ActiveSheet
    .BUTTONS.Add(53.25, 36.75, 190.5, 41.25).Select
    .BUTTONS("Button 1").Text = "Atlanta MFG"
    .BUTTONS("Button 1").name = "Atlanta MFG"
    .Shapes("Atlanta MFG").IncrementTop -12

    .BUTTONS.Add(53.25, 36.75, 190.5, 41.25).Select
    .Shapes("Button 2").IncrementLeft 222
    .Shapes("Button 2").IncrementTop -12
    .BUTTONS("Button 2").Text = "Denver MFG"
    .BUTTONS("Button 2").name = "Denver MFG"
    .BUTTONS("Button 7").Text = "Modesto MFG"
    .BUTTONS("Button 7").name = "Modesto MFG"
    .BUTTONS("Button 8").Text = "Seattle MFG"
    .BUTTONS("Button 8").name = "Seattle MFG"
    .BUTTONS("Button 9").Text = "Tustin MFG"
    .BUTTONS("Button 9").name = "Tustin MFG"
    .BUTTONS("Button 10").Text = "York MFG"
    .BUTTONS("Button 10").name = "York MFG"
    .BUTTONS("Button 11").Text = "Button 11"
    .BUTTONS("Button 11").name = "Button 11"
    .BUTTONS("Button 12").Text = "Button 12"
    .BUTTONS("Button 12").name = "Button 12"

    .Shapes("Atlanta MFG").OnAction = "Atlanta"
    .Shapes("Denver MFG").OnAction = "Denver"
    .Shapes("Jackson MFG").OnAction = "Jackson"
    .Shapes("Louisville MFG").OnAction = "Louisville"

    Range("A1").Select

End With

So I am adding like 10 buttons not all of the code is there. I add the buttons then move them some, then I make the Private subs for each location. For example,

Private Sub Atlanta()

MsgBox "Atlanta MFG do your thing!"

End Sub

I have one of those for each location. When I run it, I can click on the Atlanta button, but every one after that I can't click on. They just make it so I can move the box around and not actually run the button.

SECOND UPDATE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Apparently it is working. I am not sure if hitting ctrl-shift-f9 did it or what... but I tried it this morning, hit ctrl-shift-f9, then it all ran. So maybe I was just messing up last night. Thanks for the help!

Upvotes: 1

Views: 251

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4704

If you've ever put a break or watch on a row, Excel can sometimes think it's still there. Try control + shift + F9 to clear them all before trying again

Upvotes: 2

Related Questions