VBS-VBA Userform

[LisaGreen]

Hi,

I have a userform I build in VBS in a new excel workbook.

After I've built it, I want to be able to show the form somehow without the application.

The purpose it to present a userform with choices, commandbuttons without showing where it came from.

I've created the workbook and built the form in it but!!!

I can't get the userform to show "on top" as it were.

So....
I need help with opening excel quietly, possibly with a /e command line switch, from VBS and getting the form to show.

If anyone can help I would very much appreciate it.

TIA
Lisa

[HansV]

If you really want a stand-alone form, I'd use one of the Visual Studio development environments to create an application and compile it as a .exe.

In Excel, you can hide the application window(s) by executing

Code: Select all

    Application.Visible = False

but in my experience this may have unwanted side effects, such as an empty Excel window that cannot be closed appearing after you make the application visible again...

[LisaGreen]

Hello Hans!!

It's a user requirement that No extra software is installed besides word excel access powerpoint. VBS comes with windows of course and we know about VBA. They think that controlling what software is being run on the network adds to security. There is no internet access for example. The one piece of software that they do run is called Celsius. This is for building cabling. It's possible to run a VBS script from Celsius but the script has to be vetted as well.

Lisa

[Guessed]

Have you considered HTA?
See https://stackoverflow.com/questions/160 ... o-vbscript for an example.

[LisaGreen]

Hello Hans!

Great link! It looks as tough that will do the trick!

Thank you!

Lisa

[PJ_in_FL]

Lisa,

I just saw your request and wanted to let you know how I created an "application" in Excel:

I created a workbook that performed functions via a userform. In my case I needed to apply specific formatting for input into SAP fields that resisted formatting.

In the ThisWorkbook object, the Workbook_Open macro is:

Code: Select all

Private Sub Workbook_Open()
Application.Visible = False
Load UserForm1
UserForm1.Show
End Sub

Userform1 opens when the workbook is run, and no other Excel window opens. Excel is closed when the userform is closed by this code in the userform code module, usually by clicking the X in the upper right corner of the userform:

Code: Select all

Private Sub UserForm_Terminate()
Unload UserForm1
Application.Quit
End Sub

If you just click on the Excel file to open the "application", all open workbooks disappear and Save? dialogs will open after the Excel "application" is closed since all open workbooks are being closed at the same time.

The real trick is to open the workbook as an Excel application object in VBS. This creates a separate instance of Excel for the "application" so the other Excel workbooks are left alone. Here's the script I "wrote" (plagiarized from somewhere years ago) that did just that"

Code: Select all

' Run the SAP Formatter in minimal mode
'  	- only show the Userform
'	- close excel when exit the userform
' Create an Excel instance
Dim myExcelWorker
	
	Set myExcelWorker = CreateObject("Excel.Application") 

	' Disable Excel UI elements
	myExcelWorker.DisplayAlerts = False
	myExcelWorker.AskToUpdateLinks = False
	myExcelWorker.AlertBeforeOverwriting = False

	myExcelWorker.Workbooks.Open("D:\Tools\SAP QN Long Text Formatter.xls")

	myExcelWorker.Quit
	set myExcelWorker = Nothing

If the HTA method is working, then this will just be a backup in case you need a different interface.

[LisaGreen]

Hi Pj!

Thank you for your post and apologies for not getting back.

I went with HTA in the end and am very satisfied with it and so is the client/user.

If you want the code I can post it to you. It's quite short for me!! LOL!!

Going via VBS is something that's very useful so thank you for that.
There is a site that uses VBS to "multithread" VBA and has a free tool to do so.
https://analystcave.com/excel-multithre ... -vbscript/" onclick="window.open(this.href);return false;

Thank you again.
Lisa