Workbook_Open

[Pondcroft13]

Hi All,

I am using part of the below formula to always start with the sheet "Dynamic UI" when the workbook is opened. It is not working.

Any ideas?

Private Sub Workbook_Open()

Dim frmSplash As GPUActionsLog
'Show the form modelessly
Set frmSplash = New GPUActionsLog
frmSplash.Show vbModeless
'Process the startup code
Application.Wait Now + TimeValue("00:00:5")
'Unload the splash screen
Unload frmSplash
Set frmSplash = Nothing
'open at a sheet (and range) you want regardless
'of whether or not the sheet has been renamed

'Set the sheet to open (use its codename here)
Const StartUpSheet = "Dynamic UI"

'Set the cell to activate
Const StartUpCell = "A1"

Dim N&
For N = 1 To Sheets.Count
If Sheets(N).CodeName = StartUpSheet Then
Sheets(N).Activate
Range(StartUpCell).Activate
Exit For
End If
Next

End Sub

Kind regards,

Chris

 

[ColinKJ]

Hi Chris,

Sheets("Dynamic UI").Activate

So long as the sheets name isn't changed.

ColinKJ

 

[RoryA]

FYI, the sheet's codename could never be "Dynamic UI" as codenames cannot contain spaces.

 

[SamTYler]

Replace

'Set the sheet to open (use its codename here)
Const StartUpSheet = "Dynamic UI"
 
'Set the cell to activate
Const StartUpCell = "A1"
 
Dim N&
For N = 1 To Sheets.Count
If Sheets(N).CodeName = StartUpSheet Then
Sheets(N).Activate
Range(StartUpCell).Activate
Exit For
End If
Next

with either

Dynamic_UI.Range("A1").Activate

Or

Sheets("Dynamic UI").Range("A1").Activate

Depending if "Dynamic UI" is the Tab name of the Sheet or if you rewrote Excel to allow you to use a space in the Codename.

You can tell the difference in the VBA editor by looking at the Sheet names in the Project Explorer window. The CodeNames are not enclosed in parentheses and the Tab names are.

In a new workbook with no changes

Sheet1(Sheet1)

If you rename the Sheet1 Tab to "First Sheet"

Sheet1(First Sheet)