Format from a textbox in a userform

[brianfosterblack]

I am using the following code to take the content from a userform and place it in certain cells in a spreadsheet
ActiveCell.Value = FrmSale.TextBox2
Selection.NumberFormat = "d/m/yyyy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = FrmSale.TextBox3

The problem I am having is that everything is just being entered into all the cells as text
Some Textboxes are dates and they must enter as dates d/m/yyyy
some are whole numbers
some are numbers which must have 2 decimal points after the number e.g 5 entered as 5.00
some are percentages which must have 2 decimal points after the number. In the textbox could be the number 100 but this must enter in the speadsheet as 100% so I guess this should also have a formula to divide by 100 if the number is being represented as a percentage.

Can someone please help with this or point me to somewhere on the internet where I can find the answers.

 

[AlphaFrog]

This converts text to various numeric values.

Range("A2").Value = [COLOR=darkblue]CDate[/COLOR](FrmSale.TextBox2)                 [COLOR=green]'Convert date text to serial date value[/COLOR]
Range("A3").Value = Format(FrmSale.TextBox3 / 100, "0%")    [COLOR=green]'Percent e.g.; "88" = 88%[/COLOR]
Range("A4").Value = [COLOR=darkblue]CLng[/COLOR](FrmDecimal.TextBox4)               [COLOR=green]'Text whole number to numeric    (Long)[/COLOR]
Range("A5").Value = [COLOR=darkblue]CSng[/COLOR](FrmDecimal.TextBox5)               [COLOR=green]'Text Decimal to numeric decimal (Single)[/COLOR]
Range("A5").NumberFormat = "0.00"                           [COLOR=green]'Display format for two decimal places[/COLOR]

 

[brianfosterblack]

Hi ,
Thanks for this but it is not working for my date

This is the code I am Trying
ActiveCell.Value = CDate(FrmSale.TextBox1)

I am using Excel 2007

 

[AlphaFrog]

Hi ,
Thanks for this but it is not working for my date

This is the code I am Trying
ActiveCell.Value = CDate(FrmSale.TextBox1)

I am using Excel 2007

What's in textbox1? With this method, it would have to be a valid date format for it to be converted.

 

[brianfosterblack]

In textbox 1 is the date entered as dd/MM/yyyy in other words 05/04/2015

 

[AlphaFrog]

Hi ,
Thanks for this but it is not working for my date

This is the code I am Trying
ActiveCell.Value = CDate(FrmSale.TextBox1)

I am using Excel 2007

When you say "it's not working", what exactly happens? Does it error? If yes what's the error description?

 

[brianfosterblack]

The code stops on that line and asks if I want to debug or end.
Does it make any difference that the textbox is on a mulitipage1=0 within the userform?

 

[AlphaFrog]

What's the error description?

It shouldn't matter that TextBox1 on a Multipage object. Double-check the name of the Date textbox is truly TextBox1 and not some other textbox.

As a test, add this line. What is the value displayed in the MsgBox?

[B]MsgBox FrmSale.TextBox1.Text[/B]
ActiveCell.Value = CDate(FrmSale.TextBox1)

 

[brianfosterblack]

Hi,

Thank you for all your attention to this problem
this is strange - I added your text MsgBox FrmSale.TextBox1.Text and the message box popped up with the date correctly entered as before and when selected "OK" it accepted the code ActiveCell.Value = CDate(FrmSale.TextBox1) but then got stuck again at
ActiveCell.Value = CDate(FrmSale.TextBox2). I did the same for textbox 2 with the message box and then ActiveCell.Value = CDate(FrmSale.TextBox2) worked fine as well.
What is strange is that I then took out the code "MsgBox FrmSale.TextBox1.Text" and "MsgBox FrmSale.TextBox2.Text" and since then the ActiveCell.Value = CDate(FrmSale.TextBox1) and ActiveCell.Value = CDate(FrmSale.TextBox2) worked fine. I have a number of other dates in the multipage userform as I have to record birthdates of all family members. Do you know what is causing this strange problem or will I have to go through this process with all dates in the codes and after it works the first time then remove the code for the messagebox

 

[AlphaFrog]

I don't know why the msgbox would fix it.

What's the error description in the Error dialog?