I was wondering if you could help me figure out why worksheet.activate does nothing in the case below. I turn off
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False,
-> create a new file and add sheets to it, -> turn them back on, -> and when i try to activate the new file, it does nothing, and the main file remains active.
Does anyone have a solution for something like this?
Enum getWhat
getRow = 0
getCol = 1
End Enum
Sub main()
SetProgramAlerts 0
Set currentWorkbook= ActiveWorkbook
Set newWorbook = Workbooks.add
With newWorbook
Set WS_Project = .Worksheets("Sheet1")
WS_Project.name = wsProject
'....
End with
SetProgramAlerts 1
newWorbook.Activate 'this actually does nothing, even though application.screenupdating = true
end sub
Public Sub setProgramAlerts(turnThem As turnThem)
'TURN EVERITHING OFF AT THE BEGINING OF A MACRO
'REMEMBER TO TURN BACK ON
DoEvents
Select Case turnThem
Case 0 'All off
With Application
If .ScreenUpdating Or .EnableEvents Or .DisplayAlerts Then
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End If
If Workbooks.Count > 0 Then
If .Calculation <> xlCalculationManual Then .Calculation = xlCalculationManual
End If
'.statusBar = True
End With
Case 1 'All on in opposite order
With Application
'.statusBar = False
If Workbooks.Count > 0 Then
.Calculation = xlCalculationAutomatic
End If
If .DisplayAlerts = False Or .EnableEvents = False Or .ScreenUpdating = False Then
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End If
End With
End Select
DoEvents
End Sub
I had the same problem. After setting Application.ScreenUpdating = True before sheet.activate everything worked fine.