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?

 

[Micron]

If no code runs in the open wb that could cause this, I suspect it is caused by the loop in the wb code. AFAIK, normally, option buttons are single select unless you're done something that allows more than one to be selected. Your pic doesn't answer that question. If not, then after the wb is opened the code that is running is likely making the first wb active. Try adding Exit Sub after opening. Maybe like

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

Nice that you used code tags, but your code lacks indentation so not as helpful as they could be.

 

[Skyybot]

Are you closing the Form? I didn't see it in your example.

Unload Me

 

[pcc]

It's bizarre. I put a 'Stop' statement into the code (see image), and at that point, the newly opened workbook is displayed, as expected. However, when the code is resumed, the 'End' statement is executed,
and that should stop all VB code dead in its tracks. But when the code completes, it is still personal.xls that is displayed as the active window, not the newly opened workbook. I have tried converting my personal.xls to personal.xlsm, (out of interest), but that made no difference to the outcome.

 

[pcc]

By the way, the optionbuttons are all in the same group, so only one can be selected..

 

[pcc]

Eureka! I have found a solution. The form that was loaded initially with all of the optionbuttons was loaded modally (not for any particular reason - that's just the default).
I changed the code from

Sub showcomfilform()
ComFilForm.Show
End Sub

to

Sub showcomfilform()
ComFilForm.Show False
End Sub

And it now words as intended. Strange that it worked before in Excel 2002 without the modified code. Thanks for your inputs guys.

 

[MARK858]

I have tried converting my personal.xls to personal.xlsm

Just for reference really you normally save the Personal Workbook as an xlsb file in modern windows versions

Create and save all your macros in a single workbook - Microsoft Support

Create and save all your macros in a single workbook by creating them in Personal.xlsb, a hidden workbook that opens every time you open Excel. Macros created in Personal.xlsb are available every time you open a workbook in Excel.

support.microsoft.com

 

[pcc]

Thanks Mark. Problem is that I have loads of macros, assigned to various customized buttons, that refer to 'personal.xls'. If I change the filename to 'personal.xlsb' then all of those macros will need to be reassigned. I can do without having to do that (Unless there's an automated way of doing that via VB)

 

[Skyybot]

In VBA you can Select All and use Find and Replace. I have to do that often with older code.

 

[pcc]

Thanks, but not as simple as that. I am referring to items on the Quick Access Toolbar, where the buttons on it have been assigned to macros in personal.xls. (In earlier versions of Excel it was something like Tools>Customise>(Right-click the button)>Assign macro). Not sure where that is now! In any case, it all works fine with personal.xls, so I will leave well alone.