#31
|
|||
|
|||
StrOut syntax?
Since StrOut is comma delimited, what is the syntax needed to include a comma in my list item such as the below?
- bananas and grapes - watermelon - oranges, apples, and pears Code:
StrOut = "oranges, apples, and pears" Last edited by kevinbradley57; 07-08-2020 at 05:24 PM. Reason: forgot code |
#32
|
||||
|
||||
kevin
The examples used a comma delimiter but you could use any (non-used) character you wanted as the separator. If you wanted to change to something else you could eg Code:
StrOut = "bananas|watermelon|oranges, apples, and pears" For i = 0 To UBound(Split(StrOut, "|"))
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#33
|
|||
|
|||
You could pass it to a funtion:
Code:
Sub TestATS() Dim arrTest() As String arrTest = Split("A", "|") MsgBox fcnArrayToString(arrTest) arrTest = Split("A|B", "|") MsgBox fcnArrayToString(arrTest) arrTest = Split("A|B|C|D", "|") MsgBox fcnArrayToString(arrTest) MsgBox fcnArrayToString(arrTest, True) End Sub Public Function fcnArrayToString(varIn As Variant, Optional bOxford As Boolean = False) As String Dim strTemp As String Dim lngIndex As Long Select Case UBound(varIn) Case 0: fcnArrayToString = varIn(0) Case 1: fcnArrayToString = varIn(0) & " and " & varIn(1) Case Else fcnArrayToString = varIn(0) lngIndex = 1 Do While lngIndex < UBound(varIn) fcnArrayToString = fcnArrayToString & ", " & varIn(lngIndex) lngIndex = lngIndex + 1 Loop If bOxford Then fcnArrayToString = fcnArrayToString & ", and " & varIn(lngIndex) Else fcnArrayToString = fcnArrayToString & " and " & varIn(lngIndex) End If End Select lbl_Exit: Exit Function End Function |
#34
|
|||
|
|||
Hi again Macropod and others,
Thanks for all the feedback. I managed to get things working. I do need help with something else. Basically, I have parent content control "Spouse" and "A&A" as child. if spouse is Yes, then A&A has multiple selection options, but of the different options, IF either "spouse is able and available" or "spouse is able and partially available" then I need that value outputted somewhere in the document. Currently, I'm doing that in the form of dropdown. I would prefer a neater solution... preferable as text output when one of those 2 is selected in A&A. Would appreciate if you can reply with the code needed for this. Please post complete code so that I can copy/paste and replace the entire thing. below is existing code in it's entirety: Code:
Option Explicit Dim StrOption As String Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl) With CCtrl Select Case .Title Case "Spouse", "A&A", "Minor": StrOption = .Range.Text End Select End With End Sub Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, StrOut As String With CCtrl ' /// SPOUSE - ABLE & AVAILABLE If .Title = "Spouse" Then If StrOption = .Range.Text Then Exit Sub Select Case .Range.Text Case "Yes" StrOut = "Spouse is able and available,Spouse is able and partially available,Spouse is able but not available,Spouse is available but not able,Spouse is IHSS Recipient,Other" Case "No" StrOut = "N/A" Case "Recipient is not married" StrOut = "N/A" Case Else .Type = wdContentControlText .Appearance = wdContentControlTags .Range.Text = "" .Type = wdContentControlDropdownList .Appearance = wdContentControlTags End Select With ActiveDocument.SelectContentControlsByTitle("A&A")(1) .DropdownListEntries.Clear For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next .Type = wdContentControlText .Appearance = wdContentControlTags .Range.Text = "" .Type = wdContentControlDropdownList .Appearance = wdContentControlTags End With End If ' /// SPOUSE - ALTERNATE RESOURCE If .Title = "A&A" Then If StrOption = .Range.Text Then Exit Sub Select Case .Range.Text Case "Spouse is able and available" StrOut = "Spouse is able and available." Case "Spouse is able and partially available" StrOut = "Spouse is able and partially available." Case Else .Type = wdContentControlText .Appearance = wdContentControlTags .Range.Text = "" .Type = wdContentControlDropdownList .Appearance = wdContentControlTags End Select With ActiveDocument.SelectContentControlsByTitle("AltResource-Spouse")(1) .DropdownListEntries.Clear For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next .Type = wdContentControlText .Appearance = wdContentControlTags .Range.Text = "" .Type = wdContentControlDropdownList .Appearance = wdContentControlTags End With End If ' /// MINOR If .Title = "Minor" Then If StrOption = .Range.Text Then Exit Sub Select Case .Range.Text Case "Yes", "Recipient is not a minor" StrOut = "N/A" Case "Recipient is not a minor" StrOut = "N/A" Case "No" StrOut = "Parent is prevented from full-time employment due to child's needs,Recipient is under the care of a Legal Guardian" Case Else .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End Select With ActiveDocument.SelectContentControlsByTitle("MinorExp")(1) .DropdownListEntries.Clear For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End With End If End With Application.ScreenUpdating = True End Sub |
#35
|
|||
|
|||
Content Control as combobox instead of dropbox
Hi
I need help with a small issue in my code. Basically, the code has parent dropbox "Lives with", and if user selects "recipient lives alone" then it currently limits child dropbox (Occupants) to "N/A" but if user selects "Recipient shares home" I would like for it to output a value, but for that box to be a combo box and not a dropbox, meaning that user can edit the output values instead of locked field. I would appreciate all the help I can get on this. Thanks! Quote:
|
#36
|
|||
|
|||
Would it be possible to utilize an OR statement if each value in a dependent drop down is the same?
In my project I need to have multiple combo-boxes that utilize identical information. Although I could expand the code that has been provided in this thread (and will if I need to) I think that the more I copy and modify the more likely it is that I'll break the code. To help characterize what I'm talking about, I've modified another users code to reflect two different Spouse cases with the same StrOut variables. As you can imagine, if I'm copying / modifying 40 of these "Spouse" cases that code would be both long and probably broken. I realize I'm already pushing my luck here today as Macropod helped me a lot earlier, so I completely understand if you all don't have time for another question. However, if you do and wouldn't mind explaining why an OR statement can't be used or how to put it in I'd very appreciative. Currently my biggest "what do I do now" concern is that even if I put an OR clause at the top of the code, I have no idea how to tell Word that the resulting drop-down should go to a specific content control. Code:
With CCtrl If .Title = "Spouse1" OR "Spouse2" Then If StrOption.... Code:
Option Explicit Dim StrOption As String Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl) With CCtrl Select Case .Title Case "Spouse1", "Spouse2": StrOption = .Range.Text End Select End With End Sub Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, StrOut As String With CCtrl If .Title = "Spouse1" Then If StrOption = .Range.Text Then Exit Sub Select Case .Range.Text Case "Yes" StrOut = "Spouse is able and available,Spouse is able and partially available,Spouse is able but not available,Spouse is available but not able,Spouse is IHSS Recipient,Other" Case "No" StrOut = "N/A" Case Else .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End Select With ActiveDocument.SelectContentControlsByTitle("A&A")(1) .DropdownListEntries.Clear For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End With End If If .Title = "Spouse2" Then If StrOption = .Range.Text Then Exit Sub Select Case .Range.Text Case "Yes" StrOut = "Spouse is able and available,Spouse is able and partially available,Spouse is able but not available,Spouse is available but not able,Spouse is IHSS Recipient,Other" Case "No" StrOut = "N/A" Case Else .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End Select With ActiveDocument.SelectContentControlsByTitle("A&A")(1) .DropdownListEntries.Clear For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList End With End If End With Application.ScreenUpdating = True End Sub |
#37
|
||||
|
||||
Presumably, you want something like:
Code:
Option Explicit Dim StrOption As String Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl) With CCtrl Select Case .Title Case "Spouse1", "Spouse2": StrOption = .Range.Text End Select End With End Sub Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, StrOut As String With CCtrl Select Case .Title Case "Spouse1", "Spouse2" With CCtrl Select Case .Range.Text Case StrOption: Exit Sub Case "Yes" StrOut = "Spouse is able and available," & _ "Spouse is able and partially available," & _ "Spouse is able but not available," & _ "Spouse is available but not able," & _ "Spouse is IHSS Recipient," & _ "Other" Case "No": StrOut = "N/A" End Select End With With ActiveDocument.SelectContentControlsByTitle("A&A")(1) .DropdownListEntries.Clear .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList For i = 0 To UBound(Split(StrOut, ",")) .DropdownListEntries.Add Split(StrOut, ",")(i) Next End With End Select End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#38
|
|||
|
|||
Hi Macropod,
If I'm doing it right, your most recent code works well for the first dependent drop down box but when I add in other dependent content controls they aren't registering. I think it's highly like that it's user error, but if it's not I've listed my assumption of what's happening below. My assumption is that I'm not telling VBA which content controls should be dependent on the value of another content control beyond the first instance. To fix that I tried directing the new code by adding the following section to your code and also simply adding the additional subchapters to the first section (i.e. "R1SubChapter", "R2SubChapter"). Unfortunately in both instance I failed to properly tell VBA that R1Chapter's inputs should go with R1SubChapter's content control, while R2Chapter's inputs should go with R2SubChapter's content control. At this point I'm thinking about tying all of the chapters/subchapters into a variable so that when the time comes there is only one spot to update the reference information. That being said this is only a guess, and I'm not entirely sure that this is even possible. Code:
With ActiveDocument.SelectContentControlsByTitle("R1SubChapter")(1) .DropdownListEntries.Clear .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList For i = 0 To UBound(Split(StrOut, "|")) .DropdownListEntries.Add Split(StrOut, "|")(i) Next End With With ActiveDocument.SelectContentControlsByTitle("R2SubChapter")(1) .DropdownListEntries.Clear .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList For i = 0 To UBound(Split(StrOut, "|")) .DropdownListEntries.Add Split(StrOut, "|")(i) Next End With I'm not sure if you've ever come across this type of situation but the problem we're trying to solve is that users will be asked to cite a chapter and a subchapter that supports why an action is necessary or unnecessary. As each request for that determination utilizes the same chapters/subchapters I'm hopeful that the long list of Chapters / Subchapters would only need to be entered once. Hopefully with this additional context I've at least made it a bit more clear of what my intentions are. In short though my question my primary questions is, How can I modify the below code so that the a series of content controls can utilize the same chapters and subchapters without having to have 20 identical chapters/subchapters listed? For example, a user selects drop down list [A] and gets options "1,2,3,4". After selecting 1, a dependent drop-down list [a] displays options "x,y,z". That same user can then select drop down list [B] and also gets options "1,2,3,4." After selecting 1 again, dependent drop-down list [b] also displays options "x,y,z." I'm extremely new at this, and have been out of my depth for some time now. So I apologize to anyone reading this who found anything in this post absurd, confusing, or ignorant. Nevertheless, I truly appreciate everything Macropod and others have done for me and the community. Thank you all very much for your time and patience. Also as a better reference to what the code looks like I've included a modified version below: Code:
Option Explicit Dim StrOption As String Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl) With CCtrl Select Case .Title Case "R1Chapter", "R2Chapter", "R3Chapter": StrOption = .Range.Text End Select End With End Sub Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean) Application.ScreenUpdating = False Dim i As Long, StrOut As String With CCtrl Select Case .Title Case "R1Chapter", "R2Chapter", "R3Chapter" With CCtrl Select Case .Range.Text Case StrOption: Exit Sub Case "Chapter 1" StrOut = "SubChapter1| SubChapter2| SubChapter3|SubChapter4" Case "Chapter 2" StrOut = "SubChapterA| SubChapterB| SubChapterC|SubChapterD" End Select End With With ActiveDocument.SelectContentControlsByTitle("R1SubChapter", “R2SubChapter”, “R3Subchapter”)(1) .DropdownListEntries.Clear .Type = wdContentControlText .Range.Text = "" .Type = wdContentControlDropdownList For i = 0 To UBound(Split(StrOut, "|")) .DropdownListEntries.Add Split(StrOut, "|")(i) Next End With End Select End With Application.ScreenUpdating = True End Sub Last edited by Jeff_Reach; 04-08-2022 at 10:32 AM. Reason: Grammar and Inserting Question |
#39
|
|||
|
|||
Membership
I don't have much, but as I keep seeing that the helpful hints and suggestions I'm using are from old Macropod (Paul Edstein) posts I feel compelled to help out. My latest encounter with your past posts was on the Graham Mayor page, and I noticed that they had a donation option. Before I do that would you, Macropod, please confirm that you are still associated with or approve of that site? Thank you very much sir, and hope all is well.
|
#40
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#41
|
|||
|
|||
Response to Message
Yes sir, but unfortunately that's not really what I'm looking for at this time. It's my understanding that a multi-level dependency would follow something similar to:
(Select Box A: 1,2,3 -> [1] -> 1.1,1.2,1.3 -> [1.1] -> 1.1, 1.1.1, 1.1.2, 1.1.3), while I'm looking for something like: (Select Box A: 1,2,3 -> [1] -> 1.1,1.2,1.3 OR Select Box B: 1,2,3 -> [1] -> 1.1, 1.2, 1.3]. I think I understand the issue now, but to meet the organizations timeline I'll proceed with making a lot of duplicate drop down menus. That being said I still very much appreciate your feedback, and hope you're doing well. Also thank you for the information on the other VBA authors as I think those individuals you listed comprise the majority of the individual's work who I've continually utilized as a reference. Cheers! |
#42
|
|||
|
|||
Updates
For anyone out that may use this thread as a reference in the future I've listed a few notes that may save you some time:
|
#43
|
|||
|
|||
I am trying to learn how to create a drop down with dependent text. I need to drop down selections to be A, B, or C, where A inserts a specific number value, B inserts a different number value, and C inserts a different number value.
I've been scrolling through the forum and seems to be over my head or I'm missing something. |
#44
|
||||
|
||||
KDW
What you are describing sounds like it fits the solution described here https://www.msofficeforums.com/147961-post4.html
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#45
|
|||
|
|||
Multi Dependent Lists with Same Options
Quote:
I am attempting to create an audit tool. The example B you provided has been very helpful. However, how would I be able to repeat these dependent dropdowns throughout the same document. For example, when auditing a health assessment, there are 11 different functional items that may have to be documented for different incorrect subsections which all have the same options list. Any advice you could provide would be GREATLY appreciated |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dropdown list, Macro | shield5 | Excel Programming | 7 | 10-27-2013 01:51 AM |
VBA: How to place dropdown list next to text | YigalB | Word VBA | 0 | 08-11-2013 01:48 PM |
block selection in dropdown list | Intruder | Excel | 2 | 01-10-2013 10:20 AM |
dropdown list for documents | r_p_t_0 | Word | 2 | 12-18-2012 05:55 AM |
Dropdown list of email addresses | J Partridge | Outlook | 1 | 01-13-2011 06:37 AM |