CODEWITHSUNDEEP
vbaexcel
ManInTheMiddle
ManInTheMiddle

Reputation: 128

Start VBA macros by open Worksheet

How can I start a macro by opening an Excelsheet. I have already put this code:

Public Sub Workbook_Open()

Application.ScreenUpdating = False

With Tabelle1.ListBox1

    .AddItem "TEST1"
    .AddItem "TEST2"
    .AddItem "TEST3"
End With

With Tabelle1.ListBox2

    .AddItem "TEST4"
    .AddItem "TEST5"
End With    

With Tabelle1.ListBox1
    .Width = 140.25
    .Height = 255.25

End With
With Tabelle1.ListBox2
    .Width = 78
    .Height = 69.75    
End With

Call EnterInDesignMode
Call ExitInDesignMode
Application.ScreenUpdating = True
End Sub

Into Private Sub Workbook_Open(). But it doesn't go to the second Code. What am I doing wrong here?

Both Codes are above the Public Sub Workbook_Open()

'first Code
    Sub EnterInDesignMode()
        With Application.CommandBars.FindControl(ID:=1605)
            .Execute
        End With
    End Sub

'Second Code
Sub ExitInDesignMode()
    Dim sTemp As String
    With Application.CommandBars("Exit Design Mode")
        sTemp = .Controls(1).Caption
    End With
End Sub

Both codes are working, but it doesn't stops the call EnterInDesignMode(), so it doesn't call the ExitInDesignMode().

Upvotes: 2

Views: 256

Answers (1)

Luuklag
Luuklag

Reputation: 3914

With CommandBars("Exit Design Mode").Controls(1)
    If .State = msoButtonDown Then .Execute
 End With

Edit the code you have to this, as is posted in the link I commented with.

Source: https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/XN27a-Gxz6g

Upvotes: 1

Related Questions