Bug: Call Sub in other book
 

I have this code in workbook#1

Global wb as workbook

sub dowhat()
wb.activate <<<< error here
...more code
end sub

============
and this code in a sheet of workbook#2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

application.run "workbook1.xls!dowhat"
End Sub


The problem is that dowaht works if run from a macro in workbook#1 but not
when called from workbook#2 when a cell changes. The error I get is "Object
Required" which I assume is because "wb" is empty. Can someone please let me
know what I am missing.

thanks

Bug: Call Sub in other book
 

Assuming that what you want is for dowhat() to go back to workbook#2. To do
that, you need to let dowhat() know where it's being called from by using a
parameter containing the calling workbook's name...


Global wb as workbook

sub dowhat(strWorkbook as string) <==changed
set wb = strworkbook <==added
wb.activate
...more code
set wb = nothing <==added
end sub


Private Sub Worksheet_Change(ByVal Target As Range)
dim strMacro2Run as string <==added
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

strMacro2Run = "workbook1.xls!dowhat(" & ActiveWorkbook.Name & ")" <==added
application.run strMacro2Run <==changed
End Sub


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jotor14" wrote:

I have this code in workbook#1

Global wb as workbook

sub dowhat()
wb.activate <<<< error here
...more code
end sub

============
and this code in a sheet of workbook#2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

application.run "workbook1.xls!dowhat"
End Sub


The problem is that dowaht works if run from a macro in workbook#1 but not
when called from workbook#2 when a cell changes. The error I get is "Object
Required" which I assume is because "wb" is empty. Can someone please let me
know what I am missing.

thanks

Bug: Call Sub in other book
 

Actually not really. I just want the code in dowhat to run. The global "wb"
is set earlier to point to workbook#1.For some reason this global is invalid
when called from workbook#2.

I've tried a similar test just using a global integer set by workbook#1 and
displayed by a sub in workbook #1 when called by the workbook#2 worksheet
function and that worked fine.

Maybe there's something special about objects ??


"Gary L Brown" wrote:

Assuming that what you want is for dowhat() to go back to workbook#2. To do
that, you need to let dowhat() know where it's being called from by using a
parameter containing the calling workbook's name...


Global wb as workbook

sub dowhat(strWorkbook as string) <==changed
set wb = strworkbook <==added
wb.activate
...more code
set wb = nothing <==added
end sub


Private Sub Worksheet_Change(ByVal Target As Range)
dim strMacro2Run as string <==added
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

strMacro2Run = "workbook1.xls!dowhat(" & ActiveWorkbook.Name & ")" <==added
application.run strMacro2Run <==changed
End Sub


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jotor14" wrote:

I have this code in workbook#1

Global wb as workbook

sub dowhat()
wb.activate <<<< error here
...more code
end sub

============
and this code in a sheet of workbook#2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

application.run "workbook1.xls!dowhat"
End Sub


The problem is that dowaht works if run from a macro in workbook#1 but not
when called from workbook#2 when a cell changes. The error I get is "Object
Required" which I assume is because "wb" is empty. Can someone please let me
know what I am missing.

thanks

Bug: Call Sub in other book
 

My apologies. For some reason, my code is working now. I must have been
improperly testing.

thanks for your help.

john

"jotor14" wrote:

Actually not really. I just want the code in dowhat to run. The global "wb"
is set earlier to point to workbook#1.For some reason this global is invalid
when called from workbook#2.

I've tried a similar test just using a global integer set by workbook#1 and
displayed by a sub in workbook #1 when called by the workbook#2 worksheet
function and that worked fine.

Maybe there's something special about objects ??


"Gary L Brown" wrote:

Assuming that what you want is for dowhat() to go back to workbook#2. To do
that, you need to let dowhat() know where it's being called from by using a
parameter containing the calling workbook's name...


Global wb as workbook

sub dowhat(strWorkbook as string) <==changed
set wb = strworkbook <==added
wb.activate
...more code
set wb = nothing <==added
end sub


Private Sub Worksheet_Change(ByVal Target As Range)
dim strMacro2Run as string <==added
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

strMacro2Run = "workbook1.xls!dowhat(" & ActiveWorkbook.Name & ")" <==added
application.run strMacro2Run <==changed
End Sub


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jotor14" wrote:

I have this code in workbook#1

Global wb as workbook

sub dowhat()
wb.activate <<<< error here
...more code
end sub

============
and this code in a sheet of workbook#2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

application.run "workbook1.xls!dowhat"
End Sub


The problem is that dowaht works if run from a macro in workbook#1 but not
when called from workbook#2 when a cell changes. The error I get is "Object
Required" which I assume is because "wb" is empty. Can someone please let me
know what I am missing.

thanks

Bug: Call Sub in other book
 

I just did a little experimenting myself. It appears that while a variable
will not loose scope, an object is confined to the project. Workbook#1 and
Workbook#2 are different projects.
So it looks like you're going to either have to pass the name of the
workbook as I earlier suggested or activate the desired workbook prior to
running dowhat().
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jotor14" wrote:

Actually not really. I just want the code in dowhat to run. The global "wb"
is set earlier to point to workbook#1.For some reason this global is invalid
when called from workbook#2.

I've tried a similar test just using a global integer set by workbook#1 and
displayed by a sub in workbook #1 when called by the workbook#2 worksheet
function and that worked fine.

Maybe there's something special about objects ??


"Gary L Brown" wrote:

Assuming that what you want is for dowhat() to go back to workbook#2. To do
that, you need to let dowhat() know where it's being called from by using a
parameter containing the calling workbook's name...


Global wb as workbook

sub dowhat(strWorkbook as string) <==changed
set wb = strworkbook <==added
wb.activate
...more code
set wb = nothing <==added
end sub


Private Sub Worksheet_Change(ByVal Target As Range)
dim strMacro2Run as string <==added
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

strMacro2Run = "workbook1.xls!dowhat(" & ActiveWorkbook.Name & ")" <==added
application.run strMacro2Run <==changed
End Sub


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jotor14" wrote:

I have this code in workbook#1

Global wb as workbook

sub dowhat()
wb.activate <<<< error here
...more code
end sub

============
and this code in a sheet of workbook#2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("B1:B20"), Target) Is Nothing Then Exit Sub

application.run "workbook1.xls!dowhat"
End Sub


The problem is that dowaht works if run from a macro in workbook#1 but not
when called from workbook#2 when a cell changes. The error I get is "Object
Required" which I assume is because "wb" is empty. Can someone please let me
know what I am missing.

thanks