Create a likewise app with excel vba (workbook hidden)

[filipe colomb]

Hello,

I have some userforms that was build in a app like way, where the user can see data, close and open windows and input data, everything using the userforms. These data are stored in some worksheets and then processed with vba.

I was wondering how can I "transform" it in a app like system, where the user doesn't even know that there is an excel behind it.

I don't know how to fit the Application.visible command, since it work for every opened instances and the user often open other excel files during the use.

I was looking for something that when I open the file I see only the initial userform and when I close it the whole "app" is closed, without messing with other excel files?

Thank you.

 

[Ombir]

Hi,

Paste below code in Userform Code:

Private Sub UserForm_Initialize()
With Application
        .WindowState = xlMaximized
         Zoom = Int(.Width / Me.Width * 80)
         Width = .Width
         Height = .Height
    End With
End Sub
Private Sub UserForm_Terminate()
If Application.Windows.Count = 1 Then
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Shell ("taskkill /IM EXCEL.exe")
Else
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    ThisWorkbook.Close
End If
End Sub

This will enable you to open the userform in Full screen mode. You can change the zoom parameters according to the screen.

When you close the userform with cross sign, it will close the whole workbook. Pretty much like a app.

But users can still see the worksheets by disabling macros. You can prevent it by setting the Visible property of worksheets in VBA project sidebar to xlsheetveryhidden.

Hope this helps.

 

[filipe colomb]

Thank you for the reply Ombir. I will try doing it. Just a doubt, once I make that, the user will be able to use other excel files normally? Or this code you mess up the other instances?

 

[Ombir]

Thank you for the reply Ombir. I will try doing it. Just a doubt, once I make that, the user will be able to use other excel files normally? Or this code you mess up the other instances?

Hi,

You won't be able to use other workbooks as long as Userform is displayed on screen. There are two workarounds.


1. You can Open another instance of excel by using command Excel.exe /x in run window and then open other workbooks in that instance.

2. You need to call window API functions to add minimize button to your Userform. Check below link for this.

http://www.mrexcel.com/forum/excel-...ton-excel-form-visual-basic-applications.html