Date inserted on userform wont work

[elynoy]

Hello once again.

I have a user form that gets data from one sheet and i want to edit that data and save it back to the same exact place.

the code I have works fine but while saving from the userform to the cell it wont work.

Here's the code I have to populate the userform:

Private Sub UserForm_Initialize()    Me.StartUpPosition = 0
    Me.Top = (Application.Height - Me.Height) / 2
    Me.Left = (Application.Width - Me.Width - 500)
    
    
'first tab


Me.tbA1i = sheets("ferias").Range("D7").Value
Me.tbA1f = sheets("ferias").Range("E7").Value


'second tab


Me.tbA2i = sheets("ferias").Range("D8").Value
Me.tbA2f = sheets("ferias").Range("E8").Value

End Sub

It's a 2 pages userform.

the code for the save button:

Private Sub CommandButton1_Click()    
    sheets("ferias").Range("D7").Value = Me.tbA1i
    sheets("ferias").Range("E7").Value = Me.tbA1f
    sheets("ferias").Range("D7").Value = Me.tbA2i
    sheets("ferias").Range("E7").Value = Me.tbA2f

    
End Sub

The problem is, what I'm typing into the userform are dates. 01-01-2019 for exemple. the userform inserts the right date exactly how I type inside the userform but then I have a condition formating and it wont assume the date entered in the respective cell unless I type manually inside that cell. Then the conditional formatting will work.

this is what I'm trying to achieve:

If I type the dates 01-01-2019 and 15-01-2019 with the userform it wont turn green.

Best regards,
eLy

 

[Fluff]

Ok, try this

Private Sub CommandButton4_Click()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(tbA1i, "D7", tbA1f, "E7", tbB1i, "D9", tbB1f, "E9", tbC1i, "D11", tbC1f, "E11")
'parte 1 botão primeira página
   With Sheets("ferias")
      For i = 0 To UBound(Ary) Step 2
         If Ary(i).Value <> "" Then
            .Range(Ary(i + 1)).Value = CDate(Ary(i))
         End If
      Next i
   End With
End Sub

 

[elynoy]

with that the error wont show up but i got another issue.if i delete the dates in the userform and prss save it wont save , i mean wont clear the cell. and the dd-mm-yyyy wont work either it insists on loading mm-dd-yyyy

 

[Fluff]

Let's just concentrate on one problem at a time.
Does the above code do you what you need?

 

[elynoy]

if you're asking if it inserts the dates where it should, yes. it works. but not how it's supposed to work.

 

[Fluff]

How is it supposed to work & in what way isn't it?

 

[elynoy]

I'll try to writhe the steps on how I tested it:

press button with userform.show
insert dates on the tbA1i and tbA1f
press save button and it saves correctly, the color change according to the conditional rule.
close the userform and load it again.
The dates returns to the mm-dd-yyyy instead of dd-mm-yyyy
insert a new date on tbB1i and tbB1f
it messes up because of the date change to mm-dd-yyyy
close the form and load it again the dates show correctly on the userform as dd-mm-yyyy
close and open agian, it messes up the dd-mm-yyyy agian and so on

it needs to be like this:
tbA1i start date and tbA1f end date
displayed always as dd-mm-yyyy on cell and userform

 

[Fluff]

The date format is completely irrelevant at the moment.
Does the code in post#21 put the dates into the correct cells without any errors?

 

[elynoy]

yes it puts the correct dates in them

 

[Fluff]

Ok Try this

[COLOR=#0000ff]Option Explicit
Dim CtrlAry As Variant[/COLOR]

Private Sub CommandButton4_Click()
   Dim i As Long
   
'parte 1 botão primeira página
   With Sheets("ferias")
      For i = 0 To UBound(CtrlAry) Step 2
         If CtrlAry(i).Value <> "" Then
            .Range(CtrlAry(i + 1)).Value = CDate(CtrlAry(i))
         End If
      Next i
   End With
End Sub
Private Sub UserForm_Initialize()
   Dim i As Long
    
   CtrlAry = Array(tbA1i, "D7", tbA1f, "E7", tbB1i, "D9", tbB1f, "E9", tbC1i, "D11", tbC1f, "E11")

   Me.StartUpPosition = 0
   Me.Top = (Application.Height - Me.Height) / 2
   Me.Left = (Application.Width - Me.Width - 500)
    
    
'PÁGINA 1 INICIO E FIM
   
   For i = 0 To UBound(CtrlAry) Step 2
      With Sheets("ferias")
         CtrlAry(i).Value = Format(.Range(CtrlAry(i + 1)).Value, "dd/mm/yyyy")
      End With
   Next i
End Sub

The two lies in blue must go at the very top of the module, before any code.You will need to turn your existing code into comments

 

[elynoy]

that one inserts it and loads it correctly.