Macro running, but not working when assigned to a button/shape

[xenosaga]

Good Morning,

I am running a copy and paste macro (that I recorded). It works when I run it or when I use F8, but when I assign it to a button and use it, Excel stops working and closes. There are several buttons that I use to copy and paste different parts of the worksheet to different worksheets. It appears to "break" after I run one of the other buttons (button 1) There are 3 total buttons that do this, the first two work perfectly while the third button keeps breaking on me. Here is the code for the 3 buttons.

Button 1

Sub CreateComputerClass1()
'
' CreateComputerClass1 Macro
'
'
Sheets("Registration Lists").Select
Range("A4:C28").Select
Selection.Copy
Sheets("Class 1").Select
Range("C5").Select
ActiveSheet.Paste
End Sub


Button 2

Sub CreateComputerClass2()
'
' CreateComputerClass2 Macro
'
'
Sheets("Registration Lists").Select
ActiveWindow.SmallScroll Down:=3
Range("A31:C55").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Class 2").Select
Range("C5").Select
ActiveSheet.Paste
End Sub

Button 3


Sub CreateComputerClass3()
'
' CreateComputerClass3 Macro
'
'
Sheets("Registration Lists").Select
Range("A58:C82").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Class 3").Select
Range("C5").Select
ActiveSheet.Paste
End Sub

I tried adding the line Sheets("Registration Lists").Select to see if that would fix the problem even though it is extra, but it didn't work. Usually what I am doing is creating 3 class lists by clicking on these buttons. When I click on button 3 after creating it, it works. When I click on it after I have clicked on button 1, it breaks. I am a VBA noob, but any help you can offer would be greatly appreciated.

 

[JLGWhiz]

It sounds like an application software anomaly because I do not see anything wrong with the recorded code. It could be shortened some by getting rid of the "Selection" and using direct commands. But, there is no obvious reason for the behavior that you describe.

 

[bertie]

Hi and welcome to the forum.

Code generated by the macro recorder usually come with a lot of "ActiveSheet", and "Selections", etc. So the first thing we have to do is tidy the code up. See below:

[COLOR=darkblue]Sub[/COLOR] CreateComputerClass1()
[COLOR=green]' CreateComputerClass1 Macro[/COLOR]
   Sheets("Registration Lists").Range("A4:C28").Copy _
      Destination:=Sheets("Class 1").Range("C5")
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Sub[/COLOR] CreateComputerClass2()
[COLOR=green]' CreateComputerClass2 Macro[/COLOR]
   Sheets("Registration Lists").Range("A31:C55").Copy _
      Destination:=Sheets("Class 2").Range("C5")
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




Sub CreateComputerClass3()
[COLOR=green]' CreateComputerClass3 Macro[/COLOR]
   Sheets("Registration Lists").Range("A58:C82").Copy _
      Destination:=Sheets("Class 3").Range("C5")
[COLOR=darkblue]End[/COLOR] Sub

I think you will agree this is a lot easier to read.

Because you are working with more than one worksheet it is best to place the above code in the ThisWorkbook module or, in our instance, Module1.

Click Insert => Module - I assume the module is named Module1.
Copy and paste the above code into Module1.


Buttons
The code for your buttons go into the sheet Module.
Click Developer => Design Mode
Double click on a button.

You will get the skeleton code for the button's Click event.
Type in Module1 followed by a period (.).
You will see a list of the procedures you can assign to the button.

Here is sample code for one of your buttons:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
   Module1.CreateComputerClass1
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I will leave you to code the other buttons. Any problems give me a shout.

Bertei

 

[xenosaga]

Hi Bertei,

Thank you for your response. First, the code almost worked great. I had a problem with the second button. After some playing I discovered that I was only having problems when I was in page layout view on my worksheet. All of the code worked great, but wouldn't work in that particular view mode. Since changing views I have created several more buttons and they have all worked great. Is there any reason for this?


Thank you,

Brandon

 

[bertie]

Hi Brandon,

Page Layout is for setting up printing areas, headers, footers, etc. I wouldn't run a macro form this view.

But having said that I tried switching views; I tested: Normal, Page Layout, Page Break and Full Screen modes, and the buttons/shapes I created triggered my test macros as expected. So, I cannot recreate the problem you experienced. I can only guess it has something to do with the way you assign macros to buttons/shapes.

Sorry I couldn't be of any help in this instance,
Bertie