Date inserted on userform wont work

E

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
160
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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:
Code: 
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:
Code: 
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:

s6lqo0.png






10ynt45.png


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

Best regards,
eLy
 
Last edited:
Sort by date Sort by votes
I don't understand what you mean by
Do I need to make all textboxes now or I can use just 1 to test?
Click to expand...
are you saying that you haven't yet created the textboxes?

Regarding
Doing it to tast only on one gives me the same error.
Click to expand...
The code in post#9 for the command button, will not give you any errors.


Finally
and the userform initialize doesnt load anything
Click to expand...
can you post your amended code.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
This is the save button:
Code: 
Private Sub CommandButton4_Click()
   On Error Resume Next
   sheets("ferias").Range("D7").Value = CDate(Me.tbA1i)
   sheets("ferias").Range("E7").Value = CDate(Me.tbA1f)
   sheets("ferias").Range("D9").Value = CDate(Me.tbB1ii) 'changed from tbA2i to Me.tbB1i for testing
   sheets("ferias").Range("E9").Value = CDate(Me.tbB1ff) 'changed from tbA2f to tbB1f  for testing
   On Error GoTo 0
End Sub

This is the code from initialize:
Code: 
Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = (Application.Height - Me.Height) / 2
    Me.Left = (Application.Width - Me.Width - 500)
    
    
'PARTE 1 INICIO E FIM
Me.tbA1i = Format(Me.tbA1i, "dd/mm/yyyy")
'Me.tbA1i = sheets("ferias").Range("E7").Value
Me.tbA1f = sheets("ferias").Range("D7").Value
Me.tbB1ii = sheets("ferias").Range("D9").Value 'changed for testing
Me.tbB1ff = sheets("ferias").Range("E9").Value 'changed for testing

End Sub

this is the result of that initialize code:
2il2ejk.png


this part
Code: 
Me.tbA1i = Format(Me.tbA1i, "dd/mm/yyyy")
wont load at all. I must be doing something wrong for sure.

I didnt do all the text boxes because they are over 60, thats why I asked for testing if I needed to do it for all of them or I could make one work correctly and then change the code for all of them.

best regards,
eLy
 
Last edited:
Upvote 0
This line
Code: 
Me.tbA1i = Format(Me.tbA1i, "dd/mm/yyyy")
needs to go at the end of your code, after you have assigned values.
 
Upvote 0
oh, I got it now. Thank you once again!

Best regards,
eLy
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Sorry, but, the problem error type 13 mismcth still occurs if there are cells without dates inside the userform.
 
Upvote 0
Where does that error occur?
 
Upvote 0
this part :
Code: 
sheets("ferias").Range("D7").Value = CDate(Me.tbA1i)
on the first blank cell wich is empty
 
Upvote 0
If that is in the command button click event, then you are not using the code I supplied, or the code that you showed in post#12.
Can you please post the complete code for the procedure that line comes from.
 
Upvote 0
ok, I'll post everything on the userform: it's not finished yet and there's alot or commented stuff
Code: 
Private Sub CommandButton4_Click()On Error Resume Next


'parte 1 botão primeira página
   sheets("ferias").Range("D7").Value = CDate(Me.tbA1i)
   sheets("ferias").Range("E7").Value = CDate(Me.tbA1f)
   sheets("ferias").Range("D9").Value = CDate(Me.tbB1i)
   sheets("ferias").Range("E9").Value = CDate(Me.tbB1f)
   sheets("ferias").Range("D11").Value = CDate(Me.tbC1i)
   sheets("ferias").Range("D11").Value = CDate(Me.tbC1f)
   'sheets("ferias").Range("D13").Value = CDate(Me.tbD1i)
   'sheets("ferias").Range("E13").Value = CDate(Me.tbD1f)
   'sheets("ferias").Range("D15").Value = CDate(Me.tbE1i)
   'sheets("ferias").Range("E15").Value = CDate(Me.tbE1f)
   'sheets("ferias").Range("D17").Value = CDate(Me.tbF1i)
   'sheets("ferias").Range("E17").Value = CDate(Me.tbF1f)
   'sheets("ferias").Range("D19").Value = CDate(Me.tbG1i)
   'sheets("ferias").Range("E19").Value = CDate(Me.tbG1f)
   'sheets("ferias").Range("D21").Value = CDate(Me.tbH1i)
   'sheets("ferias").Range("E21").Value = CDate(Me.tbH1f)
   'sheets("ferias").Range("D23").Value = CDate(Me.tbI1i)
   'sheets("ferias").Range("E23").Value = CDate(Me.tbI1f)
   'sheets("ferias").Range("D25").Value = CDate(Me.tbJ1i)
   'sheets("ferias").Range("E25").Value = CDate(Me.tbJ1f)
   'sheets("ferias").Range("D27").Value = CDate(Me.tbK1i)
   'sheets("ferias").Range("E27").Value = CDate(Me.tbK1f)
   'sheets("ferias").Range("D29").Value = CDate(Me.tbL1i)
   'sheets("ferias").Range("E29").Value = CDate(Me.tbL1f)
   'sheets("ferias").Range("D31").Value = CDate(Me.tbM1i)
   'sheets("ferias").Range("E31").Value = CDate(Me.tbM1f)
   'sheets("ferias").Range("D33").Value = CDate(Me.tbN1i)
   'sheets("ferias").Range("E33").Value = CDate(Me.tbN1f)
   'sheets("ferias").Range("D35").Value = CDate(Me.tbO1i)
   'sheets("ferias").Range("E35").Value = CDate(Me.tbO1f)
   'sheets("ferias").Range("D37").Value = CDate(Me.tbP1i)
   'sheets("ferias").Range("E37").Value = CDate(Me.tbP1f)
   'sheets("ferias").Range("D39").Value = CDate(Me.tbQ1i)
   'sheets("ferias").Range("E39").Value = CDate(Me.tbQ1f)
   'sheets("ferias").Range("D41").Value = CDate(Me.tbR1i)
   'sheets("ferias").Range("E41").Value = CDate(Me.tbR1f)
   'sheets("ferias").Range("D43").Value = CDate(Me.tbS1i)
   'sheets("ferias").Range("E43").Value = CDate(Me.tbS1f)
   'sheets("ferias").Range("D45").Value = CDate(Me.tbT1i)
   'sheets("ferias").Range("E45").Value = CDate(Me.tbT1f)
   'sheets("ferias").Range("D47").Value = CDate(Me.tbU1i)
   'sheets("ferias").Range("E47").Value = CDate(Me.tbU1f)
   'sheets("ferias").Range("D49").Value = CDate(Me.tbV1i)
   'sheets("ferias").Range("E49").Value = CDate(Me.tbV1f)
   'sheets("ferias").Range("D51").Value = CDate(Me.tbW1i)
   'sheets("ferias").Range("E51").Value = CDate(Me.tbW1f)
   'sheets("ferias").Range("D53").Value = CDate(Me.tbX1i)
   'sheets("ferias").Range("E53").Value = CDate(Me.tbX1f)
   'sheets("ferias").Range("D55").Value = CDate(Me.tbY1i)
   'sheets("ferias").Range("E55").Value = CDate(Me.tbY1f)
   'sheets("ferias").Range("D57").Value = CDate(Me.tbZ1i)
   'sheets("ferias").Range("E57").Value = CDate(Me.tbZ1f)


   
   'parte 2 botão segunda página
   
On Error GoTo 0


End Sub


Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = (Application.Height - Me.Height) / 2
    Me.Left = (Application.Width - Me.Width - 500)
    
    
'PÁGINA 1 INICIO E FIM




Me.tbA1i = sheets("ferias").Range("D7").Value
Me.tbA1f = sheets("ferias").Range("E7").Value
Me.tbB1i = sheets("ferias").Range("D9").Value
Me.tbB1f = sheets("ferias").Range("E9").Value
Me.tbC1i = sheets("ferias").Range("D11").Value
Me.tbC1f = sheets("ferias").Range("E11").Value
'Me.tbD1i = sheets("ferias").Range("D13").Value
'Me.tbD1f = sheets("ferias").Range("E13").Value
'Me.tbE1i = sheets("ferias").Range("D15").Value
'Me.tbE1f = sheets("ferias").Range("E15").Value
'Me.tbF1i = sheets("ferias").Range("D17").Value
'Me.tbF1f = sheets("ferias").Range("E17").Value
'Me.tbG1i = sheets("ferias").Range("D19").Value
'Me.tbG1f = sheets("ferias").Range("E19").Value
'Me.tbH1i = sheets("ferias").Range("D21").Value
'Me.tbH1f = sheets("ferias").Range("E21").Value
'Me.tbI1i = sheets("ferias").Range("D23").Value
'Me.tbI1f = sheets("ferias").Range("E23").Value
'Me.tbJ1i = sheets("ferias").Range("D25").Value
'Me.tbJ1f = sheets("ferias").Range("E25").Value
'Me.tbK1i = sheets("ferias").Range("D27").Value
'Me.tbK1f = sheets("ferias").Range("E27").Value
'Me.tbL1i = sheets("ferias").Range("D29").Value
'Me.tbL1f = sheets("ferias").Range("E29").Value
'Me.tbM1i = sheets("ferias").Range("D31").Value
'Me.tbM1f = sheets("ferias").Range("E31").Value
'Me.tbN1i = sheets("ferias").Range("D33").Value
'Me.tbN1f = sheets("ferias").Range("E33").Value
'Me.tbO1i = sheets("ferias").Range("E35").Value
'Me.tbO1f = sheets("ferias").Range("E35").Value
'Me.tbP1i = sheets("ferias").Range("D37").Value
'Me.tbP1f = sheets("ferias").Range("E37").Value
'Me.tbQ1i = sheets("ferias").Range("D39").Value
'Me.tbQ1f = sheets("ferias").Range("E39").Value
'Me.tbR1i = sheets("ferias").Range("D41").Value
'Me.tbR1f = sheets("ferias").Range("E41").Value
'Me.tbS1i = sheets("ferias").Range("D43").Value
'Me.tbS1f = sheets("ferias").Range("E43").Value
'Me.tbT1i = sheets("ferias").Range("D45").Value
'Me.tbT1f = sheets("ferias").Range("E45").Value
'Me.tbU1i = sheets("ferias").Range("D47").Value
'Me.tbU1f = sheets("ferias").Range("E47").Value
'Me.tbV1i = sheets("ferias").Range("D49").Value
'Me.tbV1f = sheets("ferias").Range("E49").Value
'Me.tbW1i = sheets("ferias").Range("D51").Value
'Me.tbW1f = sheets("ferias").Range("E51").Value
'Me.tbX1i = sheets("ferias").Range("D53").Value
'Me.tbX1f = sheets("ferias").Range("E53").Value
'Me.tbY1i = sheets("ferias").Range("D55").Value
'Me.tbY1f = sheets("ferias").Range("E55").Value
'Me.tbZ1i = sheets("ferias").Range("D57").Value
'Me.tbZ1f = sheets("ferias").Range("E57").Value








'PAGINA 2 INICIO E FIM


'Me.tbA2i = sheets("ferias").Range("D8").Value
'Me.tbA2f = sheets("ferias").Range("E8").Value
'Me.tbB2i = sheets("ferias").Range("D10").Value
'Me.tbB2f = sheets("ferias").Range("E10").Value
'Me.tbC2i = sheets("ferias").Range("D12").Value
'Me.tbC2f = sheets("ferias").Range("E12").Value
'Me.tbD2i = sheets("ferias").Range("D14").Value
'Me.tbD2f = sheets("ferias").Range("E14").Value
'Me.tbE2i = sheets("ferias").Range("D16").Value
'Me.tbE2f = sheets("ferias").Range("E16").Value
'Me.tbF2i = sheets("ferias").Range("D18").Value
'Me.tbF2f = sheets("ferias").Range("E18").Value
'Me.tbG2i = sheets("ferias").Range("D20").Value
'Me.tbG2f = sheets("ferias").Range("E20").Value
'Me.tbH2i = sheets("ferias").Range("D22").Value
'Me.tbH2f = sheets("ferias").Range("E22").Value
'Me.tbI2i = sheets("ferias").Range("D24").Value
'Me.tbI2f = sheets("ferias").Range("E24").Value
'Me.tbJ2i = sheets("ferias").Range("D26").Value
'Me.tbJ2f = sheets("ferias").Range("E26").Value
'Me.tbK2i = sheets("ferias").Range("D28").Value
'Me.tbK2f = sheets("ferias").Range("E28").Value
'Me.tbL2i = sheets("ferias").Range("D30").Value
'Me.tbL2f = sheets("ferias").Range("E30").Value
'Me.tbM2i = sheets("ferias").Range("D32").Value
'Me.tbM2f = sheets("ferias").Range("E32").Value
'Me.tbN2i = sheets("ferias").Range("D34").Value
'Me.tbN2f = sheets("ferias").Range("E34").Value
'Me.tbO2i = sheets("ferias").Range("E36").Value
'Me.tbO2f = sheets("ferias").Range("E36").Value
'Me.tbP2i = sheets("ferias").Range("D38").Value
'Me.tbP2f = sheets("ferias").Range("E38").Value
'Me.tbQ2i = sheets("ferias").Range("D40").Value
'Me.tbQ2f = sheets("ferias").Range("E40").Value
'Me.tbR2i = sheets("ferias").Range("D42").Value
'Me.tbR2f = sheets("ferias").Range("E42").Value
'Me.tbS2i = sheets("ferias").Range("D44").Value
'Me.tbS2f = sheets("ferias").Range("E44").Value
'Me.tbT2i = sheets("ferias").Range("D46").Value
'Me.tbT2f = sheets("ferias").Range("E46").Value
'Me.tbU2i = sheets("ferias").Range("D48").Value
'Me.tbU2f = sheets("ferias").Range("E48").Value
'Me.tbV2i = sheets("ferias").Range("D50").Value
'Me.tbV2f = sheets("ferias").Range("E50").Value
'Me.tbW2i = sheets("ferias").Range("D52").Value
'Me.tbW2f = sheets("ferias").Range("E52").Value
'Me.tbX2i = sheets("ferias").Range("D54").Value
'Me.tbX2f = sheets("ferias").Range("E54").Value
'Me.tbY2i = sheets("ferias").Range("D56").Value
'Me.tbY2f = sheets("ferias").Range("E56").Value
'Me.tbZ2i = sheets("ferias").Range("D58").Value
'Me.tbZ2f = sheets("ferias").Range("E58").Value


'atribuir formato dd-mm-yyy ás células do userform


Me.tbA1i = Format(Me.tbA1i, "dd/mm/yyyy")
Me.tbA1f = Format(Me.tbA1f, "dd/mm/yyyy")
Me.tbB1i = Format(Me.tbB1i, "dd/mm/yyyy")
Me.tbB1f = Format(Me.tbB1f, "dd/mm/yyyy")
Me.tbC1i = Format(Me.tbC1i, "dd/mm/yyyy")
Me.tbC1f = Format(Me.tbC1f, "dd/mm/yyyy")
'Me.tbD1i = Format(Me.tbD1i, "dd/mm/yyyy")
'Me.tbD1f = Format(Me.tbD1f, "dd/mm/yyyy")
'Me.tbE1i = Format(Me.tbE1i, "dd/mm/yyyy")
'Me.tbE1f = Format(Me.tbE1f, "dd/mm/yyyy")
'Me.tbF1i = Format(Me.tbF1i, "dd/mm/yyyy")
'Me.tbF1f = Format(Me.tbF1f, "dd/mm/yyyy")
'Me.tbG1i = Format(Me.tbG1i, "dd/mm/yyyy")
'Me.tbG1f = Format(Me.tbG1f, "dd/mm/yyyy")
'Me.tbH1i = Format(Me.tbH1i, "dd/mm/yyyy")
'Me.tbH1f = Format(Me.tbH1f, "dd/mm/yyyy")
'Me.tbI1i = Format(Me.tbI1i, "dd/mm/yyyy")
'Me.tbI1f = Format(Me.tbI1f, "dd/mm/yyyy")
'Me.tbJ1i = Format(Me.tbJ1i, "dd/mm/yyyy")
'Me.tbJ1f = Format(Me.tbJ1f, "dd/mm/yyyy")
'Me.tbK1i = Format(Me.tbK1i, "dd/mm/yyyy")
'Me.tbK1f = Format(Me.tbK1f, "dd/mm/yyyy")
'Me.tbL1i = Format(Me.tbL1i, "dd/mm/yyyy")
'Me.tbL1f = Format(Me.tbL1f, "dd/mm/yyyy")
'Me.tbM1i = Format(Me.tbM1i, "dd/mm/yyyy")
'Me.tbM1f = Format(Me.tbM1f, "dd/mm/yyyy")
'Me.tbN1i = Format(Me.tbN1i, "dd/mm/yyyy")
'Me.tbN1f = Format(Me.tbN1f, "dd/mm/yyyy")
'Me.tbO1i = Format(Me.tbO1i, "dd/mm/yyyy")
'Me.tbO1f = Format(Me.tbO1f, "dd/mm/yyyy")
'Me.tbP1i = Format(Me.tbP1i, "dd/mm/yyyy")
'Me.tbP1f = Format(Me.tbP1f, "dd/mm/yyyy")
'Me.tbQ1i = Format(Me.tbQ1i, "dd/mm/yyyy")
'Me.tbQ1f = Format(Me.tbQ1f, "dd/mm/yyyy")
'Me.tbR1i = Format(Me.tbR1i, "dd/mm/yyyy")
'Me.tbR1f = Format(Me.tbR1f, "dd/mm/yyyy")
'Me.tbS1i = Format(Me.tbS1i, "dd/mm/yyyy")
'Me.tbS1f = Format(Me.tbS1f, "dd/mm/yyyy")
'Me.tbT1i = Format(Me.tbT1i, "dd/mm/yyyy")
'Me.tbT1f = Format(Me.tbT1f, "dd/mm/yyyy")
'Me.tbU1i = Format(Me.tbU1i, "dd/mm/yyyy")
'Me.tbU1f = Format(Me.tbU1f, "dd/mm/yyyy")
'Me.tbV1i = Format(Me.tbV1i, "dd/mm/yyyy")
'Me.tbV1f = Format(Me.tbV1f, "dd/mm/yyyy")
'Me.tbW1i = Format(Me.tbW1i, "dd/mm/yyyy")
'Me.tbW1f = Format(Me.tbW1f, "dd/mm/yyyy")
'Me.tbX1i = Format(Me.tbX1i, "dd/mm/yyyy")
'Me.tbX1f = Format(Me.tbX1f, "dd/mm/yyyy")
'Me.tbY1i = Format(Me.tbY1i, "dd/mm/yyyy")
'Me.tbY1f = Format(Me.tbY1f, "dd/mm/yyyy")
'Me.tbZ1i = Format(Me.tbZ1i, "dd/mm/yyyy")
'Me.tbZ1f = Format(Me.tbZ1f, "dd/mm/yyyy")


End Sub




'Private Sub CommandButton1_Click()
  '  sheets("ferias").Range("D7").Value = CDate(Me.tbA1i)
   ' sheets("ferias").Range("E7").Value = CDate(Me.tbA1f)
   ' sheets("ferias").Range("D9").Value = CDate(Me.tbB1ii)
   ' sheets("ferias").Range("E9").Value = CDate(Me.tbB1ff)
'
'End Sub

maybe I'm so tired of all these lines to edit that I'm missing something. the dd-mm-yyy doesnt seem to be working either now.

best regards,
eLy
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Position userform relative to cell
Replies
6
Views
288
John_w
J
W
  • Question
Saving userform data into specific row
Replies
1
Views
184
dmt32
D
I
  • Solved
If not correct value found then continue search
Replies
8
Views
267
Georgiboy
Georgiboy
E
  • Question
Userform with VBA
Replies
1
Views
121
Evie76
E
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
421
dmt32
D

Forum statistics

Threads
1,214,929
Messages
6,122,314
Members
449,081
Latest member
tanurai

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top