User Form VBA Data + Print Sections

I'm sure that much of this works in my last example.

Data Set beginning date is FIXED and subsequent records cannot be overwritten (in the actual project current rows of Data to date is 46

6)
My code does not overwrite, but you can amend. If you don't want to allow amendments, then remove the update button and associated code

Date should show as the format in the Data section, i.e." dd mmmm yyyy"

My last example fixed this, posted this morning

Next "CA Contract Days" record entry should be activated and automatically populate the form, i.e in the image No. 11

Not sure what you mean here, the date and day and new number are all automatically generated when the new button is clicked.
User Must enter Data in columns D & E before the next record can be added

Next Contract Date Entry would (in the case of the image example) should be "dd mmmm yyyy"

This should be working after my last amendment

Would like to see Option Button default to 'Print to File' on form activation'

See this amended code

Private Sub UserForm_Initialize()
    Set ws = Sheets("Sheet1")
    Set rData = ws.Range("A1").CurrentRegion
    lRw = 2
    txtConDay.SetFocus
    LoadBoxes

    Me.lblNextConDay = ws.Cells(lRw, 1) + 1
''//// printer option is selected
    Me.optPrinter.Value = True
End Sub

Select allOpen in new window

Cannot get the Print Reports Section working correctly for 'Print to File' part

I'll look at this and post back later. I'll also check the date foormats
Recommendation for any other improvements most welcome
00-Test_User_Form-18Nov--3-.xlsm

Hi Roy,

Yes, we are getting closer with your last file. As you seem to be more comfortable working from your file rather than the file attached to this original question, I will only comment rather than ping ponging each other with file updates, OK?:

1. As this is a Test File can we delete rows beyond 10 (makes it easier to see what's going on).
2. I do not want a 'normal' user to amend  ANY records so please remove the update button and associated code.
3.

        .txtPenalty = Format(ws.Cells(lRw, 8), "0.00%")

Select allOpen in new window

not % should be

        .txtPenalty = Format(ws.Cells(lRw, 8), "0.00")

Select allOpen in new window

4.

 Me.lblNextConDay = ws.Cells(lRw, 1) + 1

Select allOpen in new window

should show the LAST record +1 to indicate NEXT contract day where the user needs to Add/Enter Data..
5. PREVIOUS RECORD at CA Contract Days = 2 shows msg "You have selected the first record" should be at CA Contract Days = 1?
6.  If ADD NEW RECORD is clicked the previous values are taken.  These should be empty and the user cannot continue without inputting the data before the next ADD RECORD.
7. Line 10 you had     Me.optPrinter.Value = True, this should be    Me.optFile.Value = True?
8.

            If Me.optFile = True Then
                Me.PrintForm
                Exit Sub

Select allOpen in new window

is not correct as it just Prints the User Form? This should activate the Excel>Word?
9. The From and To Dates in the DPicker Min Date needs to be set at 13/08/2015
10. Can we make the Data set as Table KPI 1 as dynamic range?

Paul

Hi Paul

1. I've deleted the rows from 10, but this will not affect the code

2. What I've done for now is disable the update button. I can add a password for a "super user" to be able to edit if you want.

3, 4, 5. Should be OK now.

6. The Add new record button was OK before we added the auto load with the first record. What I have done is set this button as disabled. There is a reset button that will clear & add the next available number. At the same time it will enable the add button. Before adding the data the code checks that each TextBox is complete and stops if there is a missing entry, giving a warning.
The next number, day and date are automatically calculated.
 I have converted the data to a Table so it is dynamic and formulas will copy down. I am assuming that the grey boxes do not require user input. The Performance TextBox calculates, but I need to know what the other calculations are.

7. I wasn't quite sure what you were doing there.

8. The print option shows the print dialog, allowing the user to change printers. The File option prin previews the data. This can be changed to print when the  code is complete.

9. I don't think that the DatePicker is a good idea, they do not work on every computer. Mine at work does not have datepickers. I think I should change these to a non-activeX as mentioned before.

10. I have changed the data table to a Table which is dynamic and formulas will copy down.

Test this out and let me know what you think so far.
00-Test_User_Form-18Nov--3---1-.xlsm

Hi Roy,

Looking good!

1. Noted
2. Go ahead and add a password for a "Administrator" to be able to edit, that would be more appropriate.
3 & 5. OK
4.  This needs to show NEXT Contract Day in the Data set, i.e., if form is open at CA Contract Day 1 then needs to show Date in the format dd mmmm yyyy which would be for example today 22 November 2016, not a Number. See below:

6. There is run time error '438' which I presume is missing calc/references but Performance % does update.

The calculations for E, F & G are as follows:

Column E

=IFERROR((D32/C32),0)

Select allOpen in new window

Column F

=IFERROR(INDEX(KPI_Lookup!$F$7:$F$24,LARGE(IF((KPI_1!$E32<KPI_Lookup!$E$7:$E$24),ROW($C$7:$C$24)-ROW($C$7)+1,""),ROW($A$1))),0%)

Select allOpen in new window

Column G  

=Prev_12_Months_1!$Q$2*F32

Select allOpen in new window

7. OK
8. OK
9. I leave the DPicker to you.
10. Noted

Paul

I'll take a look later

I have changed the label to show the next date.

I cannot reproduce the error in the calculation When I put the values in for the previous entry it returns the correct per centage. What are you entering for STD and ADT.

I'll add the other calculations later but can you confirm the Columns to use, the ones in your post don't look correct.

What does the KPI Lookup return?

Hi Roy,

I have imported the Userform into my main project to see how it performs but its throwing up a run error, and the existing data does not load into the boxes - see attached side by side comparison and error detail.

There is one small difference between the two inasmuch as the Sheet No. and that the Project form has additional column (I) however this is not included in the dynamic Table1.

SDT (D) and ATD (E) entries are only numbers and by =IFERROR((E2/D2),0) calculate a % in column F.

Column G =IFERROR(INDEX(KPI_Lookup!$F$7:$F$24,LARGE(IF((KPI_1!$F32<KPI_Lookup!$E$7:$E$24),ROW($D$7:$D$24)-ROW($D$7)+1,""),ROW($B$1))),0%) looks up % value as a penalty charge.

Column H =Prev_12_Months_1!$Q$2*G32 then calculates the penalty charge against an amount derived from another financial summary.


Paul
UserForm_Project.docx

Additinal columns will affect the form. I'll take a look later

Hi Roy,

Seems this Q has been (rightly) re-instated?

Paul

Hi Roy,

The latest file you sent does not resolve the Print issues, as even if I select the dates (range) I want the preview shows the full range of data.

The KPI Reports for 'Print to File'  (or 'Save As File') option button is only a copy of the Table of each (sheet) specific scenario for selectable From - To dates (range) to be saved as a word document, nothing more than that - there is no specific template.

Exactly the same thing under the 'Print' option button but to the Printer via the xl Printer dialogue.

I have one query on the Revenue Monthly data area, specifically will columns 2, 3, 4, 6, 7, 8, and 9, auto fill when the data input from columns 13 & 14 is entered and Add File button to complete the full row data entry ( columns 5 & 10 are derived from the Day Revenue section, columns 11, 12 and 15 are formulas on the sheet).

Thanks to all your help in other areas the foregoing are now the only open issues remaining to complete the project.

Paul

I think this last reply should be in the latest thread.

The dates filter fine for me, but I have only edited the Day Trip form so far

Hi Roy,

Yes, I agree this is now the latest thread and concerns only the Print functionality as the open issue.

Paul

I have used the day trip form to test this.

Open the Day Trip form from the menu sheet. Select a range of dates, say 14/09/2016 to 18/09/2016. Choose to fileand then click Run Report. A Word Document will be created with the selected data pasted to it.

I have disabled the Print Dialog because I'm not sure if that is what you want, maybe you just want to print the Excel sheet with that option
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm
Trip-Report.docx

Hi Roy,

Yes, that gives me what I want based upon the date selection however, it loops through and creates multiple Word docs if No report type is selected or if I select a report (say Table KPI 1, it prints the full date range of data to Word, and creates a 2nd blank Word doc then the code stops at      wd.Range.Paste but this latest file is at least on the right track.

For the Printer option that should be straight forward for the user to print the selected date range. They can always cancel the dialogue and change the dates or the default would be to print the entire sheet however that might be troublesome on the Revenue sheet which has two tables so on the Monthly side they would need to change page orientation to landscape because it is quite wide.

Same scenario would apply to the Print to File unless page size is auto selected?

Paul

It only creates one document for me, are you sure you are choosing dates within the table? There is no loop within the new code.

Hi Roy,

I tested the file exactly as you send it with a 4 day range of dates that were in the table, strange?

Paul

Sorry, I tidied up after my testing and moved some. Try this version
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

That's got it in that last file, thanks.

Paul

I still don't see why you have the KPI drop down.

I'll add the code to the two other forms

Hi Roy,

I need the ComboBox for the user to select any Report type from within any user form. Just because the userform may be being used for say TripData the Print side should be functional to Print out or Save to File a related Report for checking back of data if needs be should the Data under PW control, be hidden.

Also noted that the Printer function is still inoperable and if selected another Word doc is generated. Also curious why the data headers are showing the filter icon?

Paul

The print option prints out the worksheet.

The file option copies the range as and pastes as an image to Word, so the filter drop downs are copied

Hi Roy,

OK I stand corrected, the Printer side does indeed send to the printer and it prints without the filter icons but I would prefer to see the printer dialogue box as I had before before printing.

Regarding the filter icons, I checked the data again and there are no filter icons showing on the sheet so why show on the Word file?

Examples attached.

Paul

I have added the code to the 3 forms.

The print to word action now prints as a word table, no filter icons. The only problem is the Monthly where the Headings don't fit the available column spaces. I'll see if I can find code to fix this. The reports work for each form and automatically print Landscape - this seems to be the best layout.

DatePickers - code has been added to set the min and max dates to be the earliest and latest dates in the tables.

The printer button displays the Printer Dialog, currently it is using PrintPreview, this can be changed to actually print when testing is finished.
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

I have added some code that changes the Font size to 8 in the Month's report. The text now fits the columns
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

Apologies for the delayed response, domestic duties calling, the latest update looking good!

I can see that you have added/modifies some modules and that Landscape is available to accommodate the wider headings. I did notice that the Show Day Revenue and Show Month Revenue command buttons are being picked up the range but couldn't see how this was happening, and that the Print to File/Printer only applies to the Userform currently open.

Noted that the printer button displays the Printer Dialog, currently it is using Print Preview and that this can be changed to actually print when testing is finished.

I am still curious about an earlier query on the Revenue Monthly data area, specifically will columns 2, 3, 4, 6, 7, 8, and 9, auto fill when the data input from columns 13 & 14 is entered and Add File button to complete the full row data entry ( columns 5 & 10 are derived from the Day Revenue section, columns 11, 12 and 15 are formulas on the sheet).

In summary it now looks as if the Combobox linkage to Print to File/Printer for any report type regardless of which Userform is open is the last obstacle?

Paul

did notice that the Show Day Revenue and Show Month Revenue command buttons are being picked up the range but couldn't see how this was happening

The data used is determined by defining which Table to use. Using Tables allows multiple data sets on a sheet and each can be filtered individually

Month Revenue:

 Private Sub cbRun_Click()
    Dim oTbl As ListObject

    Set oTbl = ws.ListObjects("Table_PrevRev")

Similarly for Daily Revenue
[code]Private Sub cbRun_Click()
    Dim oTbl As ListObject

    Set oTbl = ws.ListObjects("Table_Rev")

    With oTbl

    With oTbl

Select allOpen in new window

[/code]

I am still curious about an earlier query on the Revenue Monthly data area

Formulas and formatting in a Table(ListObject) should copy down as new rows are added. You should have the relevant formulas in the specific columns

In summary it now looks as if the Combobox linkage to Print to File/Printer for any report type regardless of which Userform is open is the last obstacle?

I have no idea what this ComboBox is for and what each selection is supposed to link to.

Hi Roy,

Thanks for the clarifications.

The Combobox (Listbox) simply allows the user to select the Table he/she wishes to print from the List choices and not exclusively for the Userform which is in use, i.e. if the user was working on UsrFrmTrips he/she could print any Table selection and not Just TripData - e.g.  Table KPI 1(=TripData), or Table KPI 2, or Table KPI 3, or Table KPI 4, or Table KPI 5-14, or Table KPI 15-20, or Table Daily Rev, or Table Monthly Rev.

Paul

There are only two Tables(LIstObjects) in the workbook

Hi Roy,

The Test files you have been working with are only part of the overall project of integrated data which has 18 sheets, 10 Userforms, and 9 Tables (ListObjects)in the complete Workbook/File. I had already got much of the project working but purposely kept the Test files to a minimum of only the problem/query areas to avoid any confusion.

The data itself will come from 2 Owner/Contractor Teams of 20 where different individuals are assigned to certain tasks which is why I need the functionality to print any report at any stage of the data reconciliation stage between the two entities as the Excel file is the definitive contract compliance reference.

Hope this helps your understanding of the bigger picture.

Paul

I'll set the print options up for the available Tables but I would really have looked at using one userform. You should be able to extend the list

Do you want the Report Period options to apply to the selected Table?

Yes, Report Period options should apply to the selected Table

No attachment.

I tried adding it twice
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

Your code seems to work OK however when I transfer to my Master file I get a Compile error

With .cboReport
            .List = Sheet30.Range("AZ1").CurrentRegion.Value

Select allOpen in new window

even though I believe have correctly redirected the PrtSheets Table to another Sheet.

 

Option Explicit

Private Sub cboReports_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.cboReport
        Set ws = Sheets(.List(.ListIndex, 2))
        Set oTbl = ws.ListObjects(.List(.ListIndex, 1))
    End With
    ''/// set starting date in To Date as the latest date in the column
    Me.ToDate.Value = Application.WorksheetFunction.Max(oTbl.ListColumns(3).DataBodyRange)
    ''/// set starting date in From Date as the earliest date in the column
    Me.FmDate.Value = CDate(Application.WorksheetFunction.Min(oTbl.ListColumns(3).DataBodyRange))
End Sub


Private Sub cmbAddRecord_Click()

    lRw = rData.Rows.Count + 1
    With Me
        ''///new record
        ''/// if the entry already exists
        '        If WorksheetFunction.CountIf(rData.Columns(1), txtConDay) = 0 Then
        '            MsgBox "The Contract Day you entered doesn't exist. Please try again...", vbExclamation, "Not Found!"
        '            Me.txtConDay = Empty
        '            Exit Sub
        '        Else: lRw = rData.Rows.Count + 1
        .txtConDay.Value = lRw - 1
        ''/// assumes records are in the same order as currently, i.e. not sorted.
        ''/// next date is added automatically
        .txtDate = Format(ws.Cells(lRw - 1, 3) + 1, "dd mmmm yyyy")
        .txtDay = Format(ws.Cells(lRw - 1, 3) + 1, "dddd")
        '        End If
    End With
    WriteToSheet
    ''/// update data range
    Set rData = ws.Range("A1").CurrentRegion
    lRw = 2
End Sub

Private Sub cmdbNextRecord_Click()

    If Me.txtConDay.Value = Empty Then
        MsgBox "No record selected", vbCritical, "Cannot run"
        Exit Sub
    End If

    lRw = CLng(Me.txtConDay.Value)

    If lRw = rData.Rows.Count Then
        MsgBox "You have selected the last record", vbCritical, "Cancel"
        Exit Sub
    Else: lRw = lRw + 2
    End If

    LoadBoxes
End Sub

Private Sub cmdbPreviousRecord_Click()
    If Me.txtConDay.Value = Empty Then
        MsgBox "No record selected", vbCritical, "Cannot run"
        Exit Sub
    End If

    lRw = CLng(Me.txtConDay.Value)

    If lRw = 2 Then
        MsgBox "You have selected the first record", vbCritical, "Cancel"
        Exit Sub
    Else
        lRw = lRw
    End If

    LoadBoxes
End Sub

Sub cmdClear_Click()

'/// if you use a With Statement then the control needs prefixing with .
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "TextBox" Then oCtl.Value = Empty
    Next oCtl
End Sub

Private Sub cmdFirstRecord_Click()
    lRw = 2

    LoadBoxes
End Sub
Private Sub cmdLast_Click()
    lRw = rData.Rows.Count
    LoadBoxes
End Sub

Private Sub cmdSearch_Click()


    If txtConDay = Empty Then
        MsgBox "Please input a Contract Day to search a record.", vbExclamation, "CA Contract Day Please!"
        Exit Sub
    End If

    ''/// if the entry already exists
    If WorksheetFunction.CountIf(rData.Columns(1), txtConDay) = 0 Then
        MsgBox "The Contract Day you entered doesn't exist. Please try again...", vbExclamation, "Not Found!"
        Me.txtConDay = Empty
        Exit Sub
    End If

    lRw = CLng(Me.txtConDay.Value) + 1
    LoadBoxes


End Sub

Private Sub cmdUpdate_Click()

    res = Application.InputBox("Please enter the password", "Admin Access")

    ''/// cancel button clicked
    If res = False Then Exit Sub

    If res <> PW Then
        MsgBox "You have entered an incorrect password", vbCritical, "Cancelled"
        Exit Sub
    End If

    If txtConDay = Empty Then
        MsgBox "The Contract Day TextBox is empty, so no record can be updated in this case." & vbNewLine & _
               "Please try again.....", vbExclamation, "Not Found!"
        Exit Sub
    End If

    WriteToSheet
End Sub
Private Sub ComboBox1_Change()

End Sub

Private Sub UserForm_Initialize()

''//// define the sheet to work with
    Set ws = Sheets("KPI_1")
    ''/// define the range of data
    ''/// I moved the data down to allow the button to be added.
    ''/// the code will work wherever the data is now. Try adding or reemoving rows above the data
    ''///  read about CurrentRegion - https://msdn.microsoft.com/en-us/library/office/ff196678.aspx
    Set rData = ws.Range("A1").CurrentRegion
    lRw = 2

    ''/// this will be the last row of the data
    lRw = rData.Rows.Count
        With Me
        .txtConDay.SetFocus

    
''/// get the last date
        .lblNextConDay = Format(ws.Cells(lRw, 3), "dd mmmm yyyy")
        ''/// set file option box to True
        .optFile.Value = True
        ''/// set starting date in To Date as the latest date in the column
        .ToDate.Value = Application.WorksheetFunction.Max(rData.Columns(3))
        ''/// set starting date in From Date as the earliest date in the column
        .FmDate.Value = Application.WorksheetFunction.Min(rData.Columns(3))

        ''/// set up Report list
        With .cboReport
            .List = Sheet30.Range("AZ1").CurrentRegion.Value
            .ListIndex = 1
        End With

    End With


    ''/// this populates the TextBoxes
    LoadBoxes
End Sub
Private Sub cbRun_Click()



    With Me.cboReport

        If .ListIndex <= 0 Then
            MsgBox "You must select a Report to produce", vbCritical, "Try again"
            Exit Sub
        End If

    End With

    With oTbl
        ''/// first check if Table is filtered
        If Not FilterOn(oTbl) Then .Range.AutoFilter
        ''/// Set up a filter, giving dates between the selected dates in DatePickers

        .Range.AutoFilter Field:=3, Criteria1:= _
                          ">=" & CLng(Me.FmDate.Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Me.ToDate.Value)
        Select Case True
        Case Me.optPrinter
            Me.Hide
            Application.Dialogs(xlDialogPrint).Show
            ws.PrintPreview
            '            ws.PrintOut
            Me.Show
        Case Me.optFile: ExcelRangeToWord rData, True
        End Select
        ''/// Now clear the filter
        .Range.AutoFilter Field:=3
    End With
End Sub

'Private Sub cbRun_Click()
'
'
'    For Each oCtl In Me.Controls
'        If Me.optPrinter Then
'            Application.Dialogs(xlDialogPrint).Show
'        Else
'            If Me.optFile = True Then
'                Me.PrintForm
'                Exit Sub
'            End If
'        End If
'
'    Next
'End Sub

Private Sub cmdQuit_Click()
    Unload UsrFrmTrips
End Sub
Sub LoadBoxes()
'/// if you use a With Statement then the control needs prefixing with Me
    With Me
        .txtConDay = rData.Cells(lRw, 1)
        .txtDay = rData.Cells(lRw, 2)
        .txtDate = Format(rData.Cells(lRw, 3), "dd mmmm yyyy")
        .txtStd = rData.Cells(lRw, 4)
        .txtAtd = rData.Cells(lRw, 5)
        .txtPerf = Format(rData.Cells(lRw, 6), "0.00%")
        .txtRate = Format(rData.Cells(lRw, 7), "0.00%")
        .txtPenalty = Format(rData.Cells(lRw, 8), "#,##0.00")
    End With
End Sub
Sub WriteToSheet()
    With rData
        .Cells(lRw, 1) = Me.txtConDay.Value
        .Cells(lRw, 2) = Me.txtDay.Value
        .Cells(lRw, 3) = Me.txtDate.Value
        .Cells(lRw, 4) = Me.txtStd.Value
        .Cells(lRw, 5) = Me.txtAtd.Value
        .Cells(lRw, 6) = Me.txtPerf.Value
        .Cells(lRw, 7) = Me.txtRate.Value
        .Cells(lRw, 8) = Me.txtPenalty.Value
    End With
End Sub

Select allOpen in new window

Can you see any obvious errors?

Maybe better I now send you the pre-final Master file perhaps but I'd rather send this privately rather than through the forum if that's OK with you.

Paul

Best send it to me and I'll check it out

Sent to you separately