How to bring userform to front or setfocus?

mexmanz

Board Regular
Joined
Sep 19, 2005
Messages
72
Hi All,


I am using the code below to save and email a new copy of a workbook, this all works fine (with thanks to Ron de Bruin!)

It allows the Outlook message box:
'a program is trying to automatically send e-mail on your behalf. Do you want to allow this?....Yes/No/Help'
to appear - I am happy with this!

The problem I have is if the user chooses 'No' by mistake on the Outlook msgbox.
I have a userform that runs if the user selects 'No' but if Outlook is already open during the process the userform runs in the background behind Outlook. Is there a way to bring the userform frmSendEmailError to display on top of Outlook after the user selects 'No'?

----------------------------------------------------------------------

'Code to save file as unique name with date stamp (iCode = Item Code)
iCode = Worksheets("DataChangeRequestForm").Range("C4").Value
fName = ("\\FILE1\Shared Files by User\User\Forms\Change Request\Autosave\Data Change Request - " & iCode)
NewFile = fName & " " & Format(Date, "dd-mm-yyyy")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewFile
Application.DisplayAlerts = True

'Code to email form
'This sends the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error GoTo CancelClick_Error
'On Error Resume Next

Application.ScreenUpdating = False

With OutMail
.To = "procurement.qc@royalberkshire.nhs.uk"
.CC = ""
.BCC = "andrew.rivas@royalberkshire.nhs.uk"
.Subject = "Change Request - " & iCode
.Body = "Pharmacy Data Change Request Form Attached - " & iCode
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Exit Sub

'This is the userform to allow user to retry if No selected from Outlook message, how can I
'bring this in front of Outlook/setfocus for this userform?

CancelClick_Error:
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus
End Sub

-------------------------------------------------------------------------------


Thank you
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
You can swap between Outlook and Excel having focus using the following:

Code:
AppActivate ("Microsoft Excel")
or
Code:
AppActivate ("Microsoft Outlook")
(I'm not sure if you use "Microsoft Outlook", I think so but as I've got Lotus Notes (hack spit) at the moment I can't check)
 

mexmanz

Board Regular
Joined
Sep 19, 2005
Messages
72
Hi Johnny,

Thanks for this, I have tried both in the code:

CancelClick_Error:
AppActivate "Microsoft Excel"
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus

and

CancelClick_Error:
AppActivate ("Microsoft Excel")
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus

I get runtime error '5'
Invalid procedure all or argument.

each time. How should I include the line?

thanks
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,579
Messages
5,512,168
Members
408,882
Latest member
Clarab9

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top