VBA Dialog Box Pop Up ?

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
1,124
Office Version
  1. 365
Platform
  1. Windows
Hi All,

If I'm using a code to produce a dialog box to save the file, I've noticed that the dialog box does not pop in front of the excel document but sometimes behind it.

How do I express in code for the dialog box to pop up in front of the working excel document?



Please let me know, if you have a code.

I've used:

Code:
     Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True


But this doesn't always work.

Can you help provide a suggestion??

Thank you,
Pinaceous
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Can you show the code you have to show the dialog? I can't think of any reason why it wouldn't appear.
 
Upvote 0
Hello gallen,

In working with Windows 10 and Excel 2016 I'm using a macro heavy workbook.

I control the ribbon and execute the code from there.

If I open up Alt+F11 and run the code directly the dialogue box gets produced (in the front!), but if I run the sub from the ribbon, it hides behind the workbook and the curser circles continuously.

The ribbon code simply unlocks the workbook, then unlocks the sheets, then opens up the tabs, copies then pasts information into the previously hidden tab then it produces exports that sheet to word.

The code where the dialogue pops up is as follows:

Code:
 Sub Excel_to_Word()


ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True
    
'Note: This code requires a reference to the Word Object Library to be set.
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlRng As Excel.Range, r As Long, c As Long, FlNm As String
With ActiveWorkbook
  FlNm = ActiveSheet.Name & " " & Format(Now, "YYYYMMDD_hhmm") & ".docx"
  With .ActiveSheet
    With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      r = .Row
      c = .Column
    End With
    Set xlRng = .Range(.Cells(1, 1), .Cells(r, c))
  End With
End With
With wdApp
  .visible = False
  Set wdDoc = .Documents.Add
  xlRng.Copy
  With wdDoc
        With .PageSetup
      .PaperSize = wdPaperLetter
      .Orientation = wdOrientPortrait
      .LeftMargin = wdApp.InchesToPoints(0)
      .RightMargin = wdApp.InchesToPoints(0.25)
      .TopMargin = wdApp.InchesToPoints(0.25)
      .BottomMargin = wdApp.InchesToPoints(0.45)
        End With
    .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
    '.SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      With wdApp.Dialogs(wdDialogFileSaveAs)
      .Name = FlNm
      .AddToMRU = False
      .Show
    End With
    .Close False
  End With
  .Quit
End With
Application.CutCopyMode = False
Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing


   Exit Sub
 
End Sub


I've tried to tweak the code by adding
Code:
 ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True
to force the dialogue to pop front and center but this stumps me.

Do you have any suggestions?

Thanks,
Paul
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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
Back
Top