Replace table with Userform

[Newbie999]

Hi,

How can the table be replaced with a data entry form please?

Thanks

[Zack Barresse]

This is a little strange, because your Table has blank rows in it, which means you'll have to find the first empty row. This can be tricky, although your Table is very, very simple. If you try this on more complicated Table's it can be trickier.

Take a look at the attached. A small form with two controls.
26288

[Newbie999]

Thank you very much. I am sorry for having blank rows. I thought that if the sheet is protected the table won't expand. Also can there be separate fields for time and date instead of combined?

[Zack Barresse]

Sure. You should be able to work that out with what I've provided. Look at the control names to name it appropriately. You can see how I separated the date from the time. When dealing with dates/times, I generally just force it into a variable of the Date type and let Excel sort it out whether it recognizes it or not. Manual entry is generally not advised for dates/times. The other option is to create a drop down, but that gets more manual. This is the simplest solution.

Also, when the Table is full, there is a piece of code which adds a new row. If the sheet will be protected that will fail, so you may want to take that out.

[Newbie999]

It would also be nice to edit appointments or search in some way. I was looking on youtube and saw some with scrolls to jump to new records (appointments in my case), save, delete, etc. How did you come up with the coding? I want to learn how to do this also please.

[Newbie999]

Hi again,

I attempted to separate the time and date but not sure how to assign the boxes.

[Zack Barresse]

If you want to edit appointments via userform, it will take a bit more work. Userforms are really nice and handy in many situations. Their biggest downside lies in the fact that you have to program 100% of them. This is a downfall of VBA, honestly. Most modern tools are much, much more forgiving.

As far as your solution goes, we can leverage the form already created. What I would do is create another form with a listbox pointing to your Table. Then, if a user double-clicks on a list item, have it launch that first form and load it with that rows details. If the user changes it (i.e. clicking "OK") then update the Table accordingly, otherwise just unload the second form. This is actually a very good exercise if you've never worked with userforms before. I'll code that real quick. In the meantime, study the code and try to step through it so you can see how it works. (Hint: F8 steps through the code. Setting breakpionts (F9) helps as well.)

[Newbie999]

Thank you I will study and try to figure out the code.:yes

[Zack Barresse]

Take a look at the attached. This is a great example of using more than one userform in conjunction with each other. We're using the first form (fAppointment - note singular name) to load an appointment item from fAppointments (note the plural name).

I also took the time to add two properties to fAppointment. This has several advantages. First and foremost, you can load these properties up when calling the form, either by launch or by another form. You can set defaults if you don't want to use the system's (i.e. a Long variable will default with a 0 value, a String will be vbNullString or "", a Boolean will be False, etc.).

While this is starting to get into intermediate VBA, it still serves as a great example for how to use them. If you want to be good with VBA, these are excellent fundamentals to learn early on. Don't worry too much about how to structure custom properties or how to code them, there are tools to help you write them so you don't have to have the exact nomenclature memorized. The thing you need to know is 1) they all have a default based on the [variable] type, 2) you can have a read-only property, 3) you can have a read-write property, 4) you can perform any action within those properties, i.e. "do this when someone gets this property value".

File: 26292

For transparency and ease of consumption, I'll post the code below.

fAppointment properties:

' Variables to hold [value] for writing property
Private pNewAppointment As Boolean
Private pAppointmentIndex As Long


Public Property Get NewAppointment() As Boolean
' Read property

NewAppointment = pNewAppointment

End Property


Public Property Let NewAppointment( _
ByVal Value As Boolean _
)
' Write property

pNewAppointment = Value

End Property


Public Property Get AppointmentIndex() As Long
' Read property

AppointmentIndex = pAppointmentIndex

End Property


Public Property Let AppointmentIndex( _
ByVal Value As Long _
)
' Write property

pAppointmentIndex = Value

End Property

fAppointment button commands:

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub OkButton_Click()

Dim Table As ListObject
Dim DateTime As Date
Dim DatePart As Date
Dim TimePart As Date
Dim Index As Long

On Error Resume Next
DateTime = CDate(Me.tDateTime.Value)
DatePart = Int(DateTime)
TimePart = DateTime - DatePart
On Error GoTo 0

If DateTime = 0 Then
MsgBox "Please enter a date and time.", vbExclamation + vbOKOnly, "Date/Time"
Exit Sub
End If

If Me.tAppointment.Value = vbNullString Then
MsgBox "Please enter an appointment text.", vbExclamation + vbOKOnly, "Appointment"
Exit Sub
End If

Set Table = ThisWorkbook.Worksheets("Calendar").ListObjects("Table2")

If Me.NewAppointment Then
If Table.DataBodyRange Is Nothing Then
Table.ListRows.Add
Me.AppointmentIndex = 1
Else
For Index = 1 To Table.ListRows.Count
If WorksheetFunction.CountA(Table.DataBodyRange(Index, 1).Resize(1, 3)) = 0 Then
Me.AppointmentIndex = Index
Exit For
End If
Next Index
End If
If Index > Table.ListRows.Count Then
Table.ListRows.Add
End If
Else
Index = Me.AppointmentIndex
End If

If Me.AppointmentIndex > 0 Then
Table.DataBodyRange(Me.AppointmentIndex, 1).Value = Format(TimePart, "h:mm AM/PM")
Table.DataBodyRange(Me.AppointmentIndex, 2).Value = Me.tAppointment.Value
Table.DataBodyRange(Me.AppointmentIndex, 3).Value = Format(DatePart, "mm/dd/yyyy")
Unload Me
Else
MsgBox "Something went wrong.", vbExclamation + vbOKOnly, "Whoops!"
End If

End Sub

fAppointments buttom commands:

Private Sub lAppointments_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim Appointment As New fAppointment
Dim DateTime As Date
Dim Index As Long
Dim SelectedIndex As Long

For Index = 0 To Me.lAppointments.ListCount - 1
If Me.lAppointments.Selected(Index) Then
SelectedIndex = Index
Exit For
End If
Next Index
If SelectedIndex = 0 Then Exit Sub

On Error Resume Next
DateTime = Me.lAppointments.List(SelectedIndex, 2) + Me.lAppointments.List(SelectedIndex, 0)
On Error GoTo 0

Load Appointment
Appointment.tDateTime.Value = Format(DateTime, "m/d/yyyy h:mm AM/PM")
Appointment.tAppointment.Value = Me.lAppointments.List(SelectedIndex, 1)
Appointment.NewAppointment = False
Appointment.AppointmentIndex = SelectedIndex + 1
Appointment.Show
Me.Repaint

End Sub


Private Sub CloseButton_Click()

Unload Me

End Sub

Buttom calls/form loads:

Sub ShowAppointments()

Dim Appointments As New fAppointments
Load Appointments
Appointments.Show

End Sub


Sub ShowAddAppointment()

Dim Appointment As New fAppointment
Load Appointment
Appointment.NewAppointment = True
Appointment.Show

End Sub

[Newbie999]

You are amazing at this wow! Can I ask one small favor please? I would prefer the time and date be separate when adding new appointments. I tried to do that but am messing up the coding badly.

Thank you

[Zack Barresse]

Sure, no problem. It's an easy fix.

File: 26294

[Newbie999]

Thank you very much for you great help. I will do my best to understand how this all functions.

Cheers!

[Zack Barresse]

You're very welcome. VBA is a very powerful tool. Post back with any questions on it.

[Newbie999]

Zack,

I ran into issues when trying to call my Protect code. The table that has the appointments where the calendar gets data changes the cell references so the new appointments don't show up. I was wondering if you have the chance to take a look to see if this can be corrected.

Thank you very much

[Zack Barresse]

If you're to the point where you're adding rows to a Table, that must be done on an unprotected worksheet. This is a known bug (Microsoft doesn't consider it a bug, but I do). You have to:


Unprotect sheet
Run your code (add row/data)
Re-protect sheet

[Newbie999]

Yes I was trying to call the Unprotect and then call the Protect codes in your codes but wasn't successful.

[paulked]

Why call the code? just use:



Sheet1.Unprotect
'do stuff...
Sheet1.Protect


with whichever sheet you want to update.

[Zack Barresse]

Ok, I see what you're doing. Don't call the Unprotect in the standard module routines, those which call the userforms. Instead, do it when you touch the grid.

I made three adjustments. Two were for the protection bit, one was a bug in my earlier code - a scenario where a newly added row wouldn't get a proper index/row value and you'd get an error. You would see this scenario if the Table didn't have any blank rows. I have fixed in the below code.


1. In 'Protection' module, add these routines. They give you a targeted approach, rather than blanketing every sheet with protect/unprotect calls.

Public Function ProtectSheet( _
ByVal Sheet As Worksheet, _
Optional ByVal Password As String _
) As Boolean

If Sheet.ProtectContents Then
ProtectSheet = True
Exit Function
End If

On Error Resume Next
Sheet.Protect Password
On Error GoTo 0

If Sheet.ProtectContents Then
ProtectSheet = True
End If

End Function


Public Function UnprotectSheet( _
ByVal Sheet As Worksheet, _
Optional ByVal Password As String _
) As Boolean

If Sheet.ProtectContents = False Then
UnprotectSheet = True
Exit Function
End If

On Error Resume Next
Sheet.UnProtect Password
On Error GoTo 0

If Sheet.ProtectContents = False Then
UnprotectSheet = True
End If

End Function

2. In 'mAppointment' remove your protect/unprotect calls.

3. In 'fAppointment' remove your protect/unprotect calls. Adjust your 'OkButton' routine to the below code.

Private Sub OkButton_Click()

Dim Table As ListObject
Dim DatePart As Date
Dim TimePart As Date
Dim Index As Long

On Error Resume Next
DatePart = CDate(Me.tDate.Value)
TimePart = CDate(Me.tTime.Value)
On Error GoTo 0

If DatePart = 0 Or TimePart = 0 Then
MsgBox "Please enter a date and time.", vbExclamation + vbOKOnly, "Date/Time"
Exit Sub
End If

If Me.tAppointment.Value = vbNullString Then
MsgBox "Please enter an appointment text.", vbExclamation + vbOKOnly, "Appointment"
Exit Sub
End If

Set Table = ThisWorkbook.Worksheets("Calendar").ListObjects("Table2")

UnprotectSheet Table.Parent
If Me.NewAppointment Then
If Table.DataBodyRange Is Nothing Then
Table.ListRows.Add
Me.AppointmentIndex = 1
Else
For Index = 1 To Table.ListRows.Count
If WorksheetFunction.CountA(Table.DataBodyRange(Index, 1).Resize(1, 3)) = 0 Then
Me.AppointmentIndex = Index
Exit For
End If
Next Index
End If
If Index > Table.ListRows.Count Then
Table.ListRows.Add
Me.AppointmentIndex = Index
End If
Else
Index = Me.AppointmentIndex
End If

If Me.AppointmentIndex > 0 Then
Table.DataBodyRange(Me.AppointmentIndex, 1).Value = Format(TimePart, "h:mm AM/PM")
Table.DataBodyRange(Me.AppointmentIndex, 2).Value = Me.tAppointment.Value
Table.DataBodyRange(Me.AppointmentIndex, 3).Value = Format(DatePart, "mm/dd/yyyy")
Unload Me
Else
MsgBox "Something went wrong.", vbExclamation + vbOKOnly, "Whoops!"
End If

ProtectSheet Table.Parent

End Sub

[Zack Barresse]

@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]

Thanks Zack I did all the steps and tried to add a new appointment. Excel shuts down when clicking the Ok button.

[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.

:thumb

[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.
:thumb

[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: 26313

[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. :thumb