Userform to update worksheet with date format and also text.

[BobH]

Hi I am trying to create a user sheet that updates the dates or the information in a column of a Worksheet. I have a code that allows me to change and update in uk date format, but if i want to leave the cell blank or add Text I get an error message and the userform will not update the worksheet. I have just started using VBA and I cant workout a code to allow me to add both date & text. This is my code so far would be grateful for any advice.

Private Sub CommandButton1_Click()
Dim i As Long, LastRow As Long
Set sh = ThisWorkbook.Sheets("Mandatory")
LastRow = Sheets("Mandatory").Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To LastRow
If Sheets("Mandatory").Cells(i, 1).Value = (Me.ComboBox3) Or _
Sheets("Mandatory").Cells(i, 1).Value = Val(Me.ComboBox3) Then
Sheets("Mandatory").Cells(i, 5) = CDate(TextBox4.Value)
End If
Next i
End Sub

Regards Bob

 

[dmt32]

Hi,
welcome to forum

Just test for a date value in your textbox & take appropriate action based on result

Private Sub CommandButton1_Click()
    Dim i As Long, LastRow As Long
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Sheets("Mandatory")
    LastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    
    For i = 4 To LastRow
        With sh.Cells(i, 1)
            If IsDate(TextBox4.Value) Then
                If .Value = Val(Me.ComboBox3.Value) Then .Offset(, 4).Value = CDate(TextBox4.Value)
            Else
                If .Value = Me.ComboBox3.Value Then .Offset(, 4).Value = TextBox4.Value
            End If
        End With
    Next i
End Sub

Code not tested & Logic may need adjusting to meet your project need but hopefully points you in right direction

Dave

 

[BobH]

Thank you Dave the code you gave me allows me to enter Text or leave the cell blank which is perfect, but it will not allow me to amend or enter a date on the worksheet now. Thanks again I will look to see if I can change the code to work Much appreciated. Bob

 

[dmt32]

Thank you Dave the code you gave me allows me to enter Text or leave the cell blank which is perfect, but it will not allow me to amend or enter a date on the worksheet now. Thanks again I will look to see if I can change the code to work Much appreciated. Bob

Glad update helps but as stated, the logic of the tests will need to be adjusted to meet specific project need but if cannot resolve yourself, post back, plenty here to assist.

Dave

 

[BobH]

Hi Dave
I changed the your code slightly to the following and it works perfect. Thankyou.

Private Sub CommandButton1_Click()
    Dim i As Long, LastRow As Long
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Sheets("Mandatory")
    LastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    
    For i = 4 To LastRow
        With sh.Cells(i, 1)
            If IsDate(TextBox4.Value) Then
                If .Value = Me.ComboBox3.Value Then .Offset(, 4).Value = CDate(TextBox4.Value)
            Else
                If .Value = Me.ComboBox3.Value Then .Offset(, 4).Value = TextBox4.Value
                    
        
               End If
        End With
    Next i
End Sub

The worksheet I am working on will have the same type of data linked from ComboBox3.value(as above) , retrieved from the next column on the worksheet (Offset(,5), and displayed in TextBox5 on the userform.
I have tried to duplicate the above code change the offset and textbox but the data was not changed/updated on the Worksheet. How do I move from updating the data from Textbox4 on the userform to do the same from Textbox5

Regards Bob

 

[BobH]

Typing out my last response solved the issue Thank you again

Bob

 

[dmt32]

Typing out my last response solved the issue Thank you again

Bob

Always better when figure out for yourself
Glad suggestion helped you & thanks for feedback

Dave