Prev: Adding to the cell value with a macro
Next: Range Vlookup
From: lallen on 25 Apr 2010 18:13
I recently moved all the code from one workbook to another so that it could
be shared amont multiple books, by making the following change in the
worksheet_activate sub in Sheet1:

from Call Macro1
to Application.Run "'Book2'!Macro1"

Now, some of the code works, and some doesn't. For example:

With Worksheets("Sheet1")
.Columns("A:I").ClearContents ' does nothing
.Cells(10,3) = "Title1" ' works
End With

What am I doinf wrong?
From: joel on 25 Apr 2010 20:25

When you have a statement like this

With Worksheets("Sheet1")

It is using the activeworkbook. Since you have two workbooks that are
opened you are probably accessing the wrong workbook. if you are
refereing to the workbook where the macro is located use Thisworkbook

With Thisworkbook.Worksheets("Sheet1")


You may also want to pass as a parameter to the macro the workbook name

Application.Run "'Book2'!Macro1" "Book1"


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198367

http://www.thecodecage.com/forumz

From: OssieMac on 25 Apr 2010 21:10
When you place code in another workbook then you need to specify which
workbook the code is to be applied to whenever it is run. If this is not done
then the code will most likely operate on whatever workbook is the active
workbook at the time. I am surprised that you say one line of the code works
and the other does not however, I am attributing that to maybe an error when
attempting to post a simplified version of your code.

One way of achieving the above is to pass the workbook to the code as an
argument at the time it is called and pick up the workbook as a parameter in
the called sub. Then use the parameter in the code.

The calling code:
Application.Run "'Book2'!Macro1", ThisWorkbook


The called macro. (Note the parameter wb in the Sub line and the use of
wb.Worksheets("Sheet1") where the variable wb refers to the calling workbook.)

Sub Macro1(wb As Workbook)

With wb.Worksheets("Sheet1")
.Columns("A:I").ClearContents
.Cells(10, 3) = "Title1"
End With

End Sub


--
Regards,

OssieMac


"lallen" wrote:

> I recently moved all the code from one workbook to another so that it could
> be shared amont multiple books, by making the following change in the
> worksheet_activate sub in Sheet1:
>
> from Call Macro1
> to Application.Run "'Book2'!Macro1"
>
> Now, some of the code works, and some doesn't. For example:
>
> With Worksheets("Sheet1")
> .Columns("A:I").ClearContents ' does nothing
> .Cells(10,3) = "Title1" ' works
> End With
>
> What am I doinf wrong?
From: Dave Peterson on 25 Apr 2010 21:12
Except that doesn't explain how the second line worked.

With Worksheets("Sheet1")
.Columns("A:I").ClearContents ' does nothing
.Cells(10,3) = "Title1" ' works
End With

I don't have a good guess why one line would work, but the other not.

I would guess that the code posted in the message wasn't the same as the real
code.

Maybe that difference is enough to hide the real solution to the problem.



joel wrote:
>
> When you have a statement like this
>
> With Worksheets("Sheet1")
>
> It is using the activeworkbook. Since you have two workbooks that are
> opened you are probably accessing the wrong workbook. if you are
> refereing to the workbook where the macro is located use Thisworkbook
>
> With Thisworkbook.Worksheets("Sheet1")
>
> You may also want to pass as a parameter to the macro the workbook name
>
> Application.Run "'Book2'!Macro1" "Book1"
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198367
>
> http://www.thecodecage.com/forumz

--

Dave Peterson
From: lallen on 27 Apr 2010 21:30
That did it. Thank you.

"OssieMac" wrote:

> When you place code in another workbook then you need to specify which
> workbook the code is to be applied to whenever it is run. If this is not done
> then the code will most likely operate on whatever workbook is the active
> workbook at the time. I am surprised that you say one line of the code works
> and the other does not however, I am attributing that to maybe an error when
> attempting to post a simplified version of your code.
>
> One way of achieving the above is to pass the workbook to the code as an
> argument at the time it is called and pick up the workbook as a parameter in
> the called sub. Then use the parameter in the code.
>
> The calling code:
> Application.Run "'Book2'!Macro1", ThisWorkbook
>
>
> The called macro. (Note the parameter wb in the Sub line and the use of
> wb.Worksheets("Sheet1") where the variable wb refers to the calling workbook.)
>
> Sub Macro1(wb As Workbook)
>
> With wb.Worksheets("Sheet1")
> .Columns("A:I").ClearContents
> .Cells(10, 3) = "Title1"
> End With
>
> End Sub
>
>
> --
> Regards,
>
> OssieMac
>
>
> "lallen" wrote:
>
> > I recently moved all the code from one workbook to another so that it could
> > be shared amont multiple books, by making the following change in the
> > worksheet_activate sub in Sheet1:
> >
> > from Call Macro1
> > to Application.Run "'Book2'!Macro1"
> >
> > Now, some of the code works, and some doesn't. For example:
> >
> > With Worksheets("Sheet1")
> > .Columns("A:I").ClearContents ' does nothing
> > .Cells(10,3) = "Title1" ' works
> > End With
> >
> > What am I doinf wrong?
 | 
Pages: 1
Prev: Adding to the cell value with a macro
Next: Range Vlookup