Replace table with Userform

Hi,

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

Thanks

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.
Attachment 26288

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?

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.

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.

Hi again,

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

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

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

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: Attachment 26292

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

fAppointment properties:

Code:

---

' 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:

Code:

---

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:

Code:

---

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:

Code:

---

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

---

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

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

File: Attachment 26294

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

Cheers!

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

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

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

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

Why call the code? just use:

Code:

---

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

---

with whichever sheet you want to update.

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.

Code:

---

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.

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

---

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

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

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

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?

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.

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?

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.

Quote:

---

Originally Posted by 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.

---

:thumb

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

Quote:

---

Originally Posted by 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.

---

:thumb

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 :(

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

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

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

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.

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.

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