VB error relating to Textbox format

[redspanna]

Hi all

when my userform is activated this code kicks in

Private Sub UserForm_Activate()

        With Worksheets("database")
        TextBox2 = Format(.Cells(.Rows.Count, "b").End(xlUp).Value, "ddmmmyy")
        
    End With
End Sub

when the user inputs all the data into the userform and then selects the add button, all the Textboxes, ComboBoxes etc are copied to a sheet. However the code sticks with an error 'type mismatch on this code

With ws.Cells(iRow, 2)
    .NumberFormat = "ddmmmyy"
    .Value = CDate(Me.TextBox2.Value)
     TextBox2 = Format(.Cells(.Rows.Count, "b").End(xlUp).Value, "ddmmmyy")

with

.Value = CDate(Me.TextBox2.Value)

being the highlighted error part

can this be fixed to stop the error?
Many thanks in advance

 

[JamesW]

Hi,

What are you entering into the textbox? CDate expects a date (something like 01/01/2011).

 

[redspanna]

Hi James
The textbox is auto populated by the last found value in column B of the database sheet.
This column is formatted as a Custom value formatted ddMmmyyyy
So if I click on the last value in column B at the moment it shows the value in the view bar as 04/05/2011 however the actual value in the cell is 04May2011

thanks

 

[JamesW]

Off the top of my head, try:

    .Value = Left(TextBox1.Value, 2) & "/" & Mid(TextBox1.Value, 3, 3) & "/" & Right(TextBox1.Value, 4)

The textbox doesn't see 01May2011 as a date.

 

[redspanna]

Cheers, thanks