Word 2010 the requested member of the collection does not exist

flyboy3300 · #1 10-08-2015, 01:17 PM

I am using the following code with several other codes in Word 2010. If I do not have any other codes or this one is the first code then it works but as soon as I add others before this one I get the error msg the requested member of the collection does not exist

I am trying to color the background of the drop down box when I select the different items

Code:

Option Explicit
Dim i As Long
 
Private Sub Document_Open()
ActiveDocument.Bookmarks("DropDown").Range.Select
Call Index
End Sub
 
Sub Index()
i = Replace(Selection.FormFields(1).Range.Bookmarks(1).Name, "DropDown", "")
End Sub
 
Sub RiskClassification()
Dim sText As String, oFld As FormField
With ActiveDocument
  Set oFld = .FormFields("Dropdown" & i)
  sText = oFld.Result
  If .ProtectionType <> wdNoProtection Then .Unprotect Password:=""
  With oFld.Range
    Select Case sText
      Case Is = " " 'white
        .Font.Color = wdColorWhite
        .Shading.BackgroundPatternColor = wdColorWhite
      Case Is = "1A" 'red
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorRed
      Case Is = "2A" 'red
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorRed
      Case Is = "3A" 'orange
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorOrange
      Case Is = "4A" 'yellow
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorYellow
      Case Is = "1B" 'red
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorRed
      Case Is = "2B" 'orange
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorOrange
      Case Is = "3B" 'yellow
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorYellow
      Case Is = "4B" 'blue
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorBlue
      Case Is = "1C" 'orange
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorOrange
      Case Is = "2C" 'yellow
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorYellow
      Case Is = "3C" 'blue
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorBlue
      Case Is = "4C" 'green
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorGreen
      Case Is = "1D" 'yellow
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorYellow
      Case Is = "2D" 'blue
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorBlue
      Case Is = "3D" 'green
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorGreen
      Case Is = "4D" 'green
        .Font.Color = wdColorBlack
        .Shading.BackgroundPatternColor = wdColorGreen
    End Select
  End With
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
End With
End Sub

macropod · #2 10-08-2015, 05:36 PM

It's not clear what's going on with your document, since the code on it's own provides no context. That said, it seems to me that having:
Dim i As Long
is not consistent with:
i = Replace(Selection.FormFields(1).Range.Bookmarks(1) .Name, "DropDown", "")
since the latter returns a string, not a number.

Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu.

flyboy3300 · #3 10-08-2015, 07:54 PM

Thanks for your quick reply, I have found the issue. It was that my bookmark was named incorrect (somehow I had but a 1 on the end of the name). It is all working now. Thanks again.

flyboy3300 · #4 12-03-2015, 06:40 AM

I have another question in reference to this document. I was able to get the dropdown box to work with the color. I named the box dropdown. My question is if I run a macro to duplicate this box and want to run the same macro to set the color how do I do that when it looking for the name "dropdown" and you cannot name boxes the same name. I have enclosed the word file that I am using with the password "newfie". Thanks for the help.

macropod · #5 12-03-2015, 04:02 PM

Aside from the horrid formatting (e.g. spaces used to align text instead of paragraph centring), your document has a mix of content controls and formfields. You should use one or the other - they're not designed to work together and can behave strangely when combined.

As for replicating the dropdown, presumably you want to replicate the entire row. For code to do that, see: https://www.msofficeforums.com/word-...word-form.html
As for the shading, the dropdown in the newly-added row should have the same 'on-exit' macro call as the existing row. So, provided you code your colouring macro to work with whatever dropdown it's called from, you should get the same shading behaviour. The code in the link contains comments & code for auto-numbering the formfields (assuming there's only one of a given type on the row).

flyboy3300 · #6 12-05-2015, 08:33 AM

Thanks for the berating comments, if I was an expert then I guess i would not need to be on here to ask questions. The link that you provided does not work to get the name of the bookmark, every time that you run it the name goes away so the other will not work to put the shading in due it looking for the name in the bookmark

macropod · #7 12-05-2015, 01:18 PM

The formatting issue is hardly expert-level stuff; it's something very basic. Mixing content controls and formfields isn't wise and you need to know that. Ignore the advice at your own peril.

As for the link, it works as described. It does not delete any bookmarks. Kindly read the comments in the code. The approach is different to what you've done so far, but that's largely because what you want to do requires a different approach.

ninaiva · #8 07-29-2016, 06:28 AM

Hi there! Could anyone please help me understand why I get the error msg the requested member of the collection does not exist?

Guessed · #9 07-31-2016, 07:52 PM

The error message is caused by code trying to do something with an object that doesn't exist. You haven't posted any code so we can't guess what this object might be.

The .docx file format doesn't/shouldn't contain any code.

ninaiva · #10 08-01-2016, 05:36 AM

Sorry, I guess I forgot to save the macro in the docx.
After using the code below in the attached file (password: cc), I get msg 'runtime error '5941'. I've read many forum posts on this, I tried different codes but I still can't get it to work. This is just an example that I want to apply later in the document I'm actually working on - it has 24 dropdown menus with 4 options. I want a certain option to color the cells' background. Thanks!

Code:

Option Explicit
Dim i As Long

Private Sub Document_Open()
ActiveDocument.Bookmarks("DropDown").Range.Select
Call Index
End Sub

Sub Index()
  i = Replace(Selection.FormFields(1).Range.Bookmarks(1).Name, "DropDown", "")
End Sub

Sub ColorDropDown()
Dim sText As String, oFld As FormField
With ActiveDocument
  Set oFld = .FormFields("Dropdown" & i)
  sText = oFld.Result
  If .ProtectionType <> wdNoProtection Then .Unprotect Password:=""
  With oFld.Range
    Select Case sText
      Case Is = "G" 'green
        .Font.Color = wdColorBrightGreen
        .Shading.BackgroundPatternColor = wdColorBrightGreen
      Case Is = "Y" 'yellow
        .Font.Color = wdColorYellow
        .Shading.BackgroundPatternColor = wdColorYellow
      Case Is = "R"  'red
        .Font.Color = wdColorRed
        .Shading.BackgroundPatternColor = wdColorRed
    End Select
  End With
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
End With
End Sub

Guessed · #11 08-01-2016, 03:13 PM

Your code is looking for a formfield called "Dropdown1". This doesn't exist in that document.
Your 4 bookmarks are called Dropdown, Dropdown2, Dropdown3, Dropdown4

The sub Index fails for two reasons. Replace returns a string not a Long. Also, removing the string "DropDown" from the string "DropDown" returns an empty string (not the number 1 you were probably expecting).

ninaiva · #12 08-01-2016, 11:07 PM

Thank you for checking it. Very much appreciated. Now I think I understand (more or less) why the code doens't work, but I lack the expertise to fix it. Would you have any suggestions?

Guessed · #13 08-01-2016, 11:42 PM

I think you only need one macro

Code:

Sub ColorDropDown()
  Dim sText As String, oFld As FormField
  With ActiveDocument
    If .ProtectionType <> wdNoProtection Then .Unprotect Password:=""
    For Each oFld In .FormFields
      Select Case oFld.Result
        Case "G"
          oFld.Range.Font.Color = wdColorBrightGreen
          oFld.Range.Shading.BackgroundPatternColor = wdColorBrightGreen
        Case "Y"
          oFld.Range.Font.Color = wdColorYellow
          oFld.Range.Shading.BackgroundPatternColor = wdColorYellow
        Case "R"
          oFld.Range.Font.Color = wdColorRed
          oFld.Range.Shading.BackgroundPatternColor = wdColorRed
      End Select
    Next oFld
    .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
  End With
End Sub

If your document has other formfields in it then you might need the code to restrict itself to a defined range (bookmarked) or something similar but you don't need to know every named formfield.

ninaiva · #14 08-02-2016, 12:49 AM

Wow, it works! Also on the document I am working on. I can't thank you enough!

Another thing - the options change colors only after I run the macro which is not ideal for me. I will be sending this document to people (I am doing a psychological research) so I was wishing that the colors of the options would change while people are picking differnet options. I can't ask them to run a macro after they've made their decision. Is that even possible?

Guessed · #15 08-02-2016, 01:03 AM

The sample doc you posted had the macro running on exit from each of these fields. That means that as the user moves the cursor away from that field, the macro fires and sets the colours for all those fields. There is no event trigger to make the macro fire when they change the choice and before they leave the field.

The user doesn't manually run the macro, it happens automatically as they leave the field. It all relies on the users enabling macros though. If they don't do that then nothing happens.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
vba error method or data member not found in excel 2010	Rose roon	Excel Programming	0	09-08-2015 03:51 AM
5941 requested member of collection does not exist Prevents Userform from Showing	marksm33	Word VBA	1	02-22-2014 08:56 AM
Outlook 2010 problem (new member)	Cullers	Outlook	0	01-21-2011 10:18 AM
[PowerPoint 2010] Change font for all slides in an exist *.PPTX file	LongTTH	PowerPoint	0	12-15-2010 02:07 AM
Pack and Go: Does it Exist in PPT 2010?	tatihulot	PowerPoint	1	11-16-2010 08:35 AM