Strange Userform date problem

[hatstand]

I have a range of cells formatted so that the dates show as # symbols as the columns are narrow. Which is what I want.

My Userform includes txtboxes as dates. It pulls the data from the cells no problem. But when I press my replace button. It pastes the data back into the correct cell but the dates are now visible no matter how narrow the columns. Obviously the dates overlap in adjacent cells.

If I press F2 and enter the cells go back to the correct format.

It’s got me stumped.

 

[Andrew Poulsom]

Please post the code for your Replace button. It should be something like:

ActiveCell.Value = DateValue(TextBox1.Text)

At the moment a text string that looks like a date is being transferred to the worksheet.

 

[hatstand]

Thanks for the help.

I've taken out a few rows for different textboxes to shrink it down a bit.

I didn't write the code, I only modified it to suit my needs.

Private Sub cmdReplace_Click()

Dim NumRepl
Dim NNumRepl
Dim NBARepl
Dim NPriceRepl

Dim NtestRepl

Dim r As Range



Dim BARepl As String
Dim PriceRepl As String
Dim NameRepl As String
Dim faddress As String


Dim testRepl As String



With Me
NameRepl = .Listbox1.Column(0)
NumRepl = .Listbox1.Column(1)
NNumRepl = .txtProductNumber
BARepl = .Listbox1.Column(2)
NBARepl = .txtBA
PriceRepl = .Listbox1.Column(3)
'New------------------------------------------------------
testRepl = .Listbox1.Column(4)





'New--------------------------------------------------
NtestRepl = .txttest



NPriceRepl = .txtPrice
End With

With Sheets("Staff_Database").Range("ProductRange")
Set r = .Find(What:=NameRepl, LookAt:=xlValue, MatchCase:=False)
If Not r Is Nothing Then
faddress = r.Address
Do
'New-----------------------------------------------------
If r.Offset(0, 1) = NumRepl _
And r.Offset(0, 2) = BARepl _
And r.Offset(0, 3) = PriceRepl _
And r.Offset(0, 4) = testRepl


r = Me.txtDescription
r.Offset(0, 1) = NNumRepl
r.Offset(0, 2) = NBARepl
r.Offset(0, 3) = NPriceRepl

r.Offset(0, 4) = NtestRepl

Exit Do
End If
Set r = .FindNext(r)
Loop While Not r Is Nothing And r.Address <> faddress
Else
MsgBox "This Name Has Not Been Found, Please Try Again", vbInformation + vbOKOnly, "Name Not Found"
Me.txtSearch = ""
Me.txtSearch.SetFocus
End If
End With
With Me
.txtSearch.Value = ""
.txtBA.Value = ""
.txtDescription.Value = ""
.txtPrice.Value = ""
.txtProductNumber.Value = ""
'New-------------------------------------------------------
.txttest.Value = ""

.Listbox1.Clear
.cmbAdd.Enabled = True
.cmdSearch.Enabled = True
.cmdDelete.Enabled = False
.cmdReplace.Enabled = False
End With
End Sub

 

[Andrew Poulsom]

So which control contains the date?

 

[hatstand]

Really sorry about that.

It's, txttest

 

[Andrew Poulsom]

Try replacing:

NtestRepl = .txttest

with:

NtestRepl = DateValue(.txttest.Value)

 

[hatstand]

I'm afraid it didn't work. Would I need to change every occurance of the line within the whole module?

 

[RoryA]

you need to change it where you write the value to the sheet:

r.Offset(0, 4) = CDate(NtestRepl)

 

[hatstand]

I'm afrid it hasn't worked. It turns this line yellow:

NhoursRepl = DateValue(.txthours.Value)

Am I doing something wrong?

 

[Andrew Poulsom]

I'm afrid it hasn't worked. It turns this line yellow:

NhoursRepl = DateValue(.txthours.Value)

Am I doing something wrong?

Wher has txthours come from? Previously it was txttest.