View Full Version : [SOLVED:] Userform to interface Excel

08-17-2005, 09:59 AM
Maybe the title is not the best but here is what I want to do.

I have a workbook that has 4 sheets in it. The first sheet is used to enter data for the installation of concrete pavers. It captures all the necessary data related to the job such as name, project, areas to be paved. It caculates the materials needed and the cost of the materials. The remaining sheets are used to do the job or submit the proposal to the client.

What I am doing is creating a userform that will be the interface to the users using the Excel workbook so they will have a form to place the data in the first sheet of the workbook. Than I will place buttons on the userform to allow the workbook to be saved or printed off. We do not want the users to know that the underlying workbook exist.

I am new to userforms and VBA but not programming. Everything that I have seen so far or read has the userform as part of the workbook. What I would like to do is have the ability to start the userform.

How do I do this? Do I use VBA?


08-17-2005, 10:48 AM
Hi SailFL,

In the ThisWorkBook code window place

Private Sub Workbook_Open()
Application.Visible = False 'hides excel from being seen
UserForm1.Show 'shows the userform
End Sub

in the userform

Private Sub CommandButton1_Click()
Application.Visible = True 'makes excel visible there will need to be some code to save the worksheets etc
Unload Me 'getrid of the userform
End Sub


08-17-2005, 12:24 PM
Hi Nils,
You can also do this with a standalone VB programme such as VB6. This typically uses a userform interface to Excel, Word or Access. I've played about with this a little, but to a beginner level only. I got myself a copy of the "Deluxe Learning Edition" which comes with Step by Step book and a Programmers Guide.
VB6 allows you to open the Userform as an .exe file, so that the background Excel data files etc are not apparent.
I don't know enough to recommend this approach, but it may be worth looking into.

08-17-2005, 03:55 PM

How do you start the form? Do you place a button on the toolbar to access the userform or how do you get userform up and running?

08-17-2005, 03:56 PM

I thought I could do that but I don't want to go out and buy another sw package. I suppose I could drag out an old version I have.

08-17-2005, 04:13 PM
You would double click or open the workbook. The workbook would open hiding Excel and the workbook and then show the form, that would be all you see is the form. When you exit the form it would allow Excel to show and the form would disappear.
Placing buttons on toolbars could also be a way.

What programming langauge are you comfortable with?

08-17-2005, 05:14 PM

Then I would use a blank excel sheet to start my userform and write the data to the closed excel workbook that I want to populate?

I have done C, C++, Fortran, Ada, Some Visual Basic and Java. I use to develop code for Simulators for DOD. So I know how to code I just need to come up to speed on VBA. My C++ is a bit rusty because I didn't code that much in C++. But I understand Object programming. I have an old copy of Visual Basic maybe I could follow up on Malcolm suggestion later. Right now I want to stick with the problems I have with learning the ins and outs of VBA.

Any other suggestions. I did leave a thread on how to change the color on MultiPages. Got any suggestions on that?

Thanks for your help.

08-17-2005, 05:41 PM

A blank excel sheet is not necessary. You would need to add the code I posted earlier to the workbook that the work needs to be in and have a userform named userform1 (this is the default). It is not as hard as it sounds, it is more concept than anything.

I translate Fortran to VB or rewrite it faster better shorter in VB. I think of VB as Fortran with a whole lot more toys :). The best part is you don't have to code everything from scratch. C++ I take too long, I like C, I use a lot of old DOS programming launguages, Autolisp, been around for a while so the list gets long and dull :).

As far as I know you can change the color on the font (foreground) or the background (I don't think this is what you want). You could add a picture or image and paint the image, maybe, what are you trying to do may be a better question.

Look at the TabStrip control under style, pick fmtabstylebuttons and change the backcolor (colour) to another color. If this is what you want you will need to program the other stuff on the tabstrip to hide and unhide.

:) Fun

08-17-2005, 11:09 PM

The color I want to change maybe considered to be the foreground. I can change the color of the tab text and the background of the tabs by using the properties of the MultiPage named by default MultiPage1. But if you look at the properties of Page1 or any of the pages of the MultiPage1 there are do way to change the color for the backcolor or forecolor of the page. I don't want grey. From what I read it looks like I might be able to change it by the way of code but not in a property.