Consulting

Results 1 to 19 of 19

Thread: Interactive combobox form help needed

  1. #1
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location

    Interactive combobox form help needed

    Hello,

    It's my first time on this forum so sorry for any mistakes that i might make while posting.

    Additionally, I'm pretty new to VBA so most of my coding is from patching together parts of projects i found in other posts and trying to make them work for me.


    I'm making an interactive word form that will show different templates of the forms my organization is currently using depending which checkbox(Activex Checkbox) is checked or option chosen from a drop down menu (Activex ComboBox).

    The templates are inserted in tables that VBA hides when the checkbox is not active.

    I have this piece of code that does it for the combobox:

    Private Sub ComboBox1_Change()
    Select Case ComboBox1

    Case "Schools LEA REF"

    With ActiveDocument.Tables(4).Rows(1)
    If .HeightRule = wdRowHeightExactly Then
    .HeightRule = wdRowHeightAuto

    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Else
    .HeightRule = wdRowHeightExactly
    .Height = ".5"
    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    End If
    End With


    Case "Foster Carers"

    With ActiveDocument.Tables(5).Rows(1)
    If .HeightRule = wdRowHeightExactly Then
    .HeightRule = wdRowHeightAuto

    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Else
    .HeightRule = wdRowHeightExactly
    .Height = ".5"
    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    End If
    End With


    lbl_Exit:

    Case Else

    End Select
    End Sub

    There's more options there but they're just a repetition of the same process.

    My question is, is it possible, to make that code hide the form that is showing after it was selected if another option was selected from combobox (so essentialy to only show the form that has been selected), at the moment if i choose an option and then choose a different one it moves the previously selected form lower and pops the new one above it.

    I can attach my current doument here if necessary.

    If this is not possible, another question would be if it's possible to make Ativex Checkbox invisible in word, I've checked the properties and it doesn't have the visibe property to change there (despite having it in excel), is there a trick to it in word?

    I would greatly appreciate any help. Thank you.

  2. #2
    I would recommend that you don't put activeX controls on your document or have hidden/no hidden items on the document.

    Make the selections on a VBA userform and write the selected items to the document, using either autotexts to store the appropriate tables in a range (or ranges), or simply create the tables required in the document from the selections. By using ranges you can change the content of the range by writing a different (or no) value to it.

    You could use multiple column combo boxes on your userform (set the widths of the extra columns to 0 to hide them and reduce the size of the first column by about 4 points to hide the scroll bars). The extra column can be used to store the autotext name used for the associated entry.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Thank you for the reply, unfortunately i don't think i can do that, the document I'm creating is holding 8 other form templates, i have literally no idea how to make that work with userforms as i have never used them before and by looking at the tutorials it seems like they just populate whatever you designed in the VBA form.

    Don't know how would i change the fields within the vba userform depending on which form a user wants to choose? how would then those different fields populate different infomation in word and not show te ones that are not needed.

  4. #4
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Unless there's a way to actually just use userform to select the different templates that i want to display, if the userform can hide all of them and only show in the document the selected one.

  5. #5
    A userform merely provides a set of instructions to create or populate a document with the information requested from the userform. Isn't that what you are doing with your 8 form templates? From what you said, you want to select some information from the combobox or from check boxes and insert them to create a document that reflects the end result based on those selections. So, for example if you selected "Foster Carers" from the userform either by checking a check box or selecting from a combo box, then the userform would insert the table that is Table 5.

    Forget all about hiding stuff. Instead only include the stuff that is to be displayed.

    However post your document and let us see what it is that you have done and we may be able to advise further.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Sorry i was overthinking it and thought you advised me to create the full forms in userform. please find the current form attached.
    Attached Files Attached Files

  7. #7
    You were certainly overthinking when you created that form .

    Sometimes I find it stimulating to modify such forms, and this one was such a challenge. It was however relatively simple to extract your tables, once I found how you hid them, make them autotext entries and create a userform to call them based on the logic in your document.

    As there are 8 such tables hidden in your form and there are nine possible options, I assume that the first item in your combo box is a prompt. In which case, the attached should be closer to what you had in mind.

    Create a new document from the template to open the userform and there's a button on the Home tab should you wish to change the options (before you have completed the tables).

    I would recommend that you replace the legacy form fields with content controls, as they are much faster to process. There are tools on my web site to help you do that http://www.gmayor.com/insert_content_control_addin.htm however my altrusim only stretches so far, so I will leave that to you
    Attached Files Attached Files
    Last edited by gmayor; 01-30-2019 at 04:47 AM. Reason: Code error.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Thanks Graham and as much as i appreciate the work you've put in to create your version I cannot use it, mostly because i don't entirely understand how it works and if something falls apart while i want to edit it i won't be able to fix it, as much as my form is probably not the best option available i'd prefer to finish it the way it's designed, this way if i ever need to pass it on i can explain how everything works. We don't have many IT oriented people around my offie as it's a small team and as i said i am new to VBA myself.


    Therefore i'd prefer to go back tomy original question, is there a way to either make activex chackbox invisible or make each of the forms from my document disappear if they're selected, the same way that the checkboxes currently work.


    Edit:

    I started using your form as a template to make my own, hopefully I'll have enough wit to make it work with only checkboxes for all 8 options and bookmarks with ranges, see what happens, thanks
    Last edited by Yoru; 01-30-2019 at 09:26 AM. Reason: Overthinking

  9. #9
    The template I attached works with one of the choices using radio buttons (to make them exclusive) plus one of the choices from the combo box (which appears to be what you intended originally) and autotexts are placed one after the other in an empty document. If you want to insert more than one of the choices from either, you will need to use check boxes and process them in the order they are to appear. If there is to be more text in the document, then pre-place the bookmarks and use the function to write to them. There are more functions on my web site that would help with such a document. See also http://www.gmayor.com/Userform.htm
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  10. #10
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Hey Graham, thank you, i have been browsing your website yesterday as i want to create it as you suggested in a userform (it looks cleaner and less likely to be messed up by someone fidling with the document.

    Originally I intended to have 4 checkboxes - first 3 showing different forms ad last one (Direct Payment) showing 5 other checkboxes for the options from the combobox - however, that wouldn't work as the activex checkbox doesn't store in a table that could be hidden, the same the other forms worked, that's why i used combobox instead.

    The way i want to do it now is to put all 8 options in the pop up userform - just separating the direct payments with a text box - and each of the checkboxes will be showing relevant form. I'm guessing i have to store each form separately in either a bookmark or some other way and then call for them using checkboxes and confirmation button.

    I'll try following some online tutorials and get some of the function from your document. Sorry for not explaining it better at the begining.
    Last edited by Yoru; 01-31-2019 at 03:43 AM.

  11. #11
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    I have managed to make it work the way i want it to but if i could also ask, how did you make autotext entries get attached to the document itself? I've read something about templates but from my reading it sounds that you then need two documents, one form and one template the document will refer to for autotext entries, yet i can see in the document you've sent that the autotext blocks are saved within the document itself, how could i do the same thing?

    Edit:
    Changed the document to .dotm and then re-saved the autotext entries to it, looks like it works fine now on other PCs, the only thing that is left now is to figure out why does it not launch the autonew macro on start on other PCs.
    Last edited by Yoru; 02-01-2019 at 09:09 AM.

  12. #12
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Unless I've missed a post, Graham didn't send a document file, he sent a template file (.dotm extension). The template file stores the buildingblocks and new documents are created from the template.
    Greg

    Visit my website: http://gregmaxey.com

  13. #13
    The document I posted is a template. You create new documents from a template, as Greg posted, and the autonew macro fires.
    If you want it to fire when you open the document, then you would need an autoopen macro, but that would require the availability of the template. If you supply a document without the template, the document cannot 'see' the template and so the autotexts and the ribbon button are not available to it. You cannot store autotexts in a document. Whoever is creating the documents therefore needs the template.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  14. #14
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Hello both, yes I've figured that out eventually on friday as i posted in the edit to my post but thank you for clarifying I'm sending over the template with AutoNew macro as i only want it to work on a creation of a new file, however, even though it works fine on my PC it doesn't seem to be starting on others, if you run the macro from within the editor, it works, it just doesn't start automatically.
    Last edited by Yoru; 02-04-2019 at 08:29 AM.

  15. #15
    Assuming the macro is actually in the template itself then are you sure that it isn't being blocked by users' macro security?
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  16. #16
    VBAX Regular
    Joined
    Jan 2019
    Posts
    9
    Location
    Figured out what was wrong, the template apparently has to be saved to your PC instead of for example runing from within an email. Once saved locally it works, unfortunately everthing backired, our document management system doesn't accept .dotm extensions and using sharepoint instead and saving the template there doesn't open a new file based on the template but the actual template.


    Edit:

    Found a workaround for the sharepoint issue, you need to create a link to the document that will force the file to be downloaded, works fine afterwards.

    Thanks for all your help othrwise, was interesting to learn new things.
    Last edited by Yoru; 02-04-2019 at 08:27 AM.

  17. #17
    I'm just reading through this thread and I have a question regarding user forms.

    I want the user to be able to use the same user form more than once in the same document until the user is done entering information.

    I have a form that user enter Name, date, etc. - then I would like to add "Add Another"... when user has entered all the information, the user would then click "Done" and the memorandum would populate with the data entered.

    Is this possible?

  18. #18
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    You could add the data sets to a hidden listbox (or you could use an array). Then use the listbox (or array) to populate the document:

    Private Sub cmdAdd_Click()
      With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = TextBox1.Text: TextBox1.Text = vbNullString
        .List(.ListCount - 1, 1) = TextBox2.Text: TextBox2.Text = vbNullString
        .List(.ListCount - 1, 2) = TextBox3.Text: TextBox3.Text = vbNullString
      End With
    End Sub
    
    Private Sub cmdAddDone_Click()
    Dim lngIndex As Long
      With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = TextBox1.Text
        .List(.ListCount - 1, 1) = TextBox2.Text
        .List(.ListCount - 1, 2) = TextBox3.Text
      End With
      For lngIndex = 0 To ListBox1.ListCount - 1
        MsgBox ListBox1.List(lngIndex, 0) & " " & ListBox1.List(lngIndex, 1) & " " & ListBox1.List(lngIndex, 2)
      Next lngIndex
      Unload Me
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  19. #19

    Sample attached

    The macro enabled file is what was given to me - As stated before I want to be able to add more than one person before clicking done. The user form needs to repeat itself until all entries are completed.

    I tried manipulating code and inserting your code to no avail.

    Hoping you can provide some additional instruction.




    -----------------------

    Quote Originally Posted by gmaxey View Post
    You could add the data sets to a hidden listbox (or you could use an array). Then use the listbox (or array) to populate the document:

    Private Sub cmdAdd_Click()
      With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = TextBox1.Text: TextBox1.Text = vbNullString
        .List(.ListCount - 1, 1) = TextBox2.Text: TextBox2.Text = vbNullString
        .List(.ListCount - 1, 2) = TextBox3.Text: TextBox3.Text = vbNullString
      End With
    End Sub
    
    Private Sub cmdAddDone_Click()
    Dim lngIndex As Long
      With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = TextBox1.Text
        .List(.ListCount - 1, 1) = TextBox2.Text
        .List(.ListCount - 1, 2) = TextBox3.Text
      End With
      For lngIndex = 0 To ListBox1.ListCount - 1
        MsgBox ListBox1.List(lngIndex, 0) & " " & ListBox1.List(lngIndex, 1) & " " & ListBox1.List(lngIndex, 2)
      Next lngIndex
      Unload Me
    End Sub
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •