VBA - How do I set an object variable?

[Gwynne]

Hello All,

I am a nu-B and don’t know much about programming. I have been tasked with converting WP 5.1 macros to Word XP and boy, do I need help!

I have created a template, and am using InputBoxes to gather information to be plugged into the document. At one point the user will have to make a decision as to whether a document was recorded in a Volume/Page or a Transaction. I have created a UserForm with 2 radio buttons allowing the user to choose one or the other. I need the UserForm to appear at the right time, but keep getting an error 91 “Object variable or With block variable not set.” My question is: How do I set an object variable?

Here is my code so far:

Private Sub Document_New()

'declare vars
Dim strCaseNum, strDefName, strCrimCrt As String
Dim strVolume, strPage, strTransNum As String
Dim frmVolumePageOrTransaction As UserForm
Dim optVolumePage As OptionButton

'set name vars to input
strCaseNum = InputBox("Enter the Case Number.&quot
strDefName = InputBox("Enter the Defendant's Name.&quot
strCrimCrt = InputBox("Enter the Numbered Court.&quot
frmVolumePageOrTransaction.Show
If optVolumePage = True Then
strVolume = InputBox("Enter The Volume Number.&quot
strPage = InputBox("Enter The Page Number.&quot
Else
strTransNum = InputBox("Enter the Transaction Number&quot
End IfstrVolume = InputBox("Enter The Volume Number.&quot
strPage = InputBox("Enter The Page Number.&quot
strTransNum = InputBox("Enter the Transaction Number&quot

'set field results to name vars
ActiveDocument.FormFields("Text1&quot.Result = strCaseNum
ActiveDocument.FormFields("Text2&quot.Result = strDefName
ActiveDocument.FormFields("Text3&quot.Result = strCrimCrt
ActiveDocument.FormFields("Text7&quot.Result = strVolume
ActiveDocument.FormFields("Text8&quot.Result = strPage
ActiveDocument.FormFields("Text9&quot.Result = strTransNum

End Sub

Any help would be appreciated before I become bald!!

Gwynne

 

[SkipVought]

Hi,

An object variable is assigned with the Set statement. In your case, you need...

Set frmVolumePageOrTransaction = UserForm1

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Hi
I haven't tested any of this but an initial thought is that this section is a bit jumbled up!

strTransNum = InputBox("Enter the Transaction Number")
End IfstrVolume = InputBox("Enter The Volume Number.")
strPage = InputBox("Enter The Page Number.")
strTransNum = InputBox("Enter the Transaction Number")

This should generate an error itself. Try breaking it down.

The other thing I'd try is using your code without declaring the option button or user form as variables as they already exist in your project as objects.

Just thoughts!
;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Zathras]

At the risk of stepping into a mine field, there are a number of problems here.

I am using Word97, so there may be things that will work for me that still won't work for you, but here goes.

I was able to get your form to show by shortening the form name to 25 characters:

   frmVolumePageOrTransactio.Show

But the real problem is with the User Interface design. You are forcing the user to answer your questions in the order you have specified. This worked fine 40 years ago in the days of the TTY interface, but is not suitable for the Windows environment. Another consequence of using the InputBox is that you have provided no way for the user to cancel the sequence once it has started. The Cancel button only cancels the InputBox while the macro goes merrily on with a blank input entry.

Please, please, rethink your User Interface. It would be much better to put all of your input items on the same form as your radio buttons and allow the user to enter data at will. Then by having the usual OK and Cancel buttons on the form, your macro can either proceed or not according to the user's wishes by testing the results of which button was clicked.

 

[Zathras]

Hey, Loomah! Good thoughts. (Simultaneous posts).

Gwynne: Here is one more thing that is an easy trap to fall into, especially if you are familiar with other programming languages such as Pascal:

Your declarations

Dim strCaseNum, strDefName, strCrimCrt As String
Dim strVolume, strPage, strTransNum As String

are equivalent to the following:

   Dim strCaseNum As Variant
   Dim strDefName As Variant
   Dim strCrimCrt As String
   Dim strVolume As Variant
   Dim strPage As Variant
   Dim strTransNum As String

In this case, that is the least of your problems (See comments regarding the UI above.), because the code will work just fine using Variants for strings. But since Loomah has pointed out the fact that you do not need to (nor should you) declare the form, and the button(s) in that way, I thought it might be useful to point out the trap that VBA has hidden in the Dim statement when declaring more than one variable per statement.

 

[Gwynne]

Skip, Loomah, and Zathras, thank you very much for trying to help me.

Loomah, I have cleaned up my code as you suggested; however, I don’t understand what you mean when you say “…without declaring the option button or user form as variables as they already exist in your project as objects.” How else would I declare them?

Zathras, I work with 135 VERY computer challenged users (read brain dead). They want to see the template so they will know what goes in the "little gray box" (shaded form fields to you and I). The first couple of templates I created used UserForms. Alas....they are used to WP 5.1 macros and the InputBoxes are as close as I can get to what they are used to. (Don't scare the people with something new).

I have never had a programming lesson in my life, and I’m winging it.

I have used your suggestion about declaration and this is the way my code looks now:

Private Sub Document_New()

'declare vars
Dim strCaseNum As Variant
Dim strDefName As Variant
Dim strCrimCrt As String
Dim strVolume As Variant
Dim strPage As Variant
Dim strTransNum As String


'set name vars to input
strCaseNum = InputBox("Enter the Case Number.&quot
strDefName = InputBox("Enter the Defendant's Name.&quot
strCrimCrt = InputBox("Enter ""CRIMINAL"" or the Numbered Court.&quot
frmVolumePageOrTransactio.Show
If optVolumePage = True Then
strVolume = InputBox("Enter The Volume Number.&quot
strPage = InputBox("Enter The Page Number.&quot
Else
strTransNum = InputBox("Enter the Transaction Number&quot
End If

'set field results to name vars
ActiveDocument.FormFields("Text1&quot.Result = strCaseNum
ActiveDocument.FormFields("Text2&quot.Result = strDefName
ActiveDocument.FormFields("Text3&quot.Result = strCrimCrt
ActiveDocument.FormFields("Text7&quot.Result = strVolume
ActiveDocument.FormFields("Text8&quot.Result = strPage
ActiveDocument.FormFields("Text9&quot.Result = strTransNum

End Sub

I think if I can get this one, I may be able to complete the 200+ macros that are in use now.

Thanks for any additional help you can give me.

Gwynne

 

[Loomah]

Hi Gwynne
I was working on the assumption that you have a form named
"frmVolumePageOrTransactio"
and on that form you have an option button named
"optVolumePage"

If that is the case you have no need to declare them as variables in your routine.

You seem to have picked up on this in your new code!

You don't say whether your revised code works or not! Are you still having the same problem(s)?

Unfortunately I haven't gone to the effort of recreating your problem and am just working from thoughts - and at the moment your code looks fine to me!!!

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

What Loomah is saying is...

UserForm1.Show

works just as well. If you wish, in the Properties Window you can give UserForm1 another name that might be more descriptive.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

You missed the point about the multiple variables on a Dim statement. If they are all strings (and they are) then you should write it this way:

 Dim strCaseNum As

[blue]

String

[/color]

 Dim strDefName As

[blue]

String

[/color]

 Dim strCrimCrt As String
 Dim strVolume As

[blue]

String

[/color]

 Dim strPage As

[blue]

String

[/color]

 Dim strTransNum As String

To reference the buttons from your macro, you need to qualify the reference with the form name:

  If

[blue]

frmVolumePageOrTransactio

[/color]

.optVolumePage = True Then

You still have not allowed the users to cancel out of the process, but you are allowing the intput to be left blank. Whether this will be a problem for your users is not for me to say. (Although I should think it would be quite frustrating for them.) Also, with 135 brain-dead users, you will probably have quite a bit of turn-over. New hires might not be quite as challenged, but when they (the new hires) see this User Interface (which is probably totally unlike any other they have used before), there may be some training issues anyway. "You pays your money and you takes your choice"

 

[Gwynne]

I guess I must be as brain-dead as my users. I have corrected my Dim statements, and replaced the If… statement, but when I try to step through the code, I get an error message “Object required.” So I placed another Dim statement with “frmVolumePageOrTransactio As Object”. When I stepped through then, I get the error message “Object variable or With block variable not set.” I’m about to go nuts over this one little part. But, thanks again for everyone’s help.

 

[Zathras]

Sounds like it may be an XP thing -- I can't help you there. Does the code work on Word 97 or Word 2K?

Also can you indicate precisely which line of code is highlighted when the error occurs?

One more thing to try: Do you get the same error if you run the code from a separate code module?

 

[Gwynne]

Thanks again for your prompt answer. No wonder you were selected as a top expert. Congratulations!

The code that is highlighted is at the point where I show the form.

"frmVolumePageOrTransactio.Show" (without the "&quot

Additionally, I don't have any computers that are running Word97 or 2K so I won't know if my error is because of XP.

 

[RJRatLD]

Try the following code change, it may help. Also when you type frmVolumePageOrTransactio. do the list members appear? if not then you may have misnamed the form. When you took the n off the end of frmVolumePageOrTransactio did you also rename the form?

frmVolumePageOrTransactio.Show
If frmVolumePageOrTransactio.optVolumePage = True Then
strVolume = InputBox("Enter The Volume Number.&quot
strPage = InputBox("Enter The Page Number.&quot
Else
strTransNum = InputBox("Enter the Transaction Number&quot
End If

Hope it help.

 

[Loomah]

Gwynne
Can I just back peddle a little here?

Would you confirm whether or not you have in your vb project:-

A user form for which you have changed the name property in the properties window to 'frmVolumePageOrTransactio'

On this user form you have a single option button (might be better with a check box but that's academic!) And you have changed the name of the option button to 'optVolumePage' in the name section of the properties window.
?
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[RJRatLD]

I should also have mentioned that if you have declared the form as an object then I think this will produce the error as the show method is not a member of an object type. I would remove that declaration as the form already exists in your project.

 

[Gwynne]

Thanks RJRatLD and Loomah for answering. (Bet you’ll be glad to be rid of me!)

RJRatLD, I have already incorporated the code that you gave me. The first line of it is where I’m getting the error.

Loomah, Yes, I have created a UserForm, and changed the name in the properties window to VolumePageOrTransactio. In this form, I have 2 radio buttons, the first for VolPage and the second one for Trans and have named them as such. Hope this helps.

 

[Zathras]

Ok, when I add the line

  Dim frmVolumePageOrTransactio As Object

I get the error message:
Run-time error '91':
Object variable or With block variable not set

When I take that line out of the macro code, it works just the way you want it.

By declaring an object locally in your code with the same name as your form, you are overriding the form with that object. And of course, since there is no "Set" operation, Excel complains that the object variable is not set. You don't want to set an object variable - so remove the declaration and allow Excel to find the form.

 

[Gwynne]

I'm sorry to do this to you, but here is my cleaned up code. I have highlighted the line where I get the error "Object Required" when I take out the "Object" variable. Oh, BTW, this is a Word macro.


Private Sub Document_New()

'declare vars
Dim strCaseNum As String
Dim strDefName As String
Dim strCrimCrt As String
Dim strVolume As String
Dim strPage As String
Dim strTransNum As String


'set name vars to input
strCaseNum = InputBox("Enter the Case Number.&quot
strDefName = InputBox("Enter the Defendant's Name.&quot
strCrimCrt = InputBox("Enter ""CRIMINAL"" or the Numbered Court.&quot
frmVolumePageOrTransactio.Show
If frmVolumePageOrTransactio.optVolPage = True Then
strVolume = InputBox("Enter The Volume Number.&quot
strPage = InputBox("Enter The Page Number.&quot
Else
strTransNum = InputBox("Enter the Transaction Number&quot
End If


'set field results to name vars
ActiveDocument.FormFields("Text1&quot.Result = strCaseNum
ActiveDocument.FormFields("Text2&quot.Result = strDefName
ActiveDocument.FormFields("Text3&quot.Result = strCrimCrt
ActiveDocument.FormFields("Text7&quot.Result = strVolume
ActiveDocument.FormFields("Text8&quot.Result = strPage
ActiveDocument.FormFields("Text9&quot.Result = strTransNum

 

[Zathras]

I just did a cut and paste from your last post and it works without alteration. I'm afraid there is nothing more I can do to help you.

You might try to see if you can show a simple form with a simple macro. Start with a blank (new) document and just insert a form with nothing on it, and insert a module with this code:

   Sub test()
     UserForm1.Show
   End Sub

Then try to run the macro (F5) to see if the form appears. If that works, then build on that. Keep the default names for everything. If you can get it to work that way, then begin changing names if you want to as long as it keeps working.

If that doesn't help then you should start a new thread, include the code from your last post, and use the subject line:

Macro works in Word97 but not in XP

 

[RJRatLD]

All i can suggest is that you check the spelling of the form name in the forms properties. The error you are getting is reproduced by me when the form is not recognised. The only difference I have is that I am using word 2000 and not XP.