PDA

View Full Version : Solved: Wrote a Macro in Word, now What???



n2mopars
08-26-2004, 07:32 AM
I have Word 97. I went into Tools...Macro...Visual Basic Editor and wrote the code and created an "online" order form for our sales reps. I don't know what to do with it now...When I want to view the order form, I have to open Word...Tools...Macro...Visual Basic Editor then Run it. Obviously, this is not acceptable to give to our reps. I need them to be able to simply open the form, enter their info and print it. Unfortunately, the person that helped me write the code is not familiar with Word's Visual Basic and can not help me. I did not know what a Macro or Code was before Monday so I am dead in the water. Help please!!! Thanks! http://www.vbaexpress.com/forum/images/icons/icon7.gif

TonyJollans
08-26-2004, 08:45 AM
Hi n2mopars,

Welcome to VBAX!

The easiest way to run a macro as a one-off is via Tools > Macro > Macros (shortcut, Alt+F8), select the macro from the list and press Run. For regular use a button, either on a toolbar or in the document, is better, or maybe just having it auto run when the document is opened.

I can't be sure based on what you've posted so far which would be best in your circumstances. Can you post a bit more, and perhaps a sample document?

Thanks

sgrant
08-26-2004, 10:54 AM
I just looked in Word help and there are references to an AutoExec and AutoRun macro, tha will cause the macro or form to open automatically when the document was open.
I couldn't get help to give me details, but I know from experience that if you tinker around in help long enought you can stumble over some gems.

Maybe you will have more luck looking at this than I did.

n2mopars
08-26-2004, 01:52 PM
Tony and sgrant,

Thank you for the quick responses. I have attached a copy of the document for Tony. Hopefully you can help me with this...Thanks again!

Julie

Zack Barresse
08-26-2004, 01:58 PM
... I have attached a copy of the document for Tony. Hopefully you can help me with this...

Where is it at? I don't see it. Did you zip it? Did you browse for it, then press the Upload button?

n2mopars
08-26-2004, 03:07 PM
Yes, I zipped, browsed and uploaded. When I tried it again it said there was an upload error. I can go to the zip file on my computer and open it and look at it ok. Any ideas on why it won't attach?

n2mopars
08-26-2004, 03:11 PM
Hey Guys!

For whatever reason, probably a Word thing, it appears to have attached this time...Let me know! Thanks~!!

Julie

JOrzech
08-26-2004, 03:20 PM
I think the easiest to way to make your code run is to write the code in an AutoNew macro. That way, when the user clicks on a new template to create a new document, your macro will run.

So add a module called AutoNew and paste this code into it:


Sub AutoNew()
Load UserForm1
UserForm1.Show
End Sub

I usuallly set all textboxes, etc. to contain nothing ... but let's see if this works for you. Let me know if you need more info.

P.S. I know they say to use Document_New but it doesn't work as well for me....

TonyJollans
08-26-2004, 04:23 PM
Hi n2mopars,

Several things I note about your document.

Firstly it is a document (.doc) and not a template (.dot) - Joanne's suggestion of an AutoNew macro will work in a template - in a document you will want AutoOpen instead.

Either way I guess you need to know where to put the code. Open up Module1 (double click on it in the explorer at the left of the VBE screen) and paste the code in the window which opens on the right. If you do it in your document as is, you'll need to change the line Sub AutoNew() to read Sub AutoOpen()

Your document has several unused objects - Userform2, Userform3, Userform4, Userform5, Module2, Class1 - these can all be deleted. Module1 is also unused but you're going to use it for the new code so don't delete that one.

I don't see what will happen after the form has been displayed - there isn't any obvious mechanism for doing anything with the data entered in the form - I haven't studied it closely so I may have missed something - how do you intend to use it? What is it that you want to print? And do you want to save the result as well?

Finally, AFAIK there is no benefit in explicitly Loading the Userform as Joanne has done and I think this is sufficient code:

Sub AutoNew()
UserForm1.Show
End Sub
or
Sub AutoOpen()
UserForm1.Show
End Sub

If you need more help then please post back.

JOrzech
08-26-2004, 05:29 PM
Thanks for correcting me Tony... I must have skimmed over the "doc" part.

I've corrected the document.

JOrzech
08-26-2004, 07:51 PM
Tony -

I think I'm still pretty clumsy about Word VBA. Why would he/she need Module1? I would add a command button to UserForm1 and run the code from there. Please educate me.

And Tony is correct - you will need to populate the Word document with the responses from UserForm1. I use DOCVARIABLES to do that, but I understand most people don't like them. I use Insert, Field, DocVariable - give it a name in the Word document, then I populate it by code like this:


ActiveDocument.Variables.Add Name:=("BlahBlah"), Value:=UserForm1.TextBox17.Text


Then you would have to update your fields:


Sub UnlinkFields()
Selection.WholeStory
Selection.Fields.Update
'this is optional - it turns the fields to text
Selection.Fields.Unlink
Selection.HomeKey
End Sub


And unload/hide your UserForm

TonyJollans
08-27-2004, 01:39 AM
Hi Joanne,

I suggested he put the AutoOpen in Module1 which was why he needed to keep it - of course it can, and probably should, go in ThisDocument. Sorry to confuse.

fumei
08-29-2004, 11:43 PM
I think Joanne is refering to myself, with my usual loud mouth opinion. DOCVARIABLES has their purpose. I use them, but also I sometime make an Active X control and resize them to a single character, and make them essentially invisible. You can store a LOT of data in there, it is easily retrievable, and unlike DOCVARs have their own firing events.

n2mopars
08-30-2004, 02:46 PM
Tony,

I was able to get it working placing the code in ThisDocument. I still have a question, though. It opens the user form but behind it is the page with my letterhead on it. If you view the sample that is on Joanne's post that she corrected, it opens the same way. Do you (or anyone) have a suggestion to make the flolaborderformtemplate.doc page not appear? Thanks a million everyone, we're almost there! :0)

Julie

JOrzech
08-30-2004, 04:44 PM
n2mopars:

First of all, I'd make it a template.

Secondly, the document will normally show up in the background and then once populated by the VB code, it will assemble on the same screen. I have a suggestion to make it not appear "behind the scenes." Try adding this to your code:

Documents.Add Template:=Options.DefaultFilePath(wdUserTemplatesPath) & "\flolaborderformtemplate.DOT", newtemplate:=False

mdmackillop
08-30-2004, 05:14 PM
Hi Julie,
I read that you're new to this, :bink: and looking at the form code, I think it's a bit daunting, even for an "expert". I've taken the liberty to mess around with it, to make it a bit more user friendly, if figures etc. need changing.
The checkboxes haxe been renamed to Option 2 - Option11, and the Textboxes on the right renamed from Cash1 - Cash15, Indexes correspond to make things more transparent. Cost are declared as Constants within the code, so if you need to change a figure, only one change is required, similarly Shipping Costs, for which I have entered some sample figures. The Calculate button should calculate the totals, rather than the coded totals from the "if" functions. I've deleted the colouring etc., and anything else which seemed unnecessary, but it can always re reinstated if required. Please test thoroughly before use.
Good luck!
MD

n2mopars
08-31-2004, 09:26 AM
MD,

I opened your file and am getting a "Run-time error '400':", "Form already displayed; can't show modally"...I like the calculate button..The colors were there to show where you were if you tab through the form, instead of clicking on which boxes you want with the mouse (easy enough for me to put back).

I still can't figure out how to make just the macro open, without the document in the background. I assume I need to make the macro an executable? If so, does anyone know how to do this from Word? I tried compile but that doesn't seem to do anything (at least not anything noticeable or that I can find). I did make the document a template and tried Joanne's code. I don't know if I am putting the code in the wrong place or what I did wrong but I could not get it to work.

Thank you again to everyone for your replies and suggestions. I really appreciate all the help you have offered :0)

Julie

JOrzech
08-31-2004, 02:40 PM
Can you upload the file so we can look at it? I'm sure it can function as you request.

mdmackillop
08-31-2004, 02:56 PM
Hi Julie,
I based my code on Joanne's posting and had assumed the "background" issues were resolved. I'd rather leave them to those assisting already:dunno .
Re your error message, what version of Word are you running, and can you pin down the line giving you the error? If you need help to do this, please ask and I'll try to assist.
MD

n2mopars
08-31-2004, 02:56 PM
Joanne,

Thanks for everything! The .dot template is attached.

Julie

n2mopars
08-31-2004, 03:04 PM
MD,

I am using Word 97 (yeah, nothing like being current). Attached is a print-screen of the code with the error. Thanks!

Julie

mdmackillop
08-31-2004, 03:20 PM
Julie,
You need to click in the border to the left of the code "Sub Autoopen" to insert a breakpoint, then try to open the form. Pressing F8 will step you through each line until an error occurs. As I'm working in 2000, this may be the problem but not insurmountable.
MD

JOrzech
08-31-2004, 07:19 PM
I got some new furniture tonight and had to pick up my son but I'm working on it - it will take a little bit. :whip

I also think you may need to register your dlls with regserv32.exe. Add them as a reference to your VBA project - it may be comdlg32.dll or something like that... but will explain that once we get you up and running. Maybe someone else here will explain how to register dlls for you... that may be why you get an error message on userform1.show. :)

TonyJollans
09-01-2004, 03:31 AM
What is the error you get?

I just took your latest posted example, double clicked on it in Winzip to create a document based on your template in Word 97 under Windows 95 and it worked fine (apart from the form being way too big for the 800*600 screen).

n2mopars
09-01-2004, 07:25 AM
Tony, MD and Joanne,

I opened the .zip file this morning and now it is opening fine. I don't know if there was something running in the background that made the computer think the form already was displayed? Who knows! I am still trying to figure out this letterhead/background issue...Thanks again!

Julie

JOrzech
09-01-2004, 04:34 PM
I worked on the file for quite a while last night... maybe I'm missing something though so please bear with me.

TonyJollins said the form worked fine for him. But I see no command button so I am at a loss to explain how the form is populated once the user completes the UserForm.

Julie - can you explain - or is this what your original question was asking? I should have it done tonight if that was the problem but I'm afraid I'm looking a fool - because when I complete the information on the UserForm, I don't know what to do next....:dunno

Thanks.

TonyJollans
09-02-2004, 12:31 AM
Sorry, Joanne. All I meant was it opened automatically so I couldn't see anything wrong with the .Show. I didn't try and do anything with it after it opened.

JOrzech
09-02-2004, 06:21 AM
Thanks Tony. I misunderstood.

n2mopars
09-02-2004, 07:14 AM
Joanne,

That's correct...I don't know what to do either. The sales rep can tab through the form over and over and...Anyway, I just need them to be able to print it to give to the customer. Which arises another question. There is no print option. Can I add one?

What I was asking about a few posts ago is when the file opens, the form opens over another document. I would like to be able just to open the macro form, nothing else. The way it is looks messy. Thanks for all your hard work! I REALLY appreciate it!! :0)

Julie

Anne Troy
09-02-2004, 07:23 AM
Julie: Do you realize you've got some of the best Word coders on the web helping you out? (Didn't know if you knew that---soooo cool!!)

I just wanted to let everyone know that the only thing I've ever heard of where we *hide the application* was this little jobbie from Colo, which hides the Excel interface and even allows the userform to show on the taskbar. Could someone modify it for Word? (I know--I make it sound easy and I'm sure it's not.)

http://puremis.net/excel/code/063.shtml

JOrzech
09-02-2004, 07:42 AM
Will do Julie - but it's gonna take a bit of work :) I'll finish it as soon as I can.

mdmackillop
09-02-2004, 09:08 AM
H Julie,
I assume you're looking to print out a document that looks like the Userform, is this correct? If not could you type up a document and post it, so that the printout can be arranged accordingly.
MD

n2mopars
09-02-2004, 09:57 AM
Dreamboat:

I know I do and I am very grateful for it!! Thanks for the suggestion..

Joanne:

Thanks again for all your effort! I will look forward to seeing it...

MD:

Yes, something like a print screen would be great (unless there is an easier way to do it).

Thanks again all!!

Julie

mdmackillop
09-02-2004, 11:37 AM
Hi Julie,
I'm looking at somthing like this attachment. Obviously formatting etc. still to be done. Does it function 97? Click to new CommandButton to run.
MD

TonyJollans
09-02-2004, 05:33 PM
Hi Julie,

I took a copy of MD's version and merged a version of Colo's code (as posted by Dreamboat) to create the attached. Is this what you want to see?

mdmackillop
09-03-2004, 12:06 AM
Hi Tony,
Thanks for that useful addition. One thing though, I can't get hold of the butterfly to delete/change it. Julie had a letterhead on her original posting, (which I had left off on my sample document) which presumably could act as the "image".
MD

n2mopars
09-03-2004, 07:16 AM
Tony & MD,

I opened both .zip files and tried to run them. If I just select all the options that have a $$ amount then hit calculate, I get a "Compile Error: Variable not defined" in the "Private Sub CommandAddTotal_Click()" on line "tmp= 0". So, there is no way to get rid of the Word document that is open in the background of the form? When I hit the command button, it takes me to the "background" page. Is this what it is supposed to do? Thanks!!

Julie

TonyJollans
09-03-2004, 07:37 AM
My fault! :006:

When I copied Colo's code, it came with an Option Explicit statement at the start and I left it there (the first line in the Form's code module), and didn't think to test the existing code with it.

Normally you should have this statement but you can delete it temporarily for this early stage of testing and one of us will make sure all the variables are declared later.

MD,

I just copied the butterfly as is from Colo's workbook. I'm sure a more relevant image can be found. If you go into Design Mode you can access it.

mdmackillop
09-03-2004, 09:34 AM
Design mode!!! :blush

TonyJollans
09-03-2004, 10:52 AM
Click on the set-square icon in the Control Toolbox

mdmackillop
09-04-2004, 04:50 AM
Hi Julie,
An updated version to try. (I'll test it on 97 later) The output need some formatting, but I think all the fields are functioning correctly. The Calculate function is automatic on the Userform, trigerred when a change is made.

Tony,
I was having some problem (still) with the image, so cancelled the Activate macro. Things still seemed to work as regards opening without the background, so I deleted this and dependent subs, and also the image. I've left in all the declarations and things, as I'm not really sure what's going on!:bink:
If you could remove any extraneous items from the start, I think a version of this would be suitable for a KB item or Add-in.

MD

JOrzech
09-04-2004, 05:41 AM
MD - Thanks - looks like you've got it pretty much done. I appreciate that for Julie's sake because I'm going out of town for Illustrator training for a week and wasn't sure when I'd be able to get to it.

As always, great job!

TonyJollans
09-04-2004, 07:12 AM
I have removed the definitions related to the code you deleted.

For the record, all that's left is:

a) in the AutoOpen Sub in ThisDocument, a line at the start - Application.Visible = False, and
b) The QueryClose Event handler in UserForm1 with the line - Application.Visible = True

The Form does not have an icon, cannot be minimized and does not appear on the taskbar but it can still be selected via Alt+Tab (showing as a Word icon).

mdmackillop
09-06-2004, 01:28 PM
Hi Julie,
Are things working?
MD

n2mopars
09-09-2004, 08:41 AM
Everyone,

Thank you so much for all of your invaluable time! It looks good...I should be able to figure out the minor adjustments I want to make. Thanks again!!

Julie
http://www.vbaexpress.com/forum/images/icons/icon10.gif