Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-08-2020, 01:24 AM
edeszti edeszti is offline How to create multilevel drop-down list in word Windows 10 How to create multilevel drop-down list in word Office 2016
Novice
How to create multilevel drop-down list in word
 
Join Date: Mar 2020
Posts: 3
edeszti is on a distinguished road
Default How to create multilevel drop-down list in word

Hi,

I want to add multilevel (3-level) dependent drop-down lists to a word document which imports the data from excel cells.
I used the logic of this excel example:
VBA: Multilevel dependent drop-down in User Form - PK: An Excel Expert




There are three content controls in the word document: Category, Sub Category and Item. The content of the Sub Category drop-down list depends on the selected value of the Category drop-down list, and the content of the Item drop-down list depends on the selected value of the Sub Category drop-down list.
My problem is that the macro doesn't update the Sub Category and the Item drop-down lists. I use Document_ContentControlOnExit event.


I attach the .docm and the excel.
Code:
Code:
Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False

Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long

StrWkBkNm = "c:\.....\teszt_lista.xlsx"
StrWkShtNm = "Sheet1"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
Dim cc1 As ContentControl, cc2 As ContentControl, cc3 As ContentControl

Set cc1 = ActiveDocument.SelectContentControlsByTitle("Category").Item(1)
Set cc2 = ActiveDocument.SelectContentControlsByTitle("SubCategory").Item(1)
Set cc3 = ActiveDocument.SelectContentControlsByTitle("Item").Item(1)

xlApp.Visible = False
Set xlWkBk = xlApp.Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
LRow = xlWkBk.Worksheets(StrWkShtNm).Cells(.Rows.Count, 1).End(xlUp).Row

With CCtrl
    Select Case .Title
        Case "Category"
            cc2.DropdownListEntries.Clear
            For i = 2 To LRow
                If Trim(.Range("A" & i)) = "Sub Category" Then
                    If Trim(.Range("C" & i)) = .Range.Text Then
                        cc2.DropdownListEntries.Add Text:=Trim(.Range("B" & i))
                    End If
                End If
            Next
        Case "SubCategory"
            cc3.DropdownListEntries.Clear
            For i = 2 To LRow
                If Trim(.Range("A" & i)) = "Item" Then
                    If Trim(.Range("C" & i)) = .Range.Text Then
                        cc3.DropdownListEntries.Add Text:=Trim(.Range("B" & i))
                    End If
                End If
            Next
    End Select
End With
xlWkBk.Close False
xlApp.Quit
Application.ScreenUpdating = True
 End Sub
Thanks a lot in advance
Attached Files
File Type: docm Category_level3_CCtr_v2.docm (28.7 KB, 12 views)
File Type: xlsx teszt_lista.xlsx (12.2 KB, 11 views)
Reply With Quote
  #2  
Old 06-08-2020, 02:51 AM
macropod's Avatar
macropod macropod is offline How to create multilevel drop-down list in word Windows 7 64bit How to create multilevel drop-down list in word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Quote:
Originally Posted by edeszti View Post
I doubt it. Your code looks rather more like something you've cobbled together from various posts here - mine, especially.

For Content Control population from Excel, see:
https://www.msofficeforums.com/word-...drop-down.html
https://www.msofficeforums.com/word-...wns-excel.html

And, for Dependent Dropdown Content Controls, see:
https://www.msofficeforums.com/word-...html#post77762
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-08-2020, 02:57 AM
macropod's Avatar
macropod macropod is offline How to create multilevel drop-down list in word Windows 7 64bit How to create multilevel drop-down list in word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Cross-posted at: vba - How to create multilevel drop-down lists in word - Stack Overflow
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 06-15-2020, 04:48 AM
edeszti edeszti is offline How to create multilevel drop-down list in word Windows 10 How to create multilevel drop-down list in word Office 2016
Novice
How to create multilevel drop-down list in word
 
Join Date: Mar 2020
Posts: 3
edeszti is on a distinguished road
Default

Hi,

Thanks for the links.

I'm a beginner in writing macros and in posting on forums. So I'm sorry, if my post wasn't appropriate.
I read many useful posts related to this topic (more of your posts), but I didn't find a solution to my problem, I only found half-way solutions. So I tried to put these solutions together.
I managed to solve the problem with legacy from fields, but the document containing these drop-down lists should be editable, so content controls would be better.


Could you have any suggestions on how to modify the code?


Thanks in advance.
Reply With Quote
  #5  
Old 06-16-2020, 01:23 AM
macropod's Avatar
macropod macropod is offline How to create multilevel drop-down list in word Windows 7 64bit How to create multilevel drop-down list in word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Quote:
Originally Posted by edeszti View Post
Could you have any suggestions on how to modify the code?
Since you haven't supplied either the document or the code, I can only suggest you have another look at the code in the links I provided; they likely already have everything you need and would require far less effort to implement than modifying code designed for something entirely different.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert multilevel bullet list to multilevel numbered list scadaman29325 Word 2 03-27-2020 03:27 PM
Create Drop Down list to change cell color bksmith Excel 2 08-21-2017 02:37 AM
create a drop down list jeffk Excel 3 09-10-2015 07:38 AM
How to create a drop down list with sub categories Gooford Word 0 06-18-2014 03:46 AM
Create Drop Down List Box hbradshaw Word VBA 0 09-27-2010 06:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:55 PM.


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