Focus on opened Filename before Dialog Box

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
Hi

I have the following macro which opens an external file.
I want the user to be able to edit it and save it and then in the parent Excel file again be given the choice to re-import those changes.

As it stands, the file opens but I get the dialog up first before focusing on the opened file. How can I force the focus or delay the dialog box?

Thanks for any help

Chris

Sub Open_Nat_Dem_331()

On Error Resume Next

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Define Source Workbook
DMRLSource = "\Nat Dem 331 01-05-14.xlsx"
SheetSource = "Template"

'Open Source Workbook and correct tab
Workbooks.Open Filename:=DMRLSource
Workbooks(DMRLSetup).Activate
Sheets(SheetSource).Select

Ans = MsgBox("Do you want to re-import the DMRL data for " & DMRLSource & "?", vbQuestion + vbYesNo)

Select Case Ans
  Case vbYes
  Call Data_Import_Nat_Dem_331
  Case vbNo
  Exit Sub
End Select

End Sub
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
One possibility; instead of using a MsgBox is to use a UserForm with a Label and two command buttons (Yes and No). The UserForm remains visible and allows the user to edit the worksheet. When done editing, they click Yes or No.

Code:
[COLOR=darkblue]Sub[/COLOR] Open_Nat_Dem_331()

[COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]

Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]

[COLOR=green]'Define Source Workbook[/COLOR]
DMRLSource = "\Nat Dem 331 01-05-14.xlsx"
SheetSource = "Template"

[COLOR=green]'Open Source Workbook and correct tab[/COLOR]
Workbooks.Open Filename:=DMRLSource
Workbooks(DMRLSetup).Activate
Sheets(SheetSource).Select
 
[COLOR=#ff0000]UserForm1.Show vbModeless[/COLOR]

[COLOR=green]'Ans = MsgBox("Do you want to re-import the DMRL data for " & DMRLSource & "?", vbQuestion + vbYesNo)[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=green]'Select Case Ans[/COLOR]
[COLOR=green]'  Case vbYes[/COLOR]
[COLOR=green]'  Call Data_Import_Nat_Dem_331[/COLOR]
[COLOR=green]'  Case vbNo[/COLOR]
[COLOR=green]'  Exit Sub[/COLOR]
[COLOR=green]'End Select[/COLOR]

End [COLOR=darkblue]Sub[/COLOR]

UserForm1 code module.
Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    [COLOR=green]'Yes[/COLOR]
    [COLOR=darkblue]Call[/COLOR] Data_Import_Nat_Dem_331
    Unload Me   [COLOR=green]'Exit userform[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton2_Click()
    [COLOR=green]'No[/COLOR]
    Unload Me   [COLOR=green]'Exit userform[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
Thanks, that sort of works, but the form is visible over the top of the editing.
The dialog was really for the parent worksheet, so when the editing in the opened file had finished, you could choose whether to update the parent file with this new info.
 

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
It's not.
My idea, but maybe flawed(!) was this:

Parent file runs Macro, dialog opens in it while other file is opened.
Nat Dem 331 file is opened and focused.
Editing happens and file is saved.
Parent file now has a dialog box present.

Clearly the dialog box takes precedent over all open Excel windows and focus is on this at all times.

I'm going to have to rethink.

Thanks for your help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,452
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top