Problem with Selection.AutoFill

[jsnyder]

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

 

[erik.van.geit]

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

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]

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]

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]

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]

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

 

[erik.van.geit]

OK!
Sie dürfen es schicken! :wink: