Add-in sheet

[CicoMico]

Hello! is there any way to make excel add-in (xla) that will show own sheet? i want to design excel add-in, which will contain sheet1 with some custom controls (multipage) and then if user loads this add-in, it'll show these controls. thanx

[Bob Phillips]

Add a custom toolbar




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.

[CicoMico]

hmm... thanx, but.... that's not what i'm trying to do... need to design multipage with labels, controls etc... and let the sheet to show...

[Bob Phillips]

It may not be what you thought of, but it is the way that professional developers build their addins.

What is wrong with it?

[CicoMico]

need to create multipage with some other interface...

[Bob Phillips]

But why?

As I said, that is not the way professionals do it, so why is your way so much better, or necessary?

[CicoMico]

i am working on complete application. i want to hide default tolbars, menus, tabs... and then show new designed interface. this interface will contain multipage control with tabs with custom options...

[Bob Phillips]

Then it seems to me that you should be

a) doing it in VB

b) using userforms.

[CicoMico]

hmm... i know, but i don't have vb :( i was thinking about using forms, but they are floating... i wanna have them on one place...

[Bob Phillips]

Excel is floating too?

[CicoMico]

nop. so should i hide excel and design my own application using userform?

[lucas]

What Bob is trying to communicate to you is that you can have your multipage controls in an addin without any sheets...just go to the visual basic editor and insert a userform and add your controls...when saved as an addin these controls, menu's ect will be available....

why would you wish to put the controls on a sheet is the question...? There is no reason is the answer.

[CicoMico]

ok, ok... :yes i got it... so basically what you all say is to design form on the fly? if so, any tuts on that? or would it be better to design it as suggested by lucas? thanx

[Bob Phillips]

IF by ... on the fly ... you mean adding controls frok within the userform code itself, then no I don't mean that. I mean design a form as normal, with the relevant controls and actions, and if you want to prtend that it isn't Excel, just hide the application.