1

i have a userform which has 3 textbox to input the shamsi(Persian) date manually to a cell in worksheet. its impotent to input the numbers as date format. But it doesnt work correctly.

i tried to use dateserial function as below:

Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer
Dim rng1 as range
If Me.TextBox43 <> "" Then
rng1.Offset(0, -2).Value = Format(rng1.Offset(0, -2).Value, "[$-fa-IR,16]yyyy/mm/dd;@")
TextBox43 = Format(TextBox43, "[$-fa-IR,16]yyyy;@")
TextBox44 = Format(TextBox44, "[$-fa-IR,16]mm;@")
TextBox45 = Format(TextBox45, "[$-fa-IR,16]dd;@")
intDay = Me.TextBox45
intMonth = Me.TextBox44
intYear = Me.TextBox43
rng1.Offset(0, -2).Value = DateSerial(intYear, intMonth, intDay)
Else
rng1.Offset(0, -2) = (Date)
End If

But it doesnt give a right output. for example for these data: textbox43 = 1402 , textbox44= 01, textbox45=28 it gives me this answer: 1282/05/10 instead of 1402/01/28 . Also when I leave the textboxes empty then date function work correctly and the output is current date. please help me if you could. thanks a lot.

Hooman Hasanzadeh
  • 11
  • 2
  • Just curious, what is the expected result of intYear because you format TextBox43? Say a value that user input to tb43 is 1402.... If not formatted, intYear value is 1402. Since you format it, so what value you expect for intYear? If you expect the same value which is 1402, what is the purpose to format the value of tb43? How about remove the format for each tb value, apply the format to the cell after you put a value to that cell something like this `tb43 & "/" & tb44 & "/" & tb45` ? – karma Apr 17 '23 at 21:41
  • Actually that's not necessary to format textboxes as you said. That was something i tried to reach the answer. When i input the date like: tb43 & "/" & tb44 & "/" & tb45 , cell fills correctly, but it's not a date format. I want to take details from the date somewhere else, or search base on the date. So if the date in the cell hasn't a real date format, it cause problems. Thanks for your reply. – Hooman Hasanzadeh Apr 18 '23 at 05:54
  • And the last word, if i don't format tb43, it doesn't take a 4 digit number and runtime error will show up with yellow line on dateserial function. – Hooman Hasanzadeh Apr 18 '23 at 06:09
  • There is a JavaScript function [here](https://stackoverflow.com/questions/54838700/how-to-convert-persian-date-to-gregorian-date/) that maybe you could rewrite in VBA. – CDP1802 Apr 18 '23 at 17:09

0 Answers0