Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 36

Thread: Load Form in Personal Forms Library With VBA

  1. #1
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location

    Load Form in Personal Forms Library With VBA

    The form of my contact has a number of extra fields that I use for insert information regarding the contacts.

    I have Yes/No fields, Date/Time fields, and Combobox fields.

    In the past, I have the macro codes that if I identify a contact in a contact folder or subfolders, or open a contact, I create a email to that contact.

    Is there a macro form that identifies the name of the field in the form of the contacts, and when I identify or open a contact, I run the macro and it adds to the field the words I want to use? This way if I am going thru 500 contacts and each one needs the same words or date in a field, I can add to them much much faster.

    This would be another upgrade that would be helpful!! Thanks so much!!!

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Rez bump
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    I have the code that does what I asked....and what I need if possible, is a macro code that when I highlight a contact, when run the macro, it clicks on a specific commandbutton in the contact......the contact form is in my Personal Forms Library and the name of the Contact form is "Contact Form 1"

    Does anyone have a code this please?

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Ims, Can you share the solution with us, please.

    You should be able to use something like:
    Load  [LibraryName].Forms("Contact Form 1")
    With Forms("Contact Form 1")
    .[Required Input Control Name].Value = Selection
    .CommandButton1_Click
    End With
    Forms("Contact Form 1").Unload
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    So add the words Personal Forms Library to the space LibraryName??.....and put a Private Sub [name] () and End Sub at the end? And please tell me what I do in the area you named "Required Input Control Name" ...is there something to do to that area?

    As I am not the expert and I appreciate your quick response

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I tested this in Excel, since I don't have Outlook installed. I had to change the Commandbutton_click Sub t

    Private Sub ClickRemoteCommandButton()
    
    Load ContactForm1
    ContactForm1.CommandButton1_Click
    Unload ContactForm1
    
    End Sub
    I had to remove the Keyword "Private" from in front of the Commandbutton_Click Sub.
    Sub CommandButton1_Click()
    MsgBox ""
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    So the code to try as a macro when I highlight a contact is the following:

    Sub ClickRemoteCommandButton()

    Load ContactForm1
    ContactForm1.CommandButton1_Click
    Unload ContactForm1

    End Sub

  8. #8
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    I ran the code above but the error shows up and the words "Load ContactForm1" show up as yellow color

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You must use the exact names of the Form and the command button as shown in the VBA Properties Window.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    Where is the VBA Properties window please...as I have used the correct names based on the when I open up the design form area....so what else do I find?
    Or should there be something before the Load for purposes of Outlook?

  11. #11
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    In VBA Press Ctrl+R, then Press F4, or you can use the VBA Menu "View" and select Project Explorer and Properties Window
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #12
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    All do respect, this is not clear.....I have put the code you gave me in a Module in the VBA and then as a macro, it shows up under that names of the macros.....my contact form is the following: Contact Form 1 and below it when I go to the list of forms in my Personal Forms Library, that is the name and below it shows up as IPM.Contact.Contact Form 1 and the commandbutton in the contact form is a commandbutton that runs a code from the Script area of the contact form and the commandbutton is Commandbutton4.

    So I am not sure what to adjust when I simply create the macro you gave me, when I highlight a contact not opening it up, and I run the macro, it effectively clicks the Commandbutton4 inside the contact I highlighted....

    So what should I fix in details...if you can write down the full code and any Function to put it, I appreciate it.

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    IMS,

    We are getting out of my field now. I have never really used Outlook and don't have it installed.

    I am going to change the title of this thread to see if we can attract an Outlook Power user for you.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #14
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    Thank you very much.....can you please change the title so it's clear that it's to load a form but just click on a commandbutton in it from the macro?

  15. #15
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You can't "Click" the button without loading the form first. Without the Form, there is no button.

    Can't you just copy the Button's code into the macro you want to click the button?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  16. #16
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    The code in script does is not the same in a macro....I was told there are changes to a script from the contact form to do the same as a macro in the VBA.....so here is the script code that updates the Task of the Contact and all fields in the Task form from the Contact fields relate to them....so if you know what to upgrade as a macro in the VBA, that would be very helpful. Thank you very much.

    Sub btnUpdateTasks_Click
    
        dim ContactName
    
        dim olns
    
        dim MyFolder
    
        dim NumItems
    
        dim myItems
    
        dim TaskCount
    
        Dim Str,Str1,Str2,Str3,Str4,Str5,Str6,Str7,Str8,Str9,Str10,Str11,Str12,Str13,Str14,Str15,Str16,Str17,Str18,Str19
    
        Dim StartDate
    
        Dim DueDate
    
        Dim Recipients
    
        
    
        TaskCount = 0
    
        Str = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox7").Value
    
        Str1 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox8").Value
    
        Str2 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox5").Value
    
        Str3 = Item.GetInspector.ModifiedFormPages("General").Controls("Initial Intro").Value
    
        Str4 = Item.GetInspector.ModifiedFormPages("General").Controls("Intro Date").Value
    
        Str5 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox10").Value
    
        Str6 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox3").Value
    
        Str7 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox2").Value
    
        Str8 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox4").Value
    
        Str9 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox5").Value
    
        Str10 = Item.GetInspector.ModifiedFormPages("General").Controls("ContactTypeComboBox1").Value
    
        Str11 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox4").Value
    
        Str12 = Item.GetInspector.ModifiedFormPages("General").Controls("StatusComboBox1").Value
    
        Str13 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox20").Value
    
        Str14 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox6").Value
    
        Str15 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox22").Value
    
        Str16 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox9").Value
    
        Str17 = Item.GetInspector.ModifiedFormPages("General").Controls("Follow-Up ComboBox1").Value
    
        Str18 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox3").Value
    
        Str19 = Item.GetInspector.ModifiedFormPages("General").Controls("NextStepComboBox1").Value
    
        StartDate = item.getinspector.modifiedformpages("General").controls("OlkDateControl4").value
    
        DueDate = item.getinspector.modifiedformpages("General").controls("OlkDateControl3").value
    
    
     
    
        ContactName = Item.GetInspector.ModifiedFormPages("General").Controls("Fullname").value
    
        Set olns = Item.Application.GetNameSpace("MAPI")
    
        Set MyFolder = olns.GetDefaultFolder(13)
    
        NumItems = MyFolder.Items.Count
    
        Set myItems = myFolder.Items
    
    
        If numitems > 0 then
    
        For I = 1 to NumItems
    
            Set Recipients = myItems(I).Recipients
    
            If Recipients.count > 0 then
    
            
    
            if myitems(I).Links.Item(1).Name = ContactName Then
    
    
                MyItems(I).Display
    
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox20").Value = Str
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox7").Value = Str1
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox24").Value = Str2
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox25").Value = Str3
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox26").Value = Str4
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox23").Value = Str5
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox8").Value = Str6
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox9").Value = Str7
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox10").Value = Str8
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox11").Value = Str9
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox12").Value = Str10
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox13").Value = Str11
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox14").Value = Str12
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox15").Value = Str13
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox16").Value = Str14
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox22").Value = Str15
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox21").Value = Str16
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox17").Value = Str17
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox18").Value = Str18
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox19").Value = Str19
    
                
    
                myItems(I).StartDate = StartDate
    
                myItems(I).DueDate = DueDate
    
    
     
    
                   MyItems(I).Save
    
                MyItems(I).Close(0)
    
                TaskCount = TaskCount + 1
    
            End If
    
            End If
    
        Next
    
        end if
    
    
        Select case TaskCount
    
            Case 0
    
                msgbox "No tasks were updated."
    
            Case 1
    
                msgbox "One task was update successfully."
    
            Case Else
    
                msgbox taskcount & " tasks were updated successfully."
    
        End Select
    
    
    End Sub
    Last edited by SamT; 09-26-2013 at 02:01 PM.

  17. #17
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Sub With Double spaces Removed. Note that some characheters in OP were not readable

    Sub btnUpdateTasks_Click
        dim ContactName
        dim olns
        dim MyFolder
        dim NumItems
        dim myItems
        dim TaskCount
        Dim Str,Str1,Str2,Str3,Str4,Str5,Str6,Str7,Str8,Str9,Str10,Str11,Str12,Str13,Str14,Str15,Str16,Str17,Str18,Str19
        Dim StartDate
        Dim DueDate
        Dim Recipients
        
        TaskCount = 0
        Str = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox7").Value
        Str1 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox8").Value
        Str2 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox5").Value
        Str3 = Item.GetInspector.ModifiedFormPages("General").Controls("Initial Intro").Value
        Str4 = Item.GetInspector.ModifiedFormPages("General").Controls("Intro Date").Value
        Str5 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox10").Value
        Str6 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox3").Value
        Str7 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox2").Value
        Str8 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox4").Value
        Str9 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox5").Value
        Str10 = Item.GetInspector.ModifiedFormPages("General").Controls("ContactTypeComboBox1").Value
        Str11 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox4").Value
        Str12 = Item.GetInspector.ModifiedFormPages("General").Controls("StatusComboBox1").Value
        Str13 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox20").Value
        Str14 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox6").Value
        Str15 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox22").Value
        Str16 = Item.GetInspector.ModifiedFormPages("General").Controls("ComboBox9").Value
        Str17 = Item.GetInspector.ModifiedFormPages("General").Controls("Follow-Up ComboBox1").Value
        Str18 = Item.GetInspector.ModifiedFormPages("General").Controls("TextBox3").Value
        Str19 = Item.GetInspector.ModifiedFormPages("General").Controls("NextStepComboBox1").Value
        StartDate = item.getinspector.modifiedformpages("General").controls("OlkDateControl4").value
        DueDate = item.getinspector.modifiedformpages("General").controls("OlkDateControl3").value
    
     
        ContactName = Item.GetInspector.ModifiedFormPages("General").Controls("Fullname").value
        Set olns = Item.Application.GetNameSpace("MAPI")
        Set MyFolder = olns.GetDefaultFolder(13)
        NumItems = MyFolder.Items.Count
        Set myItems = myFolder.Items
    
        If numitems > 0 then
        For I = 1 to NumItems
            Set Recipients = myItems(I).Recipients
            If Recipients.count > 0 then
            
            if myitems(I).Links.Item(1).Name = ContactName Then
    
                MyItems(I).Display
    
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox20").Value = Str
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox7").Value = Str1
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox24").Value = Str2
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox25").Value = Str3
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox26").Value = Str4
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox23").Value = Str5
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox8").Value = Str6
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox9").Value = Str7
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox10").Value = Str8
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox11").Value = Str9
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox12").Value = Str10
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox13").Value = Str11
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox14").Value = Str12
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox15").Value = Str13
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox16").Value = Str14
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox22").Value = Str15
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox21").Value = Str16
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox17").Value = Str17
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox18").Value = Str18
                myItems(I).GetInspector.ModifiedFormPages("P.2").Controls("TextBox19").Value = Str19
                
                myItems(I).StartDate = StartDate
                myItems(I).DueDate = DueDate
    
     
                 MyItems(I).Save
                MyItems(I).Close(0)
                TaskCount = TaskCount + 1
            End If
            End If
        Next
        end if
    
        Select case TaskCount
            Case 0
                msgbox "No tasks were updated."
            Case 1
                msgbox "One task was update successfully."
            Case Else
                msgbox taskcount & " tasks were updated successfully."
        End Select
    
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I don't see any Commandbutton4 code???
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    VBAX Regular
    Joined
    Jul 2013
    Posts
    75
    Location
    I have two codes related to Tasks...the CommandButton4 code creates the task....the one I showed above updates the Task and related fields...and the name of the commandbutton is "btnUpdateTasks" so that's the commandbutton I want to use..

  20. #20
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    After really looking at your code, I am sorry to have to say, but, except for a small edit to improve efficiency, that code can not be modified in any way unless the Coder is able to actually see the Form in Design mode.

    The Reason is that almost none of the controls or String variables have meaningful names. For Example; What is the name of the data used by Str5, CmboBox10 and Textbox23?

    For that matter, there are several controls with the same name, although members of different Form objects, that hold different kinds of data.





    Post the RemoteCommandButtonClick code you tried. Before Paste it in, click the # button at the top of the Post editor and paste the code in between the two (Bracketed) Code Tags.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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