Results 1 to 17 of 17

Including Formulas in a UserForm

Threaded View

  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
    =DATE(YEAR(TODAY()),1,9 )
    End Date
    =DATE(YEAR(TODAY()),2,12 )
    Number of days off
    =NETWORKDAYS(Employee_Leave_Tracker!$B4,Employee_Leave_Tracker!$C4,lstHolidays)
    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.
    Private Sub CommandButton1_Click()
    CommandButton1.Show
    End Sub
    Private Sub UserForm_Initialize()
    'Empty StartDateBox
    StartDateBox.Value = ""
    
    'Empty EndDateBox
    EndDateBox.Value = ""
    
    'Empty selectemployeeBox
    selectemployeeBox.Clear
    
    'Fill selectemployeeBox
    With selectemployeeBox
    Dim rngselectemployeeBox As Range
    Dim ws As Worksheet
    Set ws = Worksheets("List_of_Employees")
    For Each rngselectemployeeBox In ws.Range("lstEmployees")
    Me.selectemployeeBox.AddItem rngselectemployeeBox.Value
    Next rngselectemployeeBox
    End With
    
    'Empty LeaveTypeBox
    LeaveTypeBox.Clear
    
    'Fill LeaveTypeBox
    With LeaveTypeBox
    Dim rngLeaveTypeBox As Range
    Set ws = Worksheets("Leave_Types")
    For Each rngLeaveTypeBox In ws.Range("lstHolidayTypes")
    Me.LeaveTypeBox.AddItem rngLeaveTypeBox.Value
    Next rngLeaveTypeBox
    End With
    
    'Set Focus on selectemployeeBox
    selectemployeeBox.SetFocus
    
    End Sub
    Private Sub SaveEntryBox_Click()
        Dim i As Integer
    
        'position cursor in the correct cell A4.
        Range("A4").Select
        i = 1 'set as the first ID
    
        'validate first three controls have been entered...
        If Me.selectemployeeBox.Text = Empty Then 'name
            Me.selectemployeeBox.SetFocus 'position cursor to try again
            Exit Sub 'terminate here - why continue?
        End If
    
        If Me.StartDateBox.Text = Empty Then 'StartDate
            Me.StartDateBox.SetFocus 'position cursor to try again
            Exit Sub 'terminate here - why continue?
        End If
    
        If Me.EndDateBox.Text = Empty Then 'EndDate
            Me.EndDateBox.SetFocus 'position cursor to try again
            Exit Sub 'terminate here - why continue?
        End If
        
        If Me.LeaveTypeBox.Text = Empty Then 'LeaveType
            Me.LeaveTypeBox.SetFocus 'position cursor to try again
            Exit Sub 'terminate here - why continue?
        End If
    
        'if all the above are false (OK) then carry on.
        'check to see the next available blank row start at cell A2...
        Do Until ActiveCell.Value = Empty
            ActiveCell.Offset(1, 0).Select 'move down 1 row
            i = i + 1 'keep a count of the ID for later use
        Loop
    
        'Populate the new data values into the 'Data' worksheet.
        ActiveCell.Value = i 'Next ID number
        ActiveCell.Offset(0, 0).Value = Me.selectemployeeBox.Text 'set col A
        ActiveCell.Offset(0, 1).Value = Me.StartDateBox.Text 'set col B
        ActiveCell.Offset(0, 2).Value = Me.EndDateBox.Text 'set col C
        ActiveCell.Offset(0, 3).Value = Me.LeaveTypeBox.Text 'set col D
    
        'Clear down the values ready for the next record entry...
        Me.selectemployeeBox.Text = Empty
        Me.StartDateBox.Text = Empty
        Me.EndDateBox.Text = Empty
        Me.LeaveTypeBox.Value = Empty
    
        Me.StartDateBox.SetFocus 'positions the cursor for next record entry
    
    End Sub
    Private Sub ClearBox_Click()
    Call UserForm_Initialize
    End Sub
    Private Sub CancelBox_Click()
        'close the form (itself)
        Unload Me
    End Sub
    How can I include the formulas using my UserForm ?
    And Im still learning Excel and VB
    Attached Files Attached Files

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