Problem with Selection.AutoFill

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
I am having a problem with some code attached to an OptionButton in Excel. The code is shown below. The problem is that when I run it I get an error at the first Selection.AutoFill line, and I'm not sure what causes the code to break. This code runs fine as a stand-alone macro, but does not function properly when associated with the OptionButton. Any ideas or suggestions would be greatly appreciated.

j


Private Sub OptionButton10_Click()
Sheets("trck-data").Select
ActiveSheet.Range("A4").Select
ActiveCell.FormulaR1C1 = "=R[152]C"
ActiveSheet.Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A16"), Type:=xlFillDefault
ActiveSheet.Range("A4:A16").Select
Selection.AutoFill Destination:=Range("A4:O16"), Type:=xlFillDefault
ActiveSheet.Range("A4:O16").Select
Sheets("trck-all").Select

End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, j,

Welcome to the Board!
You didn't say which error occured: what's the message?
example: If your sheet is protected then you get error 1004 : "you can't change ..."

in fact you can - when I see everything clear enough - substitute your code with
Code:
Sheets("trck-data").Select 
Range("A4:O16").FormulaR1C1 = "=R[152]C"
Sheets("trck-all").Select
no need to select whatever
Range("A4:O16").Select
Selection.FormulaR1C1 = "=R[152]C"

and no need to fill down what you can do in a single operation
also in "real-time" you can select the entire range type "=A156" and then hit CTRL-ENTER

kind regards,
Erik
 

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
Sorry, I forgot to enter the error I received. It said "AutoFill method of Range class failed". I will try the code you supplied, thanks!
 

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
Thanks Erik, the code you supplied is a much better method. Although for some reason I still needed to use the ActiveSheet. prefix for the Range selection when it's attached to the OptionButton. Again, it works fine as a macro, but when I have it on the object it behaves slightly different.

Thanks for the quick help!

j
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

j,

What do you prefer: being practical or theoretical? A good mix is good for me! So if you want you can send to me (a part) of your workbook: then I'll take a look at it.

kind regards,
Erik
 

jsnyder

New Member
Joined
Jan 17, 2005
Messages
15
I would prefer to include a mix of theory and practicality as well, unfortunately I'm still a beginner, so I tend to use the first method I find that works rather than searching for other options. I can't send a copy of the file because it contains confidential client info. If I need more help I will try to remove the client info and send a "clean" copy. I do appreciate the offer! Danke schoen,

j
 

Forum statistics

Threads
1,147,844
Messages
5,743,511
Members
423,800
Latest member
IuneKeiki

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