Multiple Userforms from a previously working Template

Paul you have changed and added userforms and sheets I think. You have 3 identical forms, one should be sufficient.

You need to explain why you need 3 identical forms and which form works with which sheet.

I've fixed the code to display the day revenue form
Test_Trips_PrevMth_PrevYear_Sum.xlsm

This has made the navigation buttons dynamic.
Test_Trips_PrevMth_PrevYear_Sum.xlsm

Hi Roy,

Thanks for coming to my aid again.

The forms are identical but the content differs. The three we are working with now are the most complicated in terms of data content and addressing their respective purpose. Trying to do this on one form I think would be to difficult for my users so I prefer to treat each stage as a step-by-step approach in getting to the end result. There will be another 6 userforms to complete the overall project with 9 userforms all derived from the first from the template however, the content for these will be much simpler.

I still cannot see what you have doe to get things working and would appreciate some additional comment as appropriate to any changes in each form. I have tweaked a few odds and sods and attach my update for further reference.

Paul
Test_Trips_PrevMth_PrevYear_Sum-p.xlsm

I'll have a look later. Some details of what you are doing with all the forms would help

I've added some notes to my changes for you. The password code to update seems to be missing now.

Hi Roy,

The best way is to show you...

This pics below are the other userforms using Excels built-in form but unfortunately they are not really appropriate for what I need.

The entire workbook works through some contract conditions that are complex calculations results based upon performance data that the users cannot comprehend.

The users have a very low level of knowledge and ability (lessons learned from 6 months of working with them1). What I am trying to do is provide them a 'no brainer'  fill-in/feedback userform for each stage so they can check as they go day-by-day and month-by-month as to where they are. At the moment the process is all manual single Excel sheet which take weeks and months to submit to management, and they still manage to get things wrong. I believe my approach will speed up the process and minimise/eliminate errors.

Cannot comment on your changes as you did not attach the file.

Regards,
Paul

Sorry about the file. I thought that I had attached it.

I'm not clear what the forms are for. Surely, if you are creating reports using formulas then  report sheets would be the best way
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

Followed your comments and I still cannot get the TripData (Daily Trips) and Revenue (Month) forms to load. I updated my file with your previous comments attached. I did look at creating reports and based on my knowledge and ability of the guys involved but decided against that idea.

Paul
Test_Trips_PrevMth_PrevYear_Sum-p1.xlsm

Are the forms all to be used on the same sheet?

What is the difference between the forms?

You haven't copied the code correctly. In some lines you haven't used my amended code.

When using the UserForm, or any events, select them from the drop down in the right of the VB Editor,

Private Sub UsrFrmMthRev_Initialize()

Select allOpen in new window

You have typed this yourself, it should simply be

Private Sub UserForm_Initialize()

Select allOpen in new window

I'll check the rest of the code for you

The monthly form is loafing correctly.

I will have to re-write the code that posts to the sheet for each userform.

You need to be careful that controls names actually exist
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

Many thanks for the last update, I now have all three Userforms loading, and the required data/formatting where I want them to be.

You mention that you will re-write the code that posts to the sheet for each userform so no comment from me at this stage, but I think you understand what I am trying to achieve.

On the userforms/other points in general:

1. In a previous post you mentioned an 'Administrator' password protection to prevent users ammending and existing data but I can't see that in the code.
2. Can I combine the current 3 Modules in one Module?
3. I want to hide the worksheet when the userform is opened. Will the following code work OK? I feel as if I should be adding the Sheet names somewhere?
4. Sheet MthSumm contains a Pivot Table Summary from Revenue. Is there any additional cvode required to ensure that this updates on file opening/closing?

' ThisWorkbook module

Private Sub Worksheet_Open()
    Application.Visible = False
    UsrFrmTrips.Show
    UsrFrmMthRev.Show
    UsrFrmDayRev.Show

End Sub

'UserForm module1

 Sub ShowDayRev_Click()
     Unload     UsrFrmDayRev.Show
    Application.Quit
End Sub

'UserForm module2

Sub ShowMthRev._Click()
     Unload     UsrFrmMthRev.Show
    Application.Quit
End Sub

'UserForm module3

Sub ShowTrips_Click()
     Unload    UsrFRmTrips.Show
    Application.Quit
End Sub

Select allOpen in new window

All up to date changes now consolidated in the updated file Test_Multi_Userforms_Rev0.

The end is nearly in sight! Thanks again for all your support Roy, much appreciated!

Paul
Test_Multi_Userforms_Rev0.xlsm

Application.Quit will simply close Excel.

You can hide the application, but I wouldn't recommend it with level of knowledge that your users have.

I'll check the rest later

I think you mixing up hiding worksheets and hiding the application.

If this was my project then I would hide the data sheets. Create a front sheet with the buttons on, actually I would add a Tab to the Ribbon for this workbook.

Your PivotTable is getting the data from another workbook on your desktop. Is this intentional?  I have changed the source to this workbook.

All the code to display the userforms are in one module.

The data sheets are hidden

I don't know what the code above is supposed to do but don't use it

I'll check the admin password and code to write to the sheets tomorrow.

I have added a menu sheet and hidden the Tabs.
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,

That last update looks like a good idea, thank you for that. How would I or an Administrator get back to the sheets and VBA? Another password protected command button on the Menu to 'show' the sheets again?

On the Menu page the 'Show Trips Data' opens the 'Show Month Revenue userform.

Cheers,
Paul

I'll add a button and change the macro to show the correct form on the other one.

I'll also, check the other code.

I've fixed the buttons on the menu sheet and added a new to hide or unhide the data. Admin password is Secret.

I've added the same password requirement to the Add & Update buttons on the forms. Same password.

Check the Trip data form and see if if it is working as you want. I'll update the other two forms when you get back to me.

Hi Roy,

No file attached.

Paul

Sorry
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm

Hi Roy,
Thanks for the update file. I notice that you have Twexed out the Print code which you have said you are looking at so no comment there.
As I am now getting closer to going live with the userforms I want to be clear in my own mind that the userform is intuitive (simple stupid) as it can be for the Data entry operator, i.e, that they only fill in the Data in the white back colour boxes.
1.      Contact Days is OK, this, Day and Date are pre-loaded when the userform opens. The Data input operator enters the values for STd and Atd which should then trigger the remaining calculatioons and LookUp function to automatically complete the entry. If no data is entered the code stops and the user is alerted to enter the data.
2.      I notice that you have Twexed out the following code:

'Sub CalcPerformance()
'    With Me
'        If .txtStd > Empty And .txtAtd > Empty Then .txtPerf.Value = _
'           Format(CInt(.txtAtd) / CInt(.txtStd.Value), "0.00%")
'    End With
'End Sub

Select allOpen in new window

The Excel Sheet has formula which do the calculations and LookUp so is the above code needed?
3.      Whichever way the values get calculated, can I assume that this will happen automatically and that the code will move on to the next available record for data entry?
4.      Clear the form is self explanatory to the user, as is First Record, Next Record, Previous Record and Last Record.
5.      At the moment the Reset button appears to do nothing. I am wondering however about whether this and Add New Record are required? The scenario is that whilst we want to maintain the integriy of the data mistakes can and do occur and the Data entry operator has made a mistake in a previous entry somewhere which needs to be ammended. You have provided Administrator Password for Add New Record and Update – maybe Update should be Ammend Record which will require Administrator Password and there is no need for Add New Record because we are already aurtomatically adding new records in the normal procedure?
Appreciate your thoughts Roy.
Paul

Hi again Roy,

Just a thought while you are looking at the 'Print' code. The previous code I had was as below:

'    Set wdApp = CreateObject("Word.Application")
'    wdApp.Visible = True
'  '    Set wdApp = CreateObject("Word.Application")
'    wdApp.Visible = True
'    Set WdDoc = wdApp.Documents.open("<path>")
'    wdApp.Selection.WholeStory
'    wdApp.Selection.Delete
'    Set WdDoc = "G:\Test Reports\Test_Report.docx"
'    wdApp.Selection.WholeStory
'    wdApp.Selection.Delete
'    Set WdDoc = "G:\Test Reports\Test_Report.docx"

Select allOpen in new window

The above code is applicable for my machine but since I do not know which machines the Excel Project will be operated from, shouldn't the 'Print to File' option bring up the host machines 'Save As' dialogue ?

Paul

Hi Roy,

Thanks for your response, I am clear now on the points that I raised. The Reset button will be deleted, Add New Record retained but Admin password not needed, and I will change Update to Ammend Record with Admin password protrection.

The data on each sheet will be identified as a Table. All the Print section is intended to do is to print a specific Table for a user selected date period of time. From Date  - To Date. The Print to file is for the user to Print the selected Table and Save As a Word document.

Hope that makes it clearer.

Paul

Hi again Roy,

Further clarification to the last paragraph.

The data on each sheet will be identified as a Table. All the Print section is intended to do is to print (hard copy) output of a specific Table for a user selected date period of time. From Date  - To Date (nothing special, just as it is in Excel format for internal peer (team) review). The Print to file is for the user to Write the selected Table and Save As a Word document (the purpose being to elaborate the word document with commentary on the data  as a 'Report' - not necessariliy in English).

Hope that makes it clearer.

Paul

I'll look at this later.

Have you tested this on other PCs to see if the DatePickers work on all?

I'm not sure if it is necessary to export to Word.

The code will need adding to filter the data between the dates or print all.

Hi Roy,

Impossible for me to know the age of other PC's that the Excel Project may be expected to operate on. I did however take note of your earlier comments and changed the DatePicker to the ActiveX version.

If you have a way to filter the dates or print all and save the Table data to a Word document then feel free to change the code to suit.

Paul

Hi Roy,

Are we making any progress oin the Print solution?

Paul

I haven't had time to look at this yet, but hopefully I should be able to this weekend

What are the various KPI Reports?

Do you have a Template to in Word for the reports?

I have added some code to the Trip Data form that will allow you to select dates to start and end with. Then it displays the report in Print Preview for now.

Let me know how it looks and give me a bit more of an idea how the reports should look.

Your last post with me needs closing, I think we resolved it as far as we can.
Test_Trips_PrevMth_PrevYear_Sum-v1..xlsm