Can't select and activate cells during BeforePrint event

[zk69]

Hi all

I am using Excel 2010 SP2 MSO and have a workbook with 3 sheets and the code below is in "ThisWorkbook".

I tried to select and activate cell on sheet1, where no data was entered (cell is blank.)
Whereever the cursor is positioned, I try to help user with selecting and activating the empty cell.

It works correctly during the BeforeSave event, but doesn't if I try to print the worksheet (Sheet1)!
Could you help me how I should handle these cell manipulations on BeforePrint event?

Thanks in advance.

Regards
Zoltan


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim CursorFreezer As Boolean
Dim WorkRange As Range
Dim Cell As Range
Application.EnableEvents = False
Sheet1.Select
Sheet1.Activate
If Sheet1.Range("G3") = "" And CursorFreezer = False Then
Cancel = True
MsgBox "Please enter value into G3 cell!"
If CursorFreezer = False Then
Sheet1.Range("G3").Select
Sheet1.Range("G3").Activate
CursorFreezer = True
End If
End If
If Sheet1.Range("G4") = "" And CursorFreezer = False Then
Cancel = True
MsgBox ""Please enter value into G4 cell!!"
If CursorFreezer = False Then
Sheet1.Range("G4").Select
Sheet1.Range("G4").Activate
CursorFreezer = True
End If
End If
Application.EnableEvents = True
CursorFreezer = False
End Sub


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim CursorFreezer2 As Boolean
Dim WorkRange2 As Range
Dim Cell2 As Range
Application.EnableEvents = False
Sheet1.Activate
If Sheet1.Range("G3") = "" And CursorFreezer2 = False Then
Cancel = True
MsgBox ""Please enter value into G3 cell!"
If CursorFreezer2 = False Then
Sheet1.Range("G3").Select
Sheet1.Range("G3").Activate
CursorFreezer2 = True
End If
End If
If Sheet1.Range("G4") = "" And CursorFreezer2 = False Then
Cancel = True
MsgBox ""Please enter value into G4 cell!"
If CursorFreezer2 = False Then
Sheet1.Range("G4").Select
Sheet1.Range("G4").Activate
CursorFreezer2 = True
End If
End If
Application.EnableEvents = True
CursorFreezer2 = False
End Sub

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

[mana]

I can select G3 or G4.

[p45cal]

I see what you mean!
If sheet1 cell G3 is blank and if sheet3 is the active sheet as you try to print, it seems to go to sheet1 and select cell G3 and as you type it looks as though you're typing into Sheet1 (the tab Sheet1 is selected and you can see all the data on Sheet1), but when you've entered data in that cell and then move to other sheets you discover that what you've typed is actually on Sheet3!
Crazy!
I don't know the solution (someone's come across it before: http://www.sqlexcel.net/showthread.php?172316-Event-Triggered-in-Wrong-Sheet and oddly similar here with a completely different date: https://www.office-forums.com/threads/event-triggered-in-wrong-sheet.2036630/ )

So you're not going mad…
I'll do some more Googling.

[p45cal]

Can't find much about it I'm afraid. I wonder if it happens with others?
Meanwhile , an 'orrible workaround:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim CursorFreezer2 As Boolean
Dim WorkRange2 As Range
Dim Cell2 As Range
Sheet1.Activate
Application.EnableEvents = False


If Sheet1.Range("G3") = "" And CursorFreezer2 = False Then
Cancel = True
If CursorFreezer2 = False Then
Application.Goto Sheet1.Range("G3")
Sheet1.Range("G3").Value = InputBox("G3 is empty! Enter something here to be put in cell G3", "Printing Aborted")
CursorFreezer2 = True
End If
End If
If Sheet1.Range("G4") = "" And CursorFreezer2 = False Then
Cancel = True
If CursorFreezer2 = False Then
Application.Goto Sheet1.Range("G4")
Sheet1.Range("G4").Value = InputBox("G4 is empty! Enter something here to be put in cell G4", "Printing Aborted")
CursorFreezer2 = True
End If
End If

Application.EnableEvents = True
CursorFreezer2 = False
End Sub

[mana]

Maybe you can use "ontime method"


Thisworkbook module:



Option Explicit


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim s As String

Sheet1.Activate

If Sheet1.Range("G3") = "" Then
Cancel = True
MsgBox "Please enter value into G3 cell!"
s = "'gotoEmptyCell ""Sheet1!G3""'"

ElseIf Sheet1.Range("G4") = "" Then
Cancel = True
MsgBox "Please enter value into G4 cell!"
s = "'gotoEmptyCell ""Sheet1!G4""'"
End If

If Cancel Then Application.OnTime Now + TimeSerial(0, 0, 1), s


End Sub


Standard module:



Option Explicit


Sub gotoEmptyCell(myAdr As String)
Application.Goto Range(myAdr)
End Sub

[p45cal]

Yes, that seems to work well.
A few tweaks for zk69:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim s As String, cll As Range
For Each cll In Sheet1.Range("G3:G4").Cells 'this can be any range of cells, contiguous or not.
If cll = "" Then
Cancel = True
Application.Goto cll 'keep this to highlight (what looks like) the cell & sheet for the user behind the msgbox.
s = "'gotoEmptyCell """ & cll.Address(external:=True) & """'"
Application.OnTime Now, s 'call this with no delay so no waiting at all after user clicks OK on msgbox (Now() is the *earliest* time it will be executed).
MsgBox "Please enter value into cell " & cll.Address(0, 0)
Exit For
End If
Next cll
End Sub

(This needs mana's second snippet (in msg#5) in a standard code-module, of course.)

[mana]

>'call this with no delay so no waiting at all after user clicks OK on msgbox


That's right. Thank you.

[p45cal]

zk69 seems to have forgotten he asked a question here.

[zk69]

Dear p45cal and mana!

Thank you so much for your assist and your perfect suggestion. It works.
Sorry for replying late. I haven't got too much time dealing with excel development nowdays.

I greatly appreciate your effort.

Regards
zk69


Yes, that seems to work well.
A few tweaks for zk69:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim s As String, cll As Range
For Each cll In Sheet1.Range("G3:G4").Cells 'this can be any range of cells, contiguous or not.
If cll = "" Then
Cancel = True
Application.Goto cll 'keep this to highlight (what looks like) the cell & sheet for the user behind the msgbox.
s = "'gotoEmptyCell """ & cll.Address(external:=True) & """'"
Application.OnTime Now, s 'call this with no delay so no waiting at all after user clicks OK on msgbox (Now() is the *earliest* time it will be executed).
MsgBox "Please enter value into cell " & cll.Address(0, 0)
Exit For
End If
Next cll
End Sub

(This needs mana's second snippet (in msg#5) in a standard code-module, of course.)