VBA without Excel in the way?

[dnickelson]

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

 

[HalfAce]

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"...)

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

Private Sub Workbook_Deactivate()
With Application
        .CommandBars("Worksheet Menu Bar").Enabled = True
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .CommandBars("Worksheet Menu Bar").Enabled = True
    End With
End Sub

Hope it helps,
Dan

 

[parry]

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]

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]

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

Place in ThisWorkbook module

Private Sub Workbook_Open()
'Hide Excel application
Application.Visible = False
'Display userform
UserForm1.Show
End Sub

Userform 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]

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]

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.

Hope some of this helps.
Dan

 

[Peter Rattigan]

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

Cheers
Peter

 

[whiteghost]

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.