+ Reply to Thread
Results 1 to 17 of 17

Including Formulas in a UserForm

  1. #1
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Including Formulas in a UserForm

    Hi all.
    Another problem. In my worksheet I have a total of 5 sheets. 1 of my sheets has a formula for calculating number of days off taken from a start date and end date, as follows.

    Start Date
    Please Login or Register  to view this content.
    End Date
    Please Login or Register  to view this content.
    Number of days off
    Please Login or Register  to view this content.
    So, when using my UserForm to input these days, it is not including the formula, so simply enters the data literally as a date.

    Here is my VB code for the UserForm.
    Please Login or Register  to view this content.
    How can I include the formulas using my UserForm ?
    And Im still learning Excel and VB
    Attached Files Attached Files

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Including Formulas in a UserForm

    Try this..it places the formula in all the cells in the column. I added and If to it so that if column B is blank, column E will be blank...
    Employee Attendance2.xlsm
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    Works fine for me now.
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    Quote Originally Posted by gmr4evr1 View Post
    Try this..it places the formula in all the cells in the column. I added and If to it so that if column B is blank, column E will be blank...
    Attachment 500570
    Thanks for taking the time to look at this

  5. #5
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    Quote Originally Posted by bakerman2 View Post
    Works fine for me now.
    I like this way, thanks.

  6. #6
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    Quote Originally Posted by bakerman2 View Post
    Works fine for me now.
    Just one tiny problem.
    It is Americanising the date.
    I want this date, 6 February 2017, in this format 6/2/2017. But it is reversing the day and month to, 2/6/2017

    I have changed the default settings (ctrl +1) but still no change ?

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    Try this.

    Please Login or Register  to view this content.
    Thanks for adding rep points.

  8. #8
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    Ive finally had time to return to this.
    Your code certainly fixed my date problem, but has now broken copying the formula?
    Ive searched google endlessly, but to no avail.

    Quote Originally Posted by bakerman2 View Post
    Try this.

    Please Login or Register  to view this content.
    Thanks for adding rep points.

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    Have you tried converting the table back to a range and then reconvert it to a table ?

  10. #10
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    I will try that now.

  11. #11
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    OK, so that did not work.

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    Post the file that doesn't work. Have retried the file I posted in Post#3 and it works every time I save data.

  13. #13
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    I just tried your version again and yes, it works.
    I will upload my latest version now.

    Quote Originally Posted by bakerman2 View Post
    Post the file that doesn't work. Have retried the file I posted in Post#3 and it works every time I save data.
    Attached Files Attached Files

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    How does this work for you ?
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    What did you change?
    It works pefectly

    Quote Originally Posted by bakerman2 View Post
    How does this work for you ?

  16. #16
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Including Formulas in a UserForm

    Tables were a nice addition to XL but they can get grumpy sometimes for one reason or anther.
    I copied the formula from any row in the Column and pasted it in the topcell of the column, then double-clicked the little black square on the bottom right of the cell so the formula gets re-applied to the columns DataBodyRange.
    Et voilą, Xl knows again what to do with the formulas on newly added rows.

  17. #17
    Registered User
    Join Date
    01-29-2017
    Location
    UK
    MS-Off Ver
    Office 2016
    Posts
    20

    Re: Including Formulas in a UserForm

    Well done. I was pasting it into the last box, but didnt know about clicking the little black box.

+ 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. Need to automatically insert new row including formulas
    By fizzwolf in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-11-2016, 06:13 AM
  2. count not including formulas
    By guthrie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2016, 06:38 PM
  3. Insert n-1 rows including formulas
    By Businessrocker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2014, 09:27 AM
  4. Including averages logic into IF formulas
    By ExcelRookieATX in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2014, 06:26 PM
  5. Including Worksheet names in formulas
    By manatee2000 in forum Excel General
    Replies: 7
    Last Post: 12-28-2009, 06:40 PM
  6. Drop down lists: including formulas
    By keepyournose14 in forum Excel General
    Replies: 5
    Last Post: 11-28-2008, 09:55 AM
  7. Can I save a spreadsheet including formulas
    By Sandraoc1 in forum Excel General
    Replies: 4
    Last Post: 07-10-2007, 08:57 PM

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