VBA Dialog Box Pop Up ?

[Pinaceous]

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:

     Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

But this doesn't always work.

Can you help provide a suggestion??

Thank you,
Pinaceous

 

[gallen]

Can you show the code you have to show the dialog? I can't think of any reason why it wouldn't appear.

 

[Pinaceous]

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:

 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

 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