locked
Listbox not showing data after save RRS feed

  • Question

  • Hi

    I have a document that contains a UserForm that does various bits like adding addresses, services and monitory values. When the user submits the data, the services and totals are all placed on the form by means of updating the list box. This works fine.

    However, when I save the document and reopen it, the contents of the list box are blank and appear not to be saved. This might make more sense if I show you the code so I will make it available to anyone who needs it. I can add it to a text file and then email to whoever needs it. I can't see how to attach a file on this post. I won't post it in here as it is nearly 300 lines.

    Anyone any ideas?

    Toni 


    Toni Chaffin aka Talisa



    • Edited by TSRC Monday, January 21, 2013 10:01 AM Update Text
    Monday, January 21, 2013 9:49 AM

Answers

  • You have to create each of the document variables. This is a one-time action. You could create a macro for this:

    Sub CreateVars()
        ActiveDocument.Variables.Add "var_src_txt_1p_afa", " "
        ActiveDocument.Variables.Add "var_src_txt_1p_afa_charge", "0"
        ...
    End Sub

    In the UserForm_Initialize procedure, change

        Format(src_txt_1p_afa_charge.Value, "£#.00") = ActiveDocument.Variables("var_src_txt_1p_afa_charge")

    to

        src_txt_1p_afa_charge.Value = Format(ActiveDocument.Variables("var_src_txt_1p_afa_charge"), "£#.00")


    Regards, Hans Vogelaar

    • Marked as answer by TSRC Monday, January 21, 2013 2:01 PM
    Monday, January 21, 2013 1:07 PM

All replies

  • The data in userform controls are not stored with the userform. You have to populate the controls each time the userform is loaded; you can use the UserForm_Initialize event for this.

    If you want to make the code available: unlike most discussion forums, those from Microsoft don't let you attach a file to a post. Instead, you can upload a file to your Windows Live SkyDrive (https://skydrive.live.com) or to a site such as DropBox (http://www.dropbox.com) or FileDropper (http://www.filedropper.com). Share the file, and get a link to the uploaded file (all these sites let you get a link) and post the link in a reply.


    Regards, Hans Vogelaar

    Monday, January 21, 2013 10:08 AM
  • I kind of understand what you mean. Once the user has created a new document based on the template (dotm) then when they open the saved document (docx) the UserForm does not load anyway.

    I have uploaded the document (dotm) to my SkyDrive and enabled Edit etc. If you could have a look for me that would be brilliant. Link should be https://skydrive.live.com/redir?resid=C59474CE7FF81DD7!2818&authkey=!AFWPyyDMLHM-Wmw

    Hope it works

    T

     

    Toni Chaffin aka Talisa

    Monday, January 21, 2013 10:26 AM
  • I am not sure that I explained myself in my reply properly. Once the user has completed the UserForm and submitted the data to the document, they have no way of then reopening the form and that is my intention/by design. They can then save the completed document in the usual manner. If they then open the newly saved document I want the figures (ListBox Values) there and still no access to the UserForm.

    Bottom line, once submitted the userform to disappear for ever until they create a new document based on the template.

    Is that any better?

    Toni


    Toni Chaffin aka Talisa

    Monday, January 21, 2013 10:57 AM
  • You could store the values in document variables, and use DocVariable fields to display the values in the document

    You can download a sample template (ziopped) that illustrates this from http://eileenslounge.com/viewtopic.php?p=15936

    If you create a new document from the template, a userform will be displayed. The userform can be displayed later on by pressing Alt+Shift+V.


    Regards, Hans Vogelaar

    Monday, January 21, 2013 11:00 AM
  • I did a couple of boxes based on your test.zip and I get an error when double clicking on the template to open a new document

    Run Time Error 5825

    Object has been deleted

    The code I added was;

    Private Sub UserForm_Initialize()
        src_txt_1p_date.Value = Format(Date, "dd") & Mid("thstndrdth", (DatePart("d", Date) Mod 10) * 2 + 1, 2) & " " & Format(Date, "mmmm yyyy")
        src_txt_1p_afa.Caption = ActiveDocument.Variables("var_src_txt_1p_afa")
        Format(src_txt_1p_afa_charge.Value, "£#.00") = ActiveDocument.Variables("var_src_txt_1p_afa_charge")
    End Sub

    and

        If src_txt_1p_afa.Value = True Then
             ActiveDocument.ListBox1.AddItem src_txt_1p_afa.Caption
             ActiveDocument.ListBox2.AddItem Format(src_txt_1p_afa_charge.Value, "£#.00")
             ActiveDocument.Variables("var_src_txt_1p_afa") = src_txt_1p_afa.Caption
             ActiveDocument.Variables("var_src_txt_1p_afa_charge") = Format(src_txt_1p_afa_charge.Value, "£#.00")
        End If

    What have I done wrong here?

    T


    Toni Chaffin aka Talisa

    Monday, January 21, 2013 11:40 AM
  • You have to create each of the document variables. This is a one-time action. You could create a macro for this:

    Sub CreateVars()
        ActiveDocument.Variables.Add "var_src_txt_1p_afa", " "
        ActiveDocument.Variables.Add "var_src_txt_1p_afa_charge", "0"
        ...
    End Sub

    In the UserForm_Initialize procedure, change

        Format(src_txt_1p_afa_charge.Value, "£#.00") = ActiveDocument.Variables("var_src_txt_1p_afa_charge")

    to

        src_txt_1p_afa_charge.Value = Format(ActiveDocument.Variables("var_src_txt_1p_afa_charge"), "£#.00")


    Regards, Hans Vogelaar

    • Marked as answer by TSRC Monday, January 21, 2013 2:01 PM
    Monday, January 21, 2013 1:07 PM
  • Thank you so much for this. Problem solved!

    Next Question about to be posted in new thread ;)

    T


    Toni Chaffin aka Talisa

    Monday, January 21, 2013 2:22 PM