VBA to display a different sheet before displaying a message box

[Derek]

Hi Everyone

I am running an event macro from sheet A and I want to display sheet B on the screen before a message box displays. However the message box always displays first and Sheet B displays after I click OK.
I have tried Doevents, application.screenupdating = true, sheet.activate etc but none of this works for me.

Please can someone tell me the correct VBA to display sheet B before the message box opens.

I really appreciate your help

thanks

Derek

 

[alansidman]

Show us your current code so that we can modify it.

 

[Dryver14]

seems odd, it should just run in order.

if you have this line just before msgbox worksheets("SheetB").activate I don't see how msgbox could appear first. can you post the events macro?

 

[Derek]

Hi Dryver14

Yes I already tried active but no joy.

Here is the whole event macro code:

'LOCATE STAFF NUMBER IN SHEET ALL STAFF AND ENTER DATA
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
Sheets("ALL STAFF").Range("F10").Value = Target.Value
Sheets("ALL STAFF").Select
Sheets("ALL STAFF").Range("G10").Select
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim C As Long
Z = Target.Offset(0, 2).Value
X = WorksheetFunction.Match(Target.Value, Sheets("ALL STAFF").Range("H11:H20000"), 0) + 10
Y = WorksheetFunction.CountIf(Sheets("ALL STAFF").Range("N8:XFD8"), "<" & Z)
C = (Y + 14) * 1

ANSWER = MsgBox("Staff Number belongs to " & Sheets("ALL STAFF").Range("F" & X).Value & " " & Sheets("ALL STAFF").Range("G" & X).Value & vbCr & vbCr & "DO YOU WANT TO TERMINATE THIS PERSON?", vbYesNo)
If ANSWER = vbNo Then
Sheets("ALL STAFF").Select
GoTo Here
End If
If ANSWER = vbYes Then
Sheets("ALL STAFF").Range("F" & X & ":G" & X).Copy Destination:=Target.Offset(0, -2)
Application.CutCopyMode = False
Sheets("ALL STAFF").Range("J" & X).Copy Destination:=Target.Offset(0, 1)
Application.CutCopyMode = False
Sheets("ALL STAFF").Range("K" & X).Value = Target.Offset(0, 2).Value

Sheets("ALL STAFF").Select
Sheets("ALL STAFF").Cells(X, C).Select
ActiveWindow.ScrollColumn = C - 5
ActiveWindow.ScrollRow = X
Exit Sub
End If
Here:
Sheets("ALL STAFF").Select
MsgBox "Check this is the correct staff number, and start again"
Exit Sub
End Sub

I have also tried application.screenupdating = true and Doevents

thanks

Derek

 

[Dryver14]

move the Sheets("ALL STAFF").select line above the ANSWER line, or copy it, it will make the second instance redundant but wont break anything

 

[Derek]

Sorry, that doesn't seem to do it either

 

[Dryver14]

Just what sheet are you on? everything seems to be happening on sheet ALL STAFF which you seem to select every 10 lines.

is that the sheet you want to go to?

 

[Derek]

Hi Again

Found a solution! Need to run it in a new module, as follows:

Change event macro code to this

Here:
Sheets("ALL STAFF").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Check this is the correct staff number, and start again"
End Sub

but it actually seems to run more smoothly if I put that code in a module macro and just call it.

Thanks to everybody who replied.

Derek

 

[Dryver14]

Glad you are sorted