Writing dates into cells with Userform (Textbox)

[thewrestler723]

Hi all,

I'm trying to create a data entry form for a colleague, and don't understand why this is happening: I have fields for first name, last name, and two different date fields (WL date and active LA date). I am looking to have the entered data entered into a sheet with other data that's already sorted alphabetically by last name. My code is properly finding the correct spot for the new data based on alphabetic order, and inserts a new row and the first and last names fine. But it simply doesn't do anything when the lines to write the dates into cells is run. I ran the code in "Step-Into" mode and the lines that are supposed to write the dates to the sheet run without error, but nothing happens when they run. Any suggestions? I am quite new to VBA.

Also, I'm wondering if there's some way to write formulas into cells using VBA? I tried to do so by writing a string of the formula into a cell but it's also not doing anything when it runs (Please forgive me if me using ActiveCell to reference a cell and create a formula is a dumb way to do it, lol).

Note: I've tried to label all my text boxes intuitively, their name reflects the value they hold.

Private Sub SubmitButton_Click()

'Prepare sorting process by activating top of column
Range("D7").Activate

'Store entered last name in str1 variable
Dim str1 As String
str1 = LastNameTextBox

'Loop thru all values alphabetically lower than the entered string to find correct spot in column
While str1 > ActiveCell.Value
    ActiveCell.Offset(1, 0).Activate
Wend

'Insert row and write data to sheet
ActiveCell.EntireRow.Insert
ActiveCell.Value = str1
ActiveCell.Offset(0, -1).Value = FirstNameTextBox.Value

'Everything above works fine, everything below runs without error but simply does nothing, the cells I reference are just blank after running
ActiveCell.Offset(0, 1).Value = WLDateTextBox.Value
ActiveCell.Offset(0, 3).Value = ActiveLATextBox.Value
ActiveCell.Offset(0, 4).Value = "=" & ActiveCell.Offset(0, 3) & "-" & ActiveCell.Offset(0, 1)

Unload Me
End Sub

 

[dmt32]

Hi,
welcome to forum
Had a little play with your code & all textboxes posted data to the Active worksheet. Also, rather than searching where to insert each record in Alphabetical order, consider using the Range.Sort method which will allow you to sort on both LastName & FirstName columns

Assuming your data is laid out like this

16-07-2022.xls
	C	D	E	F	G	H
6	First	Surname	WLDate		ActiveLA	No Days
7	Chris	Evans	01/07/2022		31/07/2022	30
8	Anne	Hathaway	01/04/2022		30/04/2022	29
9	Brad	Pitt	01/12/2022		27/12/2022	26
10	Emma	Watson	01/02/2022		28/02/2022	27
11	Kate	Winslet	01/09/2022		20/09/2022	19
Sheet1

and the sheet data is posted to is the Activesheet which is NOT protected - then see if this update to your code will do what you want

Private Sub UserForm_Initialize()
    EnableSubmitButton
End Sub

Private Sub ActiveLATextBox_Change()
    EnableSubmitButton
End Sub

Private Sub WLDateTextBox_Change()
    EnableSubmitButton
End Sub

Sub EnableSubmitButton()
    Me.SubmitButton.Enabled = IsDate(Me.WLDateTextBox.Value) And IsDate(Me.ActiveLATextBox.Value)
End Sub

Private Sub SubmitButton_Click()

    Dim rng         As Range, nextrow As Range
  
    'header row value
    Const HeaderRow As Long = 6
  
    Set rng = ActiveSheet.Cells(HeaderRow, 3).CurrentRegion
    'get next blank row
    Set nextrow = rng(rng.Cells.Count + 1)
  
    'add new record
    With nextrow
        .Value = FirstNameTextBox.Value
        .Offset(, 1) = LastNameTextBox.Value
        .Offset(, 2) = DateValue(WLDateTextBox.Value)
        .Offset(, 4) = DateValue(ActiveLATextBox.Value)
        .Offset(, 5).Formula = "=" & .Offset(, 4).Address(0, True) & "-" & .Offset(, 2).Address(0, True)
    End With
  
    'sort lastname / firstname
    rng.Resize(nextrow.Row, 6).Sort key1:=Cells(HeaderRow, 4), Order1:=xlAscending, _
                                    Key2:=Cells(HeaderRow, 3), Order2:=xlAscending, Header:=xlYes
                                  
    Unload Me
  
End Sub

I included additional checking to ensure both date textboxes have a valid date entered before record can be submitted.

Hope Helpful

Dave