Why does this happen?

[pcc]

I have hundreds of Excel files, but there are a handful that I use frequently. To this end, I have (In my personal.xls file) a userform, which, when loaded, displays those files as optionbutton captions (see image). I select the file required by clicking the optionbutton, then clicking the command button that says "Go". The code attached to this button is as follows:

Private Sub CommandButton2_Click()
For Each but In Me.Controls
If but.name Like "Option*" Then
If but.Value = True Then
Workbooks.Open "C:\users\peter\data\xl\" & but.Caption & ".xlsm"
End
End If
End If
Next but
End Sub

The selected file opens up OK, but then Excel reverts back to showing the personal.xls window, not the window for the file that has just been opened. I don't understand why this happens. I recently ugraded
from Excel 2002 to Excel 2021, and in Excel 2002 it opened the file and stayed displaying that window.

Any suggestions as to why this might be happening, and, more importantly, how to overcome this, so that the file that has just been opened becomes the active window?

 

[Skyybot]

Copy that.

 

[MARK858]

In any case, it all works fine with personal.xls, so I will leave well alone.

Just be aware if you use the dropdown in the record Macro dialog box and use the default Personal Workbook it will probably create a new Personal workbook in the xlsb file format and so you will have 2 Personal Workbooks.

 

[Micron]

Nice that you found a solution, but I think you misunderstand the purpose of the Stop statement.

However, when the code is resumed, the 'End' statement is executed,
and that should stop all VB code dead in its tracks

Stop is just like a line break. When it gets there, execution halts but the procedure is still "active". If you resume, it will behave as if the stop was not there, so yes, any following lines that need to be executed will be. Such an example of a line like that would be End If, End With, End Select...

 

[pcc]

Mark858: Thanks for the tip. I will watch out for this.
Micron: The 'End' statement after the Stop should have ceased all execution I believe.

 

[Micron]

My apologies, I didn't look at the pic because I thought you meant End If but it was a typo. Have never used that statement and didn't eve know it existed after all these years. Live and learn I guess. Thanks.