Form Buttons to Control Box Buttons

southpaw163

New Member
Joined
Aug 28, 2006
Messages
4
I have a spreadsheet that currently has Buttons made from the Form Toolbox, that I would like to switch to Buttons made from the Control Toolbox. I'm having problems transfering the code from a button that adds a row in my spreadsheet. Everytime I copy the code over it gives me an error in the code. The code is:
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Sheets("Hide").Select
Rows("15:15").Select
Selection.Copy
Sheets("Preliminary").Select
ActiveSheet.Paste
Range("C7").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

The error it highlights is Rows("15:15").Select. But the macro works just fine when assigned to the Form Toolbox Button. Is there something I'm over looking? Is there any easy way to fix this problem so I can get rid of my Form Toolbox Buttons?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
When you use buttons from the Control Toolbox then you can have problems if you use select in the code.

Try this.
Code:
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.Rows("7:7").Insert Shift:=xlDown
Sheets("Hide").Rows("15:15").Copy
Sheets("Preliminary").Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 

southpaw163

New Member
Joined
Aug 28, 2006
Messages
4
I'm sorry but I don't know much about code at all, but now I get an error at:

Sheets("Preliminary").Paste

of the code

Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.Rows("7:7").Insert Shift:=xlDown
Sheets("Hide").Rows("15:15").Copy
Sheets("Preliminary").Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Anyone have any ideas on this?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
You may want to consider using AutoShapes as buttons. You assign macros to them exactly like you do Forms buttons, but you have the option to change color, text color, shadow, etc.

From the Drawing Tool Bar, choose AutoShapes | Basic Shapes | Bevel
Drag your button size on the sheet. Right Click to adjust properties and Assign to the Macro of your choice.

No need to use design mode with this button. Easy to change color and font, just right-click and choose Format AutoShape...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Can you explain what the code should do?

What I posted was solely based on that you posted.

And it wasn't clear from that where you wanted to copy to.
 

Forum statistics

Threads
1,141,587
Messages
5,707,247
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top