+ Reply to Thread
Results 1 to 17 of 17

VBA Code to auto populate a fax template based critera's

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    VBA Code to auto populate a fax template based critera's

    Hi all,

    I am after a VBA code that I can use to populate a fax template based on criteria's or any ideas

    Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.

    I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge
    This way all the information for each fax is populated and the user can just print.

    and if possible mark a Colum non the master sheet as printed with a X or something

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Hi Sara.

    Can you post your sample fax template?

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    TEST.xlsmHi Mehmetick See attached You will see the master sheet which is all the data for each record and then on another sheet the invoice.

    NOTE: not all the information from the master is populated only certain information,
    Currently the user uses the dropdown box and selects the URN [Unique reference which is applied to each record] but the don't know if they are to be charged, as there maybe charges which re auto calculated but waivered E.T.C, So I need ity based on the last but 1 "column to be charged" Yes/no and the date

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Ok this looks simple.

    How about this:-

    Nothing happens until the user types yes into column BG.

    A macro copies the URN into the invoice sheet cell B24.

    You have already set the Invoice sheet to update itself with most of the required data.

    However I note that the Telephone and Fax number are not updated.

    Once yes is typed into column BG of the Master.

    A duplicate of the Invoice sheet is made, all formulas replaces as values.

    The Duplicate is saved as "URN" & URN Number.

    A macro is created that prints all sheets in the workbook that starts with "URN"

    Now is that what you need?

  5. #5
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    Mehmetick

    Ok sounds great just a few things to point out,
    -As the data for each row is automatically populated when the user presses add new record on the user form, Yes/No is automatically populated.
    Does this mater ?

    -the userform is used for logging new records and editing previous records.. I.e the user re-calls a row from yesterday say and edit some data and re save over the current data for that row, - this means a new fax needs to be issued with the corrected data.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Ok

    That is clearer.

    BRB

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Ok I cannot test this, I cannot get at the bottom of the userform, your screens must be bigger than my laptops.

    paste this code at the bottom of the userform buttom macro
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by mehmetcik; 08-14-2014 at 09:19 AM. Reason: Stop Delete Message

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Try this to see if it works:




    Please Login or Register  to view this content.
    Last edited by mehmetcik; 08-14-2014 at 09:30 AM. Reason: This Version Works.

  9. #9
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    Ok so paste the first bit in my Ok cmd on my userform and thenthe other just as a new module for the print ? thanks for your help so far

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Whoever wrote that Userform did it the hard way.

    If I'd written that userform I would have saved all textboxes with a simple loop.

    Something Like:

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 08-14-2014 at 10:18 AM. Reason: This saves all the textboxes in a userform when you click on the userform

  11. #11
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    i wrote most of it just by learning myself to be honest it isn't the best but does the trick.
    Wil this work when trying to edit a record as the record populates back to the form?

  12. #12
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    its showing an error at this point

    Sheets("Invoice (Master)").Range("B24").Value = txtUrn.Text
    dosent seem to populate the invoice

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    what is the textbox name?

    Oh I remember, I chaged your Invoices sheet name to "Invoice (Master)"

    I suggest that you do the same.

  14. #14
    Registered User
    Join Date
    03-20-2014
    Location
    wolverhapton
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: VBA Code to auto populate a fax template based critera's

    txtUrn if that's the one you mean ?

  15. #15
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    To populate 50 textboxes from a worksheet:-

    Please Login or Register  to view this content.
    But your textboxes must have sequential names Textbox1 to Textbox2

    Alternatively

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 08-14-2014 at 10:43 AM.

  16. #16
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Change your Invoices sheet name to "Invoice (Master)"

  17. #17
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA Code to auto populate a fax template based critera's

    Change your Invoices sheet name to "Invoice (Master)"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New sheet based on template to auto populate column in summary page
    By RichTea88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 07:52 AM
  2. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  3. Replies: 3
    Last Post: 02-28-2012, 11:54 AM
  4. Macro to add rows and populate cells based on critera
    By Scott Wagner in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2005, 12:00 PM
  5. HOW ?Excel chart auto insert /populate a code based on date
    By MikeR-Oz in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 12-22-2005, 08:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1