Needs some VBA advice from the gurus

Posted by Mark on January 16, 2002 6:57 AM

I'm trying to assemble an application that will allow me to maintain several jobs that I'm working on. This is my first VBA project, so I'm constantly refering back to this site for ideas & help.

The VBA project is coming along pretty well. I have 10 forms that I've coded correctly (as far as I can tell). Ideally, when I start the app, the form "main" will be displayed.

"Main" basically does two things. First, I have a combobox that will allow me to select which job I need to work on, and also displays the job's name in a text box. Second, It has commandbuttons that open seven other forms. So far, so good. All of the controls work as needed.

The other seven sub forms have two text boxes at the top that I need to display the job's number & name as selected on "main". This has me baffled. Any Ideas??

Also, what needs to be done to compile the VBA project into an application?? When the project gets compiled, what actually happens to the file?? The reason I ask is because my VBA project is already 114meg & I'm about 1/2 complete. I zipped the file & the size dropped to 4.6meg. Can I expect the file size to decrease??

Thanks for being kind enough to lend your expertice to the "small guys". Without the help & advice from this sight, it would be next to impossible for me to even this far.

Mark

Posted by DK on January 16, 2002 8:32 AM

Hello Mark,

To answer your questions in turn. In the VB editor double click the ThisWorkbook icon from the Project Explorer. This will open a code module for your workbook. Change the comboboxes to Workbook on the left and Open on the right (this should happen anyway).

Private Sub Workbook_Open()
frmMain.Show 'Whatever your form name is.
End Sub

That will make your userform open automatically (unless the user disables macros).

As for your second question. To refer to the value of a textbox on frmMain in frmSub1 just use this in the code module for your sub form. The keyword Me refers to the object where the code is placed and is an efficient way of coding.

Private Sub UserForm_Activate()
Me.TextBox1 = frmMain.TextBox1
End Sub

As for your last question. Workbooks created using anything other than Office Developer Edition aren't compiled. The VBA code is interpreted as it is run rather than compiled into an executable stand alone application. Whoever wants to use your application needs to have a copy of Excel installed on their machine.

If anything isn't clear please let me know.

HTH,
D.

Posted by Mark on January 16, 2002 9:36 AM

DK, thanks for your reply.

The main.show sub works great, but I'm having trouble with the text box sub. Here's what I have...

Private Sub releaselog_activate()
Me.jobname = main.txtboxjobname
Me.jobnumber = main.MAINjobnumberlist
End Sub

I placed this in frm releaselog's code, but there still nothing showing when the form is ran. Any idea what I'm doing wrong??

Posted by DK on January 16, 2002 10:35 AM

In the releaselog form try this code instead:-

Private Sub UserForm_Activate()
Me.jobname = main.txtboxjobname
Me.jobnumber = main.MAINjobnumberlist
End Sub

If this doesn't work let me know,
D

Posted by Mark on January 16, 2002 10:51 AM

You're tha man!! Thanks **