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]

Is everything now working for those few textboxes?

 

[elynoy]

yes, the they all insert the date correctly and loads correctly on all of them

 

[Fluff]

In that case all you need to do is expand the array

 CtrlAry = Array(tbA1i, "D7", tbA1f, "E7", tbB1i, "D9", tbB1f, "E9", tbC1i, "D11", tbC1f, "E11")

to include the other textboxes & the relevant range, in the same way.

 

[elynoy]

already did that, and the same for the second page of the userform, how do I implement the second page on the array?
and by the way, how to save it as a blank cell? since if i delete a date from the userform and press save it wont save as empty, only saves the date or another date.

 

[Fluff]

This will clear the cell if the textbox is blank

      For i = 0 To UBound(CtrlAry) Step 2
         If CtrlAry(i).Value = "" Then
            .Range(CtrlAry(i + 1)).ClearContents
         Else
            .Range(CtrlAry(i + 1)).Value = CDate(CtrlAry(i))
         End If
      Next i

I'm not sure what you mean by the second page on the array.
You just need one array for all textboxes,ranges

 

[elynoy]

the userform has 2 pages. that array, I just need to add the rest of the textboxes for the secon d page of the userform?

 

[Fluff]

With everything in the one array it works for me with a multipage userform

 

[elynoy]

your last code for clearing the contents if the cell is empty gives me an error on .Range. It says invalid or unqualified reference.

Edit: Nevermind, I was adding it to the wrong place. now I just need to add the rest of the textboxes to the array, it's too big it goes down a line and wont work. how can I "divide" so it goes down one line on the vba with "& _ "?

 

[elynoy]

Another thing that I noticed now is that it takes too long to save. like it the excel is about to crash, everything freezes

 

[Fluff]

To split the array over multiple lines do it like

   CtrlAry = Array(tbA1i, "D7", tbA1f, "E7", tbB1i, "D9", _
      tbB1f, "E9", tbC1i, "D11", tbC1f, "E11", tb1, "D8", tb2, "E8")

To speed up the code try

Private Sub CommandButton1_Click()
   Dim i As Long
   
   With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
      .EnableEvents = False
   End With
'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)).ClearContents
         Else
            .Range(CtrlAry(i + 1)).Value = CDate(CtrlAry(i))
         End If
      Next i
   End With
   With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
   End With

End Sub