Replace table with Userform

[Zack Barresse]

Hmm, something is off. Try shutting Excel all the way down, open it back up, and try again. It ran no problem for me.

[Newbie999]

Perhaps my requests have made this a nightmare of a workbook. I apologize for not making it a logical format. I think the main issue is have indirect ways of showing the data.

Instead of one input table that the calendar fills from I opted to make the formulas pull from the other table.

Is this the case of going back to the drawing board?

[Newbie999]

I have this version which the formulas pull from the tables directly in columns U to X.

The only issue I forsee with this will be when I have to scroll down to appointments and lose the calendar view.

[Zack Barresse]

I'm not sure I see the difference in the new workbook (6.1.1) other than you've injected several blank rows into the data Table. From a design perspective, input data is generally segregated from output/display data, which is your calendar in this case. having the input data adjacent to the output can have issues. One of which you've identified, if the user wants to see more data they have to scroll down. Partly this is because the Table is housing all data and not just the data for the currently viewed month.

My recommendation for this would be to house the input Table on a different worksheet. If you want to show the data in a Table format for the selected month, you can do that with a secondary Table. This would probably be the approach I would take if I were doing this.

As a side note, the Table in this new version has headers which are blank. It appears your header row is one row above the Table in a standard range. This makes it look like a blank row in the Table. Also, your formulas aren't using structured references, which I would recommend using. It makes working with formulas a bit easier.

I can setup a data input sheet, like I described above, although I don't want to do some work if you don't want it (waste of both our time). Is that something you're interested in?

[Newbie999]

Yes I am interested in that for sure Zack. I will move the tables to a different sheet and be back soon with that version thanks.

[paulked]

@paulked: It's a good idea to compartmentalize your code, such as separate routines for protecting and unprotecting, as it keeps your code modular, reusable, and easier to maintain and debug. If this were the only code ever written, then sure, just call it in the routine.

[Newbie999]

I corrected the formulas and now have the tables on the other sheet. I do appreciate all the help Zack.

[paulked]

@paulked: It's a good idea to compartmentalize your code, such as separate routines for protecting and unprotecting, as it keeps your code modular, reusable, and easier to maintain and debug. If this were the only code ever written, then sure, just call it in the routine.

[Newbie999]

Hi Zack,

Please allow me some time to think about my workbook requirements. I may be redesigning it for my new business. The corona virus has altered our lives in many ways and now I am being forced to start the new business soon. I will be doing a handyman business so I have to think about customers and appointments.

Thank you for understanding

[Newbie999]

Okay I think maybe I should close this thread and start a new one. I have a workbook that is much different now.

Thanks for all your help

[Zack Barresse]

I made a few changes.

• Table names updated to properly reflect the type of data they house
• Added a (highlighted) set of merged cells under each Table (explained below)
• Updated add appointment routine to properly reflect column changes in source Table
• Added ability to mark appointment entry as holiday or birthday/anniversary
• Removed 'M' and 'D' columns of birthday/anniversary Table
• Combined data from all three tables into a [Power Query] Table in order to report on it (PivotTable, explained below)

With the 'edit' button, it will launch a new form with three buttons which will let you choose Appointment, Holiday, or Birthday/Anniversary. This will launch the 'fAppointments' with that selected source Table as the RowSource. If I were doing this for myself, I would've probably combined the Table's instead of having three of them, and added another field to specify the type of appointment. This is a decent enough method with such small amounts of data, and also serves as a good example of how to use a dynamic data source for a listbox. Additionally, there is now a drop down in 'fAppointment' to specify the source [appointment] type.

With the source Table's being stacked vertically, and you having some formatting above them, I added a small merged range directly underneath each Table. I did this to preserve that blank row you have. Excel is funny in how it consumes rows for adding to Table's. If there's a blank row, Excel will just grow the Table without pushing anything down like a normal insert would. Only when it comes up against something is when it tries to insert rows, but only the width of the Table range, which is a reason for not stacking Tables which have different column counts or not in the same columns. This is not needed at all, and only preserves the white space on the Tables on the 'Tables' sheet.

In order to show a list of data on the 'Calendar' sheet (original Table showed here) and not have to use some fancy formulas, I opted for a PivotTable of all data. This presented the pros I wanted (no mega-formulas, constant updating, ability to filter/drill-down), although it presented challenges. Foremost was you had your data split into 3 Tables. I had to use Power Query (on the Data ribbon tab, in the Get & Transform group) to set each source Table into its own connection, then combine those into one giant source Table with all data (as seen on the Consolidated Data sheet). Then I pointed the PivotTable to the newly created consolidation query as the source and voila.

In addition to adding a PivotTable to the Calendar sheet, I added a slicer so you could filter for the source [appointment] type. I also added a worksheet event so when you change the year or month, it updates the PivotTable with that selection too. This way it will only show data for the selected month, however, you can manually adjust if you want.

I hope this helps and is what you were looking for.


File: Calendar Rev 6.1.5.xlsm

[Zack Barresse]

I didn't see your last posts before I posted. Apologies. I hope the work I did can still be utilized.

[Newbie999]

Yes I will try to incorporate to the new table once I figure out how to get the jobs on the calendar or someone else can figure that out.

I agree that the form method is great thanks Zack.

[Newbie999]

Thank you so much Zack for all of your hard work getting this workbook to function properly. I wish I could return the favor some how.

[Zack Barresse]

You're very welcome, it's my pleasure. I've only ever asked for one thing in return for [forum] work - pay it forward when you can. This community is amazing, and I certainly wouldn't be able to do what I can today without the help from others in it. They sacrificed their time so that I might build my knowledge base. I'm happy to be paying it forward.