Update worksheet cell from user form entry

[EssKayKay]

Hello,
I’m not sure this is apropos as this is actually an extension of a previous post. However, I’d like to change direction on a bit. If this is frowned upon please let me know.

I don’t know if this is even possible but I’ll try to explain what I have in mind.
I have a form (frmPITI) with three text boxes and one command button.
txtYEAR, txtYTD, txtDistribution, & cmdSubmit

What I’d like is if after a date is entered into form’s txtDistribution box then when the cmdSubmit button is clicked the cell in column T that corresponds to the YEAR in column R that matches the YEAR in the form would be updated to the date in the entered in the form.

Example:
If date 12/09/25 is entered into the form’s txtDistribution box, when the cmdSubmit button is clicked, cell T34 would be populated with 12/09/25. That is, the Year in column R because matches the Year in the forms txtYEAR box (both 2025). Column R could be as long as 100 years (albeit I doubt that would ever happen).

As a test, I tried something like this but was not successful.

Private Sub cmdSubmit_Click()
   If txtDistribution > 0 Then
     MsgBox "Submit It"
   Else
     MsgBox "Do Nothing"
   End If
End Sub

I should note I tried setting up a table but this old guy could not get it to work. So I was hoping something like this would be easier (at least for me).

Thanks for viewing,
Steve K.

 

[Jeffrey Mahoney]

How about this as a start? I created a named range called PITIYearHdr that references Cell R32 (the year header). The macro uses that.

Private Sub cmdSubmit_Click()
  Dim PITIYearHdr As Range
  Dim Rng As Range
  Dim Cel As Range
  Dim Yr As Long
  Dim Amnt As Single
  Dim DistDate As Date
  Dim Sht As Worksheet
  
  On Error Resume Next
  Yr = txtYEAR.Value
  Amnt = txtYTD.Value
  DistDate = txtDistribution.Value
  
  If Yr = 0 Or Amnt = 0 Or DistDate = 0 Then
    MsgBox "Please fill in the form before submitting"
    Exit Sub
  End If
  
  Set Sht = Sheets("Sheet1")
  Set PITIYearHdr = Sht.Range("PITIYearHdr")
  Set Rng = Sht.Range(PITIYearHdr, PITIYearHdr.End(xlDown))
  For Each Cel In Rng
    If Cel.Value = Yr Then
      Cel.Offset(0, 1).Value = Amnt
      Cel.Offset(0, 2).Value = DistDate
      Exit For
    End If
  Next Cel
  
  Unload frmPITI
  
  
End Sub

 

[jolivanes]

If the amount in Column S needs to be the same as txtYTD.

Private Sub cmdSubmit_Click()
If txtDistribution <> "" Then
    With Sheets("Sheet1").Columns(18).Find(txtYear, , , 1) '<---- Change as required
        If .Offset(, 1).Value = txtYTD Then .Offset(, 2).Value = txtDistribution
    End With
End If
End Sub

 

[EssKayKay]

Thank you Jeffrey for your code. This appears to be doing what I want. I’m having an issue with the DATE formatting of my txtDistribution box but I will work on that.

Jolivanes – Yes the amounts in column S and txtYTD should be the same. However, I think that will just happen but I will check into this. If need be I will “try to” incorporate your code as a fix. I appreciate your insight. Much appreciated.

Again, thank you both,
Steve

 

[Jeffrey Mahoney]

My date format is 10/14/2024 (mm/dd/yyyy)

 

[EssKayKay]

My date format is 10/14/2024 (mm/dd/yyyy)

I'm back,

For the most part this is starting to look pretty good. There’s still much to do but for right now I’m having a problem formatting the txtDistribution box to date (dd/mm/yyyy). I tried adding this code:

Apparently however, I am still doing something wrong as this does not work. When I attempt to keyin a date into the txtDistribution box it immediately displays an old date (I assume corresponding to the date of the single number I keyed in).

Example:
If I want to keyin today’s date (8/14/24) as soon as I enter the 8 the txtDistribution box displays 07/01/1900.

Any suggestion?
Thanks again,
Steve K.

 

[EssKayKay]

OOPS - actually reformatted as "mm/dd/yyyy" which returns 07/01/1900

Private Sub txtDistribution_Change()
    txtDistribution.Value = Format(txtDistribution.Value, "mm/dd/yyyy")
End Sub

 

[Jeffrey Mahoney]

Hey, I found some code that might help

Private Sub txtDistribution_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(txtDistribution.Text) Then
        MsgBox "Date required"
        Cancel = True
    End If
    'Display value in another textbox for testing purposes
    txtDistribution.Text = Format(CDate(txtDistribution.Text), "dd/mm/yyyy")
End Sub

 

[EssKayKay]

Hey, I found some code that might help

Private Sub txtDistribution_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(txtDistribution.Text) Then
        MsgBox "Date required"
        Cancel = True
    End If
    'Display value in another textbox for testing purposes
    txtDistribution.Text = Format(CDate(txtDistribution.Text), "dd/mm/yyyy")
End Sub

Thank you Jeffery for your response and suggestion. I actually did see that posting (or one very similar). I tried it and yours; however, it has not helped. The textbox txtDistribution still does not display the date in mm/dd/yyyy format.

What does happen is when I run your cmdSubmit routine it updates the spreadsheet cell in column “T” as I was hoping; that’s great. However, in addition to that, what I was hoping for is when a date is entered into the form’s txtDistribution box, it displays in mm/dd/yyyy format. That is, if I enter 8/14 (leaving off the year) it will display 08/14/2024. What’s happening is txtDistribution continues to only display 8/14. However, the corresponding cell in column “T” in the worksheet displays 08/14/2024 which is what I want (perfect). So all I want is for txtDistribution box to display a full date (i.e., mm/dd/yyyy or as in my example 08/14/2024).

Maybe this is just not possible. I may have a workaround here by having a message box display what is being posted or another textbox that displays what is in the corresponding cell in column “T”. I was hoping this would be easier.

Again Jeffrey, thank you so very much for your concern, suggestions, and support.
Steve K.

 

[EssKayKay]

I think I have a workaround. I found some code online that populates a second textbox (txtTEMP) with the data entered in the first box (txtDistribution). The txtTEMP box is formatted as dd/mm/yyyy and displays accordingly.

I have code then that does the following:

1. When the UserForm opens, txtTEMP is hidden (txtTEMP.visible=False)

2. I added a Sub txtDistribution_AfterUpdate() which hides txtDistribution and unhides txtTEMP
txtDistribution.Visible = False
txtTEMP.Visible = True

3. On the forms Initialize routine I do just the opposite of #2. When the form opens,
txtDistribution.Visible = True
txtTEMP.Visible = False

txtDistribution and txtTEMP are positioned on top of each other so to the user it appears as the textbox data changes rather then being hidden etc..
I have more testing to do but it appears to be working. I assume this is not good coding practice but unless there is some easier solution, I’m OK with this.

Thanks again all for your time. I’m sure I’ll be back asking (read begging) for more assistance with other issues.
Steve K.