Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-08-2015, 01:17 PM
flyboy3300 flyboy3300 is offline Word 2010 the requested member of the collection does not exist Windows 7 32bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Novice
Word 2010 the requested member of the collection does not exist
 
Join Date: Oct 2015
Posts: 4
flyboy3300 is on a distinguished road
Default Word 2010 the requested member of the collection does not exist

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

Last edited by macropod; 10-08-2015 at 05:17 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 10-08-2015, 05:36 PM
macropod's Avatar
macropod macropod is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-08-2015, 07:54 PM
flyboy3300 flyboy3300 is offline Word 2010 the requested member of the collection does not exist Windows 7 32bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Novice
Word 2010 the requested member of the collection does not exist
 
Join Date: Oct 2015
Posts: 4
flyboy3300 is on a distinguished road
Default Corrected

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.
Reply With Quote
  #4  
Old 12-03-2015, 06:40 AM
flyboy3300 flyboy3300 is offline Word 2010 the requested member of the collection does not exist Windows 7 32bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Novice
Word 2010 the requested member of the collection does not exist
 
Join Date: Oct 2015
Posts: 4
flyboy3300 is on a distinguished road
Default

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.
Attached Files
File Type: docm SPM-10 IEP EVALUATION Test.docm (49.9 KB, 13 views)
Reply With Quote
  #5  
Old 12-03-2015, 04:02 PM
macropod's Avatar
macropod macropod is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 12-05-2015, 08:33 AM
flyboy3300 flyboy3300 is offline Word 2010 the requested member of the collection does not exist Windows 7 32bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Novice
Word 2010 the requested member of the collection does not exist
 
Join Date: Oct 2015
Posts: 4
flyboy3300 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 12-05-2015, 01:18 PM
macropod's Avatar
macropod macropod is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 07-29-2016, 06:28 AM
ninaiva ninaiva is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
ninaiva is on a distinguished road
Default

Hi there! Could anyone please help me understand why I get the error msg the requested member of the collection does not exist?
Attached Files
File Type: docx DropDown.docx (12.1 KB, 9 views)
Reply With Quote
  #9  
Old 07-31-2016, 07:52 PM
Guessed's Avatar
Guessed Guessed is offline Word 2010 the requested member of the collection does not exist Windows 10 Word 2010 the requested member of the collection does not exist Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,304
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #10  
Old 08-01-2016, 05:36 AM
ninaiva ninaiva is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
ninaiva is on a distinguished road
Default

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
Attached Files
File Type: docx DropDown.docx (12.3 KB, 8 views)
Reply With Quote
  #11  
Old 08-01-2016, 03:13 PM
Guessed's Avatar
Guessed Guessed is offline Word 2010 the requested member of the collection does not exist Windows 10 Word 2010 the requested member of the collection does not exist Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,304
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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).
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #12  
Old 08-01-2016, 11:07 PM
ninaiva ninaiva is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
ninaiva is on a distinguished road
Default

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?
Reply With Quote
  #13  
Old 08-01-2016, 11:42 PM
Guessed's Avatar
Guessed Guessed is offline Word 2010 the requested member of the collection does not exist Windows 10 Word 2010 the requested member of the collection does not exist Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,304
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #14  
Old 08-02-2016, 12:49 AM
ninaiva ninaiva is offline Word 2010 the requested member of the collection does not exist Windows 7 64bit Word 2010 the requested member of the collection does not exist Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
ninaiva is on a distinguished road
Default

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?
Reply With Quote
  #15  
Old 08-02-2016, 01:03 AM
Guessed's Avatar
Guessed Guessed is offline Word 2010 the requested member of the collection does not exist Windows 10 Word 2010 the requested member of the collection does not exist Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,304
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


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
Word 2010 the requested member of the collection does not exist Pack and Go: Does it Exist in PPT 2010? tatihulot PowerPoint 1 11-16-2010 08:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft