Errors with VB Coding?

[golf4]

Hi, everyone -

With the help of everyone here, I think I'm picking up a lot on the "vb coding" thing. I've created, and modified, what I think is pretty complicated coding, but for some reason, :x , I'm experiencing some error messages when I try to use some of my command buttons. I'll try to explain what's happening, and I hope I can get some imput from some of the XL experts here.

I continue to work on my "living" income calculation spreadsheet, and one of my worksheets I have my staff printing 4 copies with text boxes at the bottom of the pages. I have created/modified coding so that, when staff prints out the 4 copies, each text box is populated with a check-mark - relieves staff from marking each copy for each department/person. I have indicated the code below:

Sub PrintS8Amendment()
With Sheet14
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
End With
End Sub

(I'm using the "P" so that, when I format the text boxes with WingDings2, the macro with populate the boxes with the check-mark symbol. Sheet14 refers to the S8 Amendment Worksheet.)

I have created 2 command bottons to print out the 4 worksheets: one directly on the S8 Amendment Worksheet (Sheet14) and one on another worksheet known as the Data Entry Sheet. The button(s) code:

- on the Data Entry Sheet:
Private Sub CommandButton3_Click()
PrintS8Amendment
End Sub

AND

- on the S8 Amendment Worksheet:
Private Sub CommandButton1_Click()
PrintS8Amendment
End Sub.

The problem is that one command button with print the worksheets off just great and one generates a run-time error. The button located directly on the S8 Amendment works great. The button on the Data Entry Sheet, using the "same" coding, generates the following run-time error:

Run-time error '1004': Unable to get the text property of the Charactors class.

Can anyone tell me why one prints the worksheets fine and one doesn't and, hopefully, how to fix the problem? I have created a number of other command buttons and other codings that don't involve the multiple text box print trick, and they work fine.

Any help would be great. :D :D :D

Thanks

 

[Damon Ostrander]

Hi golf4,

I believe the problem is simply that when you execute your PrintS8Amendment macro from a sheet other than Sheet14--meaning that Sheet14 is not active--when the code tries to access the Selection.Characters.Text property, it is looking for the selection on the active sheet, NOT Sheet14. The best way to solve this is to eliminate selections altogether since they are neither necessary nor efficient. Write your code like this:

Sub PrintS8Amendment()
With Sheet14
.Shapes("Text Box 1").Characters.Text = "P"
.Shapes("Text Box 2").Characters.Text = ""
etc.

 

[golf4]

Hi, Damon -

Thanks for the response. I had a feeling it was something like this or the possible security differences between XL2000 (home) and XP (at the office). I'll give it a shot, when I get home tonite, and let you know.


Thanks again

 

[golf4]

Hi, Damon -

I attempted your suggestion. My coding now appears like so:

Sub PrintS8Amendment()
With Sheet14
.Shapes("Text Box 1").Characters.Text = "P"
.Shapes("Text Box 2").Characters.Text = ""
.Shapes("Text Box 3").Characters.Text = ""
.Shapes("Text Box 4").Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Characters.Text = ""
.Shapes("Text Box 2").Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Characters.Text = ""
.Shapes("Text Box 3").Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Characters.Text = ""
.Shapes("Text Box 4").Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Characters.Text = ""
.Range("A12").Select
End With
End Sub

I attempted to run it from the Data Entry Sheet (other than Sheet14, and received a different run-time error message:

"Run-time error '438': Object doesn't support this property or method".

I'm now completely thrown...............

Any other suggestions?


Thank a million :P