vba - public sub to hide workbook behind userform(x)

[devofish]

I have sort of painted myself into a corner with creating a pretty in-depth vba project and my "hack" is to hide the workbook behind the userform. I know, I know, I should have used VB Studio since I'm trying to emulate a "standalone" application. Regardless, what I have I can insert into each userform module. I'm wondering how I could make this a public sub and call it from the userform. Is this even possible or would there be caveats to this approach? I've tried using Dim uForm as UserForm and substituting Me with uForm but that didn't work.

Private Sub HideApp()

    Dim wbName              As String    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    wbName = ws.Range("D4")
    
    On Error GoTo Err
    
        Application.ScreenUpdating = False


    With Windows(wbName)
        .WindowState = xlNormal
        .Top = Me.Top
        .Left = Me.Left
        .Height = Me.Height
        .Width = Me.Width
    End With


        Application.ScreenUpdating = True
Err:
        Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi,
to do what I think you want, you pass an instance of the userform to your common code as an argument.

Place this update to your code in a STANDARD module

Sub HideApp(ByVal Form As Object)
    Dim wbName As String
    
    On Error GoTo myError
    
    wbName = ThisWorkbook.Sheets(1).Range("D4").Text
    
    Application.ScreenUpdating = False


    With Windows(wbName)
        .WindowState = xlNormal
        .Top = Form.Top
        .Left = Form.Left
        .Height = Form.Height
        .Width = Form.Width
    End With


myError:
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

to call from your userforms

 HideApp Me

Dave

 

[Fazza]

Maybe I'm on the wrong track here: would it suit to have the file as an add-in?

 

[devofish]

Hello Dave. That works very well. Thank you! I want to steer clear of add-ins Fazza, since I want to limit the user's interface with Excel as much as possible. In addition, this is a "template" file that produces an interface file for a database. I'm completely "internet" taught so like I said, after a year of studying and trial and error with this project, I've come to find I'm using the wrong tool for the job - hence my jenky "hack".

 

[dmt32]

Hi,
Glad solution did what you wanted.

I want to limit the user's interface with Excel as much as possible.

I would just add a note of caution doing what you say above – If you change or limit users Excel interface or access to it, ensure that you fully reinstate it when your application is closed otherwise, you may just find users will not want to use it.

Personally, I would not change anything to do with users Excel desktop settings.


Dave

 

[devofish]

I agree. From what I have read and my own personal experience with trial and error testing (to my horror a couple times), I'm not doing anything to change the user's actual Excel settings, other than just "hiding" the application window behind whatever userform is active.

It's a little jenky since the window state has to be set to xlnormal when a new form is loaded, causing the window to (flicker) jump around (even with screenupdating = false), but it seems to allow the user to work within multiple instances of the program without the instance holding the vba project affecting or begin affected by the other running instances.

 

[Fazza]

thanks for the further explanation, devofish. best wishes for the project. regards, Fazza

 

[devofish]

Not sure if I can get more feedback on this from you Dave, but...this is a problem that has me completely stumped.

During runtime on my machine, I am incapable of resizing the window using the application borders and I cannot interface with application window (the main forms are not modal but the application holding the vba project cannot be interfaced nonetheless because the UserForm_Layout code calls the HideApp function.

However, when we run the project on another user's machine, the they are capable of resizing the window and interacting with the application window - something I'm trying to avoid. The application window remains behind the userform and the userform behaves just like a nonmodal form should behave. But I can't figure out why the userform "mimics" a modal form on only my machine, but not on others.

Could the project references being different from machine to machine affect the way the UserForm_Layout sub executes? My references are:

Visual Basic for Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Scripting Runtime

 

[devofish]

Nevermind, Dave. Back to the drawing board on this one. The behavior is the same on my machine now, so I will have to find an alternative. Thanks, again.