Design Mode Change Event

[dageci]

Hello guys/girls,

does anyone know any tip (or maybe even the event) on how to detect inside the VBA when the user switches the
Design Mode On/Off?

 

[RoryA]

There is no event for that, not least because code won't run in design mode (running code manually will turn design mode off).

 

[dageci]

RoryA thanks for reply,

but there could be an event just like we have the BeforeClose event.

 

[RoryA]

Perhaps, but there isn't. You couldn't really have an event for design mode switching on, because it wouldn't be able to run.

Why would you need such an event.

 

[dageci]

Finally, I managed to sit down in front of my computer.

RoryA what I'm trying to do is to skip the need to have an UserForm in my Excel project.

Best would be that I show you in the video. This video is a part of series in an Academy tutorials that I'm creating about the Usercontrol that I'm building.

It's called Virtual Forms, it is a kind of a new Userform control for Excel for 32-bit and 64-bit.

I'm trying to give to the Excel users what Access users are having for years. But in a simple and not so complicated way.

It's in an Early Access strage, so there are still bugs, the documentation is still in writing mode........

Now I need the Userform control to be able to have the
UserForm_Initialize event
and the
UserForm_Terminate events

Because I declare the control as a public withevents so I need to create the control and then at the end destroy it

Here is a video where you can see what I'm doing:
https://youtu.be/6Qaf-o-4L7Q

 

[Jaafar Tribak]

I am not sure I understand properly but, can't you use a Class Module instead of using a userform .. You can then initialize and terminate the usercontrol inside the ClassX_Initialize and ClassX_Terminate event procedures of the Class module while still being able to use the usercontrol own events.

 

[dageci]

Jaafar thanks for your idea.

Trying to optimize the first steps in using the Virtual Forms control.
I wanted to skip the step where I need to tell the people to insert a Userform or Class.

I need to test this approach. What happens with the class when the user switches Design mode On/Off if it gets destroyed. If yes then the object is also destroyed.

If there would be some solution to use this in the Workbook scope. In this case the host (userform or class) would be skipped.

But thanks,

Davor

 

[Jaafar Tribak]

How about using the ThisWorkbook Class module which doesn't get destroyed and in which you could handle the usercontrol events 'withevents' just as with a class module or userform module ?

 

[dageci]

I have tried this, but when I switch the Design On/Off the object is gone.

Here is the code that I have used, and I will put a link here to download the zip file with the sample data & definition file.
Link to download the sample data & VF definition file

But to be able to run this you will need to download and install Virtual Forms Framework from virtual-forms.com

Public WithEvents VF2 As VirtualForm2.VirtualForm


Private Sub Workbook_Open()
    Dim strVFFile As String
    Dim strConn As String
    
    ' instance new
    Set VF2 = New VirtualForm2.VirtualForm
    
    strVFFile = ThisWorkbook.Path & "\VFFile1.vf"
    VF2.VFFile = strVFFile
    
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
    strConn = strConn & ThisWorkbook.Path & "\Northwind.xlsm"
    strConn = strConn & ";Extended Properties='Excel 12.0 Macro;HDR=YES;';Persist Security Info=False"
    VF2.ConnectionString = strConn
    
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' to clean up before we leave
    VF2.CloseAllVirtualForms
    VF2.DisconnectDatabase
    
    Set VF2 = Nothing
    
End Sub




Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    'open virtual form
    VF2.ShowVirtualForm "VF3"
    
End Sub