Sub called from another workbook (Insert new line)

[csokosdama]

Hi everyone,

I'm trying to call in the parent Workbook (test) a sub in another Workbook (test2). This sub is supposed to insert a new line the test2. That is where the code is originally located. If I run the code seperately it works fine. If I try to call it from test, it doesn't. Do you have any solutions?

Thank you very much!

test.xlsm:

Sub testing()
Application.Run "test2.xlsm!Sheet1.newline()"
End Sub

test2.xlsm:
Sub newline()
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

In what way isn't your code working?

 

[csokosdama]

Thank you!

Well, I don't get any failure messages, so I suppose the sub is being called however, no row is inserted.
I also inserted some other codes newline() that fill a cell with text and it does that.

 

[Andrew Poulsom]

What other code did you add? If your VBA code is in a Worksheet module that worksheet will be used if references are unqualified.

 

[csokosdama]

The code looks the following:

test.xlsm:

Sub testing()
Windows("test2.xlsm").Activate
Application.Run "test2.xlsm!Sheet1.newline()"
End Sub

test2.xlsm:

Sub newline()
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(5, 7) = "Hello World!"
End Sub

I writes the "Hello World!" in the specified cell, however still no new row is added.
Btw. I'm using Excel 2010, if that makes any difference.

The VBA code in test.xlsm is in a separate module. In test2.xlsm the code is in the Worksheet.
Could that be the source of the problem?

 

[Andrew Poulsom]

As I said before, if your <ACRONYM title="visual basic for applications">VBA</ACRONYM> code is in a Worksheet module that worksheet will be used if references are unqualified.

 

[GTO]

I went by the code in post 1.

As I said before, if your <ACRONYM title="visual basic for applications">VBA</ACRONYM> code is in a Worksheet module that worksheet will be used if references are unqualified.

As Andrew says, if the called procedure is in a worksheet's module, non-qualified references refer to stuff belonging to that sheet; including Selection...

I am drawing a blank on why inclusion of the parenthesis in 'Application.Run "Book2.xlsm!Sheet1.newline()"' isn't drawing an error, but regardless, they should not be included in the string argument.

For simplicity, I used Book1 (calling) and Book2 (called). This seems to work:

Presuming both are open, in a Standard Module in Book1.xlsm:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> testing()<br>  Application.Run "Book2.xlsm!Sheet1.newline"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

In Sheet1's module in Book2.xlsm:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> newline()<br>  Me.Rows(5).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark

 

[csokosdama]

Great! Thank you very much for your support! It works perfectly now.

 

[GTO]

You are most welcome