sub terminates but desired workbook not displayed

Question

I have a VBA program that asks for the user to enter a desired range, which is in another opened workbook. If there is an error, i.e. the other workbook is manually activated, but no range is selected, or there is an error, I want the program to display the original macro workbook sheet with an error message. The code below works in Debug mode, but when the VBA program is run, it displays the error correctly but does not display the original macro worksheet. It remains on the sheet that was manually activated by the user. What am I missing?

In the code below, "HMArea" is a Range variable returned by the routine getting user input. "Macro_Fname" is a string variable for the file name of the original VBA program.

HM_file = FileName(HMArea)   
If HM_file = "Macro_Fname" Then
    Windows("Macro_Fname").Activate
    Sheets("[name of the sheet in Macro_Fname]").Select
    Range("D4").Select
    MsgBox "ERROR: No data selected"
    Exit Sub
End If

score 0 · Answer 1 · answered Oct 24 '13 at 01:08

0

Try changing

Windows("Macro_Fname").Activate

to

Workbooks("Macro_Fname").Activate

However, the exact nature of your question is vague. Assuming you are in Workbook A, do you want to select a range in Workbook B (which is open at the same time)?

answered Oct 24 '13 at 01:08

WGS

13,969
4
48
51

score 0 · Answer 2 · edited May 23 '17 at 10:25

To refer to macro workbook use ThisWorkbook

When another workbook is opened always assign it to variable so that you can have control over it.

Set wbk = Workbooks.Open("D:\test.xlsx")

When working with multiple workbook always prefix the workbook object. If its ignored it will take active workbook.

`Sheets("[name of the sheet in Macro_Fname]").Select`

Avoid using Select/Acitvate. See here

Range("D4").Select

Once the above issues are fixed your code will run as expected.

sub terminates but desired workbook not displayed

2 Answers2