recorded macro for pagesetup producing error

**suprme32excel** · 07-27-2009, 02:55 PM

I first attempted to record a macro for changing the page setup to print 2 tall by 1 wide. (code below)

I received an error message "Object variable or with block variable not set".

This macro is going inside of an excel add-in. Which leaves me to believe this may be a referencing error.

Please Login or Register  to view this content.

There were more options inside the WITH statement, but I shrunk it down for reading purposes. I tried searching for solutions online and tried a few snippets from others, but all came back with this error message.

Any ideas?

Thanks in advance!

**royUK** · 07-27-2009, 03:22 PM

There's no clue from the posted code, can you attach a sample workbook?

**suprme32excel** · 07-27-2009, 05:09 PM

You can copy and paste that exact code into a module and run it and it works flawlessly for the current project.

However, I am in need of it to be active each time excel opens (add-in), when it is brought into a project as an add-in, the error message is produced.

Which is why I feel it is a referencing error, is there a more precise way to reference the worksheet?

**royUK** · 07-27-2009, 05:20 PM

It's working on the activesheet so unless the code sets a reference to activeworkbook somewhere else it should work.

I'm not sure what you mean by "brought into a project as an add-in"

**suprme32excel** · 07-27-2009, 05:37 PM

sorry for the ambiguity,
what I mean by that is:
If you were to put that code into a workbook and save it as an add-in. Then include that add-in to excel so that each time excel loads that code is available to your current project. Hence the code isn't being created each time excel loads, it is being "brought into" each project through the add-ins.

As I said, this code works perfectly except when being used as an add-in.

I hope that helps!

**suprme32excel** · 07-27-2009, 05:51 PM

here is the ultimate goal:

having a command button in the toolbar that when pressed will format the open worksheet.

everything works perfectly fine except for the page setup.

I have attached two files for you:

formatter - copy.xls - you will need to change this extension to .xla, I had to change it so I could upload it. I would like this to be used as an add-in, with a button in the toolbar to run the code.

the other file is the unformatted document

User would open the unformatted document, press the button and all the changes would take place. Finally saving the formatted document to the same folder.

I appreciate the help!