Tool bars and startup
 

I have designed an excel sheet to work as an application..

Is there a way to hide all the menubars and toolbars when i open this sheet ?
Is there a way to create my own ones ?
Is there a way to show a certain startup picture or page instead of the one
of Excel as it happens with MS Access ?

Please help and note that i'm still new to excell

Tool bars and startup
 

"Pietro" wrote in message
...
I have designed an excel sheet to work as an application..

Is there a way to hide all the menubars and toolbars when i open this
sheet ?



Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False


Is there a way to create my own ones ?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



Is there a way to show a certain startup picture or page instead of the
one
of Excel as it happens with MS Access ?


Create a useform with your picture and add this code

ThisWorkbook code module

Public Sub KillTheForm()
Unload SplashScreen
End Sub

Standard Code Module

Private Sub Workbook_Open()
SplashScreen.Show
End Sub


Userform code module

Private Sub UserForm_Activate()
Const SS_DURATION As Long = 5 'seconds
Application.OnTime Now + TimeSerial(0, 0, SS_DURATION), "KillTheForm"
End Sub

Tool bars and startup
 

Hi "Bob Phillips",
You are perfect,but kindly note that i'm still new to excell,so i could not
find the place where i should put these codes!!!!!!
Could you please rewrite them again putting in consideration that i need to
copy and paste them directly in the sheet ?

Regards

"Bob Phillips" wrote:


"Pietro" wrote in message
...
I have designed an excel sheet to work as an application..

Is there a way to hide all the menubars and toolbars when i open this
sheet ?



Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False


Is there a way to create my own ones ?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



Is there a way to show a certain startup picture or page instead of the
one
of Excel as it happens with MS Access ?


Create a useform with your picture and add this code

ThisWorkbook code module

Public Sub KillTheForm()
Unload SplashScreen
End Sub

Standard Code Module

Private Sub Workbook_Open()
SplashScreen.Show
End Sub


Userform code module

Private Sub UserForm_Activate()
Const SS_DURATION As Long = 5 'seconds
Application.OnTime Now + TimeSerial(0, 0, SS_DURATION), "KillTheForm"
End Sub

Tool bars and startup
 

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article ,
Pietro wrote:

You are perfect,but kindly note that i'm still new to excell,so i could not
find the place where i should put these codes!!!!!!
Could you please rewrite them again putting in consideration that i need to
copy and paste them directly in the sheet ?

Tool bars and startup
 

The first bit of code depends upon when you want to hide the toolbars. The
second and third parts included instructions on where to place it. The
Second part would build that example menu when the workbook opens and remove
it on the close. You would need to change all of my buttons, captions and
macro etc. with yours. The third bit was also to be invoked on opening a
working, and displays that log/splash screen for 5 seconds before closing it
down.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Pietro" wrote in message
...
Hi "Bob Phillips",
You are perfect,but kindly note that i'm still new to excell,so i could
not
find the place where i should put these codes!!!!!!
Could you please rewrite them again putting in consideration that i need
to
copy and paste them directly in the sheet ?

Regards

"Bob Phillips" wrote:


"Pietro" wrote in message
...
I have designed an excel sheet to work as an application..

Is there a way to hide all the menubars and toolbars when i open this
sheet ?



Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False


Is there a way to create my own ones ?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



Is there a way to show a certain startup picture or page instead of the
one
of Excel as it happens with MS Access ?


Create a useform with your picture and add this code

ThisWorkbook code module

Public Sub KillTheForm()
Unload SplashScreen
End Sub

Standard Code Module

Private Sub Workbook_Open()
SplashScreen.Show
End Sub


Userform code module

Private Sub UserForm_Activate()
Const SS_DURATION As Long = 5 'seconds
Application.OnTime Now + TimeSerial(0, 0, SS_DURATION), "KillTheForm"
End Sub