VBA without Excel in the way?

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
I've written up some workbooks that perform tasks rather unrelated to excel, or rather, not dependant on the excel application at the moment, they only need excel to open up because I wrote them in vba behind it and\or reference some cells. The end result is that the user doesn't need to interface with excel.

What I'm wondering is what do some of you do to minimize the focus on the excel application when you run into this situation?

Not sure exactly how to make it work nicely but I'd considered making a userform open on entry, hiding the application, and closing the application once the user form is closed. But it had also occurred to me to maybe hide all the toolbars, make just a shell of the window, etc.

I don't want to modify the application the next time they use it, and I'd considered one resolution is not to use excel at all, just go figure out vba, but excel is such a handy interface, everyone has it available, don't have to mess with compiling, etc.

Anyway, mostly looking for ideas, but technical tips would be great.

(as an example, i have one application where i'm just checking to see if they have a dsn set up and setting one up if they don't. or a simpler one might be a hex converter, just need the userform interface, etc.)

Thanks in advance for any idea.

-Dan
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Dan,
A couple of things come to mind.
One would be if you're looking to do this without Excel at all, then you might want to look into using Visual Basic (VB) instead of VBA (Visual Basic for Applications)

For your other option ("making a userform open on entry, hiding the application, and closing the application once the user form is closed. But it had also occurred to me to maybe hide all the toolbars, make just a shell of the window, etc.") You can pretty much do that by making your userform, in the properties use about 450 for the Height and about 600 for the width (depending on your screen size, resolution, etc) and then using something like this in the ThisWorkbook module. (All three of these, just to be safe and to not "modify the application the next time they use it"...)
Code:
Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Application
    .WindowState = xlMaximized
    .CommandBars("Worksheet Menu Bar").Enabled = False
    .CommandBars("Standard").Visible = False
    .CommandBars("Formatting").Visible = False
    .CommandBars("Chart").Visible = False
    .CommandBars("Circular Reference").Visible = False
    .CommandBars("Clipboard").Visible = False
    .CommandBars("Control Toolbox").Visible = False
    .CommandBars("Drawing").Visible = False
    .CommandBars("External Data").Visible = False
    .CommandBars("Forms").Visible = False
    .CommandBars("Picture").Visible = False
    .CommandBars("PivotTable").Visible = False
    .CommandBars("Reviewing").Visible = False
    .CommandBars("Visual Basic").Visible = False
    .CommandBars("Web").Visible = False
    .CommandBars("WordArt").Visible = False
    .DisplayFormulaBar = False
End With
Application.ScreenUpdating = True
UserForm1.Show
End Sub
Code:
Private Sub Workbook_Deactivate()
With Application
        .CommandBars("Worksheet Menu Bar").Enabled = True
    End With
End Sub
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .CommandBars("Worksheet Menu Bar").Enabled = True
    End With
End Sub
Hope it helps,
Dan
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, another alternative may be to use VB Scripting, which uses VB type methods but is just a plain text file (with a VBS extension). The trouble is it has no intellisense, help files or GUI but your code from Excel may work as is. If your wanting a GUI then you really need a stand alone product like VB, but this costs money.
 

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
Thanks guys, I really wasn't looking for a stand alone product, though the vbs idea could come in handy for some of the simpler projects (don't suppose there's a userform-like functionality in there though, is there?), otherwise I'm pretty content using the excel interface, just that when I don't need any interaction between the user and excel I'd like to put it 'behind the scenes' to reduce any confusion as to the functionality I'm going for.

This really only occurred to me after I started using the digital signatures topic i'd found on this site to bypass the 'Enable Macros' prompt'. Since I can have the userform pop up immediately upon opening, just thought it would be a natural extension, if I only needed that interaction, to only display the userform at all.

Thanks for the responses guys. This is easily one of the best sites I've seen on any subject for responsiveness to direct questions.

-Dan
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355

ADVERTISEMENT

Hi Dan, I think this is what you require...

Place in ThisWorkbook module
Code:
Private Sub Workbook_Open()
'Hide Excel application
Application.Visible = False
'Display userform
UserForm1.Show
End Sub

Userform code
Code:
Private Sub cmdOK_Click()
'Place value of textbox into cell A1
Sheet1.Range("A1") = Me.TextBox1

'Close workbook and show Excel again if required
Call CleanUp

End Sub

Private Sub UserForm_Terminate()
Call CleanUp
End Sub


Sub CleanUp()

'Unload userform
Unload UserForm1

'Close and save workbook
ThisWorkbook.Close True

'See if theres > 0 workbooks open
If Application.Workbooks.Count > 0 Then
'There was more than just this book open so make Excel visible again
    Application.Visible = True
Else
'Quit Excel. If you dont do this Excel is still running but just not visible
    Application.Quit
End If
End Sub
 

Peter Rattigan

Board Regular
Joined
Oct 18, 2004
Messages
77
Hiding toolbars

I have used the information in this thread to hide toolbars on a project I an working on. The code works fine when I open the spreadsheet as all toolbars turnoff etc. However, when I open the workbook using a hyperlink from access which is how it would ordinarily work in my project the code doesn't seem to operate even though I have said yes to enabling macros. Has anyone any thoughts on why this could be happening and how I can rectify it please.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

Hello,
Don't know if this is an option (or if it will even work for that matter), but if you can, try setting your macro security in Excel to Low and then try opening the workbook from Access again. I would imagine the macro security stops any sub from running, (what it's designed to do...), and when you're clicking yes to enable macros, there's nothing calling the sub because the workbook's already open by that point.

Another option might be something like having the workbook save on a specified sheet (so it always opens on that sheet), and provide a button on your startup sheet for the user to access any other sheet(s) necessary, and then just include the code to hide all the commandbars in that button's code.

As far as I know you can't include the commandbar hiding code in a hyperlink, but maybe as a third option you can replace the hyperlink with a standard macro in access and include the code in that.(?)

Just a couple of ideas to try. I'm not well versed on Access at the moment, but yesterday my sister came up with a project that may require me to change that. :unsure:

Hope some of this helps.
Dan
 

Peter Rattigan

Board Regular
Joined
Oct 18, 2004
Messages
77
Toolbars

Hi Dan
Thanks for the response. I ended up removing the hyperlink and replacing it with a OpenApp macro and the problem was fixed. It also had the added benefit of removing the open macro warning message ( Can't explain that one). Just in case your interested you can also remove the upodate links warning by going to options,edit and untick the "ask to update automatic links" checkbox. By then using code to select full screen I can move from Access to Excel fairly seamlessly.

Once again Thanks

and stay warm up there :biggrin:

Cheers
Peter
 

whiteghost

Well-known Member
Joined
Nov 22, 2002
Messages
500
Code:
Private Sub UserForm_Initialize()
Me.Top = Application.Top
    Me.Left = Application.Left
    Me.Height = Application.Height
    Me.Width = Application.Width

End Sub





Ihad been given this code to make the userform fiit the screen

[/quote]
Since I can have the userform pop up immediately upon opening, just thought it would be a natural extension, if I only needed that interaction, to only display the userform at all.
 

Forum statistics

Threads
1,148,228
Messages
5,745,482
Members
423,953
Latest member
MrC54

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top