zk69
12-14-2016, 10:22 PM
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
-----------------------------------------------------------------------------------------------------------
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
-----------------------------------------------------------------------------------------------------------