Office XP vs Office 2000 versions using same VBA 6.3 error 461 1

[YACHTIE]

User form in Word2002 of Office XP macro using Active X buttons does not work with Office 2000 versions. Even though same VBA 6.3 is installed.
Get "Method word or data member not found (Error 461)".
Short of upgrading and synch'ng all PC's is there a workaround for this?

 

[fumei]

You do not say, but have you got your code to run properly at all, on any machine?

If not, then the rrro is likely a real one. Something is bnot right with your code. The method and/or data member is not found. Could be a fee things.

1. a Syntax error. I am ssuming you are using Option Explicit? And all Use Auto Syntax etc etc set ON under Tools > Options in the VBE.

2. You are missing some reference. This is fairly common. if the reference is missing, but you are calling on an object ormethod that uses that reference, then obviously the compiler can do much with it, as it can not "find" it.

Could you post some code so we can look at it?

Gerry

 

[YACHTIE]

Thanks Gerry, here is the code in question:

The Word form has two buttons that when activated run the code below also there are 6 optionbuttons and lots of Text, checkbox and drop-down form fields.
I am no expert on VBA and basically managed to obtain the required functionality from trial and error and recording a macro and lurking a lot on this forum.
In answer to your question #1 is No and #2 is Yes.

Private Sub AddPartCommandButton1_Click()
' AddRow Macro, this procedure appends a duplicate formatted table row
' to a forms Word document
'Macro recorded 8/17/2004 by G. Houweling
' Unlock the document
    ActiveDocument.Unprotect Password:="MES3052D"
'Position cursor in table
' Created bookmark (PartText39) in last row first cell
    ActiveDocument.Bookmarks("PartText39").Range.Fields(1).Result.Select
       Selection.MoveRight Unit:=wdCharacter, Count:=47, Extend:=wdExtend
    Selection.Copy
    Selection.MoveDown Unit:=wdLine, Count:=1
       Selection.TypeParagraph
    Selection.PasteAndFormat (wdPasteDefault)
     ' Lock the document
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="MES3052D"
End Sub
Private Sub AddPartCommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Clicking this button adds new PARTS/Material rows below"
End Sub
Private Sub CopyTableRow_Click()
'
' AddRow Macro, this procedure appends a duplicate formatted table row
' to a forms Word document
'Macro recorded 8/17/2004 by G. Houweling
' Unlock the document
    ActiveDocument.Unprotect Password:="MES3052D"
'Position cursor in table
' Created bookmark (Text32) in last row first cell
    ActiveDocument.Bookmarks("DocAffText32").Range.Fields(1).Result.Select
    Selection.MoveRight Unit:=wdCharacter, Count:=6, Extend:=wdExtend
    Selection.Copy
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
    Selection.PasteAndFormat (wdPasteDefault)
    'Selection.PasteAppendTable
     ' Lock the document
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="MES3052D"
End Sub
Private Sub CopyTableRow_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Clicking this button adds new Docs Affected rows above"
End Sub
Private Sub OptionButtonCFGN_Click()
'If OptionButtonClass2Yes.Value = True Then
'OptionButtonCFGNYes.Value = True
'End If
End Sub
Private Sub OptionButtonCFGY__MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Check Here only if the CN is CLASS 1"
End Sub
Private Sub OptionButtonClass2__MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Check Here if the Part or Document revision will be incremented only"
End Sub
Private Sub OptionButtonClass1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Check Here if the CN causes the partnumber to change"
End Sub
Private Sub OptionButtonClass2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
StatusBar = "Check Here if the CN is revision changes only, ie. reworked parts will be interchangeable"
End Sub
Private Sub OptionButtonCVFYes_Click()
If OptionButtonCVFYes.Value = True Then
    Documents.Open FileName:="K:\Quality Forms\Change_Notice_Validation_(MES-3053).doc"
End If
End Sub

Private Sub OptionButtonCVFYes_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StatusBar = "Clicking this button will also open the Validation Form"
End Sub

 

[fumei]

1. Where exactly are you getting the error? There are a number of Subs here, and I don't know exactly where your error occurs.

2. You are using Selection far too much. This could be better designed.

3. You have an Option Button double click event that only does a status line display. This seems odd to me. A user is going to double click an Option button just to see status line text?

    ActiveDocument.Bookmarks("DocAffText32").Range.Fields(1).Result.Select
    Selection.MoveRight Unit:=wdCharacter, Count:=6, Extend:=wdExtend
    Selection.Copy
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
    Selection.PasteAndFormat (wdPasteDefault)
    'Selection.PasteAppendTable

What exactly are you doing here?
Are you using a Table Style?
It looks like you copying the bookmark, sort of, but what is the selection extend doing? You have a TypeParagraph. This should not make a new table row...a new line yes, but a new table row? Why are you copying the result?

ActiveDocument.Bookmarks("DocAffText32").Select will select it. What are you doing with the Range.Fields?

Gerry

 

[YACHTIE]

Gerry,
I have no doubt there may be a better way, am not at all VBA capable.
However essentially I created this with help from the macro recorder and then tweaked it. (Am just trying VBA out of necessity and with lots of help from you guys and the FAQ)
The document it is used on is a MsWord userform with lots of tables and checkboxes, list boxes and some OCX like the two buttons; the two macros "CopyTableRow" and "AddPartCommandButton1" control. The rest are just optionbuttons and no have real functionality other than the "mouse over" event giving us some help info in the statusbar.
The error occurs when either one of the two "add a row" buttons is clicked upon, and both are somewhat identical in that they both add additional rows albeit different ones.
I have since isolated the actual error causing line and have changed it to:

   Selection.PasteSpecial (wdPasteDefault)
    ' was in XP: PasteAndFormat (wdPasteDefault)

the "PasteAndFormat" is apparently not valid in Office 2000 and the "PasteSpecial" fixes the problem and runs the macro fine in 2000, however back on XP it adds the rows ok but they are thicker and spaced differently so it was an improvement of sorts but not perfect yet.

On your item 3 question:

3. You have an Option Button double click event that only does a status line display. This seems odd to me. A user is going to double click an Option button just to see status line text?

I am still experimenting with that item, for some unexplainable reason I can get the "mouse over event" to give me info in the status bar when the mouse is over one (just the first) button but not over the others, no matter what. I should mention that these 4 buttons are all in the same row but in separate cells in pairs of two.
Would love to hear your suggestions on this as well but it was not my priority just one of those "why th#$%^& does this not work " questions but not the "show stopper".

As for your last questions:

What exactly are you doing here?
Are you using a Table Style?
It looks like you copying the bookmark, sort of, but what is the selection extend doing? You have a TypeParagraph. This should not make a new table row...a new line yes, but a new table row? Why are you copying the result?

ActiveDocument.Bookmarks("DocAffText32").Select will select it. What are you doing with the Range.Fields?

Starting form the bottom up: the "Range.Fields" is there because the bookmark being in a formfield of a table needs this to work. The "TypeParagraph" I discovered solves my problem of allowing more than three rows added to the table. without it after clicking on the button more than 3 times no more rows were added.

I have added remarks "'" to every line of code below for explanantion:

' the following line goes to the first formfield in the bottom row LH cell and selects it 
    ActiveDocument.Bookmarks("DocAffText32").Range.Fields(1).Result.Select
'this line then tabs over to the right to select the adjacent formfields and cells of the row
    Selection.MoveRight Unit:=wdCharacter, Count:=6, Extend:=wdExtend
    Selection.Copy
'After copying the row move down and paste the copied selection
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
    Selection.PasteAndFormat (wdPasteDefault)

hope this explains it better and I welcome any suggestions for improvement.

TIA for all your effort

 

[fumei]

I asked because while your comments in the code may be clear to you, it is not clear to me. You are doing well though with your efforts, so please do not take it as if I am being rude with my comments. I hope you do not.

the following line goes to the first formfield in the bottom row LH cell and selects it

"Range.Fields" is there because the bookmark being in a formfield of a table needs this to work.

Incorrect. You do NOT need this to work. You unprotected the document, so (as I posted previously)

ActiveDocument.Bookmarks("DocAffText32").Select

will select the bookmark. Again, you do not need that range.field blah blah. However, the reason I was asking what are you doing is that...what are you doing???? What you are doing is copying the row, aren't you? You did not answer the question.

Dim r As Range
' go to bookmark, just to make sure
' in the right table
Selection.GoTo what:=wdGoToBookmark, Name:="DocAffText32"

' set range object as last row in table
Set r = ActiveDocument.Tables(1).Rows(ActiveDocument.Tables(1).Rows.Count).Range

'  copy and paste it
r.Copy
r.Paste

'  release range object
Set r = Nothing

the above code never makes a selection, therefore never uses GUI resources. It does not Select the bookmark, then extend the selection across the row etc etc.

This is why I was asking you, what you are doing? Not the steps, but what you are doing.

If I understand it correctly (and maybe I am not), but you want to copy the row and make a new row with that copy. THIS is what I was asking. Intention.

The above code with copy the formfields in the row as well. However, the copied row will retain the original names, the original row formfields will not.

Again, intention. What do you need to happen?

Next, you mention clicking on the button more than three times. Please explain further.

Is the Selection.TypeParagraph in the table or outside it? In any case, properly set up you likely do not need it.

Something to think about. You are using a lot of stuff to get information to the user - especially on the Status bar. However, the fact is you have a UserForm running. Why not put your messages there? Right in front of the user. Use labels for messages. You can make them change, or disappear. Heck you could have the mouse_move event for the buttons make a label text be 16 pt BOLD. Use the .Caption property of a label. The following assumes a Label named "Messages" on the UserForm.

Private Sub OptionButtonCVFYes_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Messages.Caption = "Clicking this button will also open the Validation Form"
End Sub

Note that the above will set the label caption with the mouse_move of the button. When the mouse moves away, the caption will remain. This is not really what you want...so.....

make the mouse_move of the UserForm itself make the caption = "".

The mouse moves over the button, Caption gives message; mouse moves over the form (NOT over the button now), message becomes blank. This is the way to simulate a mouse_over event on a UserForm.

I think I am wandering off post here....

Personally I think it is much better to have information right on the form, since that is where (generally) the attention will be.

Gerry

 

[YACHTIE]

Thanks Gerry for your response,
I like your suggestion and tried it however it adds the table above the one I need added instead. Can't quite figure out why, actually the row being added does not even have a bookmark assigned to it. Any suggestions??

Secondly I wanted to try your "MouseOver " suggestion but it produces a "Object required" error on the following line:

Messages.Caption = "Clicking the Yes button will also open the Validation Form"

Thirdly, I'd like to also try your "MouseMove Off" routine but don't understand how, can you be more specific and mabe give me an example?

thanks

 

[fumei]

Regarding the row (although you state “table”, I think you mean row), going above, that is my fault.

The Selection point goes to the bookmark/formfield. The Range copies the row, and pastes. Of course it paste at the Selection point…which is at the beginning of the row, so it inserts the copy above. There is no bookmark assigned to the row. YOUR code has a formfield (which is a bookmark) in the row. All I am doing is going to that bookmark, and copying the row.

It should be:

Dim r As Range
' go to bookmark, just to make sure
' in the right table, then collapse to a point
Selection.GoTo what:=wdGoToBookmark, Name:="DocAffText32"
Selection.Collapse Direction:=wdCollapseStart

' set range object as last row in table
' make the Selection the range end
Set r = ActiveDocument.Tables(1).Rows(ActiveDocument.Tables(1).Rows.Count).Range
Selection.Start = r.End

' copy range and paste it
r.Copy
r.Paste

' release range object
Set r = Nothing
End Sub

Regarding the Object required error:
From my post:

The following assumes a Label named "Messages" on the UserForm.

Did you put a label on the form, and name it "Messages"? If not...well there is no object named Messages, and trying to set its Caption will return an error, well, because an "Object required".

Please read the post again.

Put a label on the form. Give it whatever name you like... I used "Messages", because that is what it is for. What ever name you use, use that name in the code. Right?

You have a mouse_move event for the option button (I will not go into the design anymore...you can make your own choices there). It changes the label Caption to whatever message you want.

The mouse moves away from the option button. The Caption (the display text of the label) will NOT change.

SO...you make another mouse_move, but for the form itself.

' THE OPTION BUTTON
Sub OptionButtonCVFYes_MouseMove(ByVal blah blah blah blah)
Messages.Caption = "Clicking the Yes button wi blah blah blah

' THE FORM
Sub UserForm_MouseMove(ByVal blah blah blah)
Messages.Caption = ""

This makes the caption change to blank when the mouse is moved on the UserForm. The caption changes to your option button message when the mouse is moved to the option button. The caption changes to the UserForm message (...blank) when the mouse is moved on the UserForm; the caption is changed to the option button message when the mouse is moved to the option button; the caption changes to the UserForm message (...blank) when the mouse is moved on the UserForm;

I hope you can get that because I don't know how I can be more specific. Make a label. Option button mouse move makes the label text one thing. UserForm mouse move makes the label text another thing.

I guess I should say that if your UserForm is not named UserForm - the default, and yes you should name it something else - then it would not be UserForm_MouseMove, it would be WhateverName_MouseMove.


Gerry

 

[YACHTIE]

Hi again
It's been awhile since but I am again trying to get this to work for me.
First I apologize for thinkin I was actually using a "UserForm" but of course it's all Bill Gates' fault really....., using words that have been used for years and then arbitrarily assign a new meaning,like "Windows" and "UserForm" etc etc.

My Userform as it turns out is really a Word document with tables (5 separate tables in fact) and rows with different columns in each. The table cells contain "formfields" where the "user" ultimately enters information.
There are also a number of Active X radio buttons and two CommandButtons on the form to insert a specific row for expansion purposes.
The CommandButtons being the reason for this thread and in the previous post "fumei" stated:

Regarding the row (although you state “table”, I think you mean row), going above, that is my fault.

The Selection point goes to the bookmark/formfield. The Range copies the row, and pastes. Of course it paste at the Selection point…which is at the beginning of the row, so it inserts the copy above. There is no bookmark assigned to the row. YOUR code has a formfield (which is a bookmark) in the row. All I am doing is going to that bookmark, and copying the row.

As you can see, I really did mean "table" and not "row" as your code does, despite referring to the bookmark in the bottom of the second table, paste a new row in the first table. I also believe (am not to good with VBA code)your code only copies one of the cells of the row and not the whole row. I have tried manipulating the code but no matter what I tried it keeps adding to the first table.
What it needs to do is copy the whole table row of the bookmarked formfield cell and paste that underneath the row in question.
Hope I made it clear for you this time.

 

[fumei]

That is because the code has it as Table(1).

If you want to do it for all the tables, then you should change the index number.

However, the code does a jump to a named formfield/bookmark. Are you trying to tell me you have the same named formfield in multiple tables?

Do you want to go to the ONE formfield, copy THAT row, and make new rows with THAT on ALL the tables?

As for just copying a cell, I am going to have to say that either there IS only one cell in that row, or you have changed something. Going to post some more code.

This has been tested with a document with:

3 tables, each of which has a formfield in the last row/first cell. The three tables have varying number of columns.

1 command button in the document that fire the Sub.

The Sub copies EACH of the LAST row to the respective table. In other words, Table1 gets its own last row; Table2 gets it own last row etc.

The formfields are hardcoded into an array so that it goes to the right formfield.

Here is the Sub.

Sub NewRows()
Dim mTable As Table
Dim r As Range
Dim myFields(2) As String
Dim i As Integer
ActiveDocument.Unprotect
' these are the three formfield names
  myFields(0) = "MyText_Table1"
  myFields(1) = "MyText_Table2"
  myFields(2) = "MyText_Table3"
For Each mTable In ActiveDocument.Tables
' go to bookmark, just to make sure
' in the right table, then collapse
    Selection.GoTo what:=wdGoToBookmark, Name:=myFields(i)
    Selection.Collapse Direction:=wdCollapseStart
        
' set range object as last row in table
' make the Selection the range end
    Set r = ActiveDocument.Tables(i + 1).Rows(ActiveDocument.Tables(i + 1).Rows.Count).Range
    Selection.Start = r.End
'  copy range and paste it
    r.Copy
    r.Paste
'  release range object
        Set r = Nothing
        i = i + 1
Next
ActiveDocument.Protect (wdAllowOnlyFormFields), Password:=""
End Sub

here is the Sub of the AxtiveX control (a command button named "cmdNewRow").

Sub cmdNewRow_Click()
Call NewRows
End Sub

As I still really don't get what is happening, this is the best I can do. I am basing it on the assumption that you have DIFFERENT formfields, in different tables, but, shrug....



Gerry

 

[YACHTIE]

Thanks for trying....you wrote:

That is because the code has it as Table(1).

If you want to do it for all the tables, then you should change the index number.

I tried that but it did not make any difference it still kept pasting in the table above the one I need it to paste the row.

However, the code does a jump to a named formfield/bookmark. Are you trying to tell me you have the same named formfield in multiple tables?

No I don't and the row that your code pastes has no bookmark

Do you want to go to the ONE formfield, copy THAT row, and make new rows with THAT on ALL the tables?

True for the first part of your question here, only want to make new rows in the one table I am copying the last row from.

Going to post some more code.

This has been tested with a document with:

3 tables, each of which has a formfield in the last row/first cell. The three tables have varying number of columns.

1 command button in the document that fire the Sub.

The Sub copies EACH of the LAST row to the respective table. In other words, Table1 gets its own last row; Table2 gets it own last row etc.

Now it sorta works, in that it tries to add rows to all tables even though I have modified the code (probably not correctly) with only one bookmark to add one row to one table but it continues to add to the table above it as well and then in the third table it runs into a "vertically merged table" and chokes (runtime error 5991) and it also only works once as it seems to remove my bookmark also.
If you can simplify the code for me that it only adds the row to the same table the bookmark is in that may help for me.
Thanks a lot