Defining Value of a UserForm textbox (2003 SP)

Topic

New Reply

Good evening

I have tried searching the Interwebby but cannot find a simple answer to this, would somebody mind posting some example code that I can keep somewhere to show the following when using text boxes on user forms

Alpha only
numeric only (whole numbers)
numeric only, 2 decimals
currency

Hope thats not too much trouble

Cheers

Steve

Reply | Quote

Replies

Could you explain in detail and precisely what you want?

Reply | Quote

Hi Hans

I have a couple of text boxes on UserForm,

one is txtBoxType which will contain the name of a manufacturers box, this could be a mixture of Alpha,Numeric or specials such as _ – & etc. so by adding no code it behaves correctly.

the second is txtCost which I have coded as follows (Ihave found this in a book)

Private Sub TxtCost_Change()
If TxtCost = vbNullString Then Exit Sub
If Not IsNumeric(TxtCost) Then
MsgBox “Sorry, numbers only”
TextCost=VBNullString
End If
End Sub

No matter what I try though the result in the cell on my worksheet is a left alligned number although the cell is formated as currency, it occurs to me that this is because the example snippet of code above is only allowing numbers. So my question and thinking process is that there must be a ‘standard’ set of commands/code to make text boxes return values to forms in certain formats, for example

Private Sub TxtCost_Change()
If TxtCost = vbNullstring (number + 2 decimal places, right align)
Then Exit Sub
If Not IsNumeric(TxtCost) Then
MsgBox “Sorry, numbers only”
TxtCost = vbNullstring (number + 2 decimal places, right align)

End If
End Sub

I hope that this explaination makes a little more sense

Cheers

Steve

Reply | Quote

I am gradually (slowly and painfully) getting there

I have now added – TxtCost.Value = Format(TxtCost.Value, “£#,##0”)

However it returns whole numbers only , I am struggling to get it to return something like £11.85 and it still does not right align in the cell, it does however work in calculations which is a good +

Steve

Reply | Quote

I don’t understand why the result is left-aligned or why it would be necessary to specify the format. If I set the ControlSource of a text box on a userform to a cell that is formatted as currency with 2 decimal places, any valid value entered in the text box will be displayed in the cell as currency with two decimal places, no code needed.

But if you want two decimal places in the line of code you posted, you must specify them:

TxtCost.Value = Format(TxtCost.Value, "£#,##0.00")

Reply | Quote

Hi Hans

Thanks, I did not realise that you had replied when I replied to myself at 10:27, they must have crossed, however the example you showed is the same as I put in my text and no matter what I do it shows a whole number (in desperation I even copied and pasted your line over mine), as soon as I type a 1, £1.00 is entered. How would I set the ControlSource it gives me no options and Googling does not throw up anything?

Cheers

Steve

Reply | Quote

I don’t understand. 1 = 1.00. If you need the cell to display the decimals, you can format it the cell contents

Do you want if someone enters a 1 to actually be 0.01? If so you can just divide the entry by 100 before putting it into the cell.

Steve

Reply | Quote

Hi Steve

On the user form I would like to be able to put xx.xx ie £9.87, 143.98 etc. my cell is formatted as currency and the text box command is

Private Sub TxtCost_Change()
txtCost.Value = Format(txtCost.Value, “£#,##0.00”)
If txtCost = vbNullString Then Exit Sub
If Not IsNumeric(txtCost) Then
MsgBox “Sorry, numbers only”
TextBox1 = vbNullString
End If
End Sub

But when I run the form as soon as I enter the text box Cost and type a number it takes that as a whole number, ie. if I type 1 it immediately shows £1.00, if I press 4 it changes immediately to £4.00 and won’t let me change it unless I press number 5 through 9 and it will then increment the pence by 1p, if say I enter 1 it will show £1.00 automatically and I can’t change it but if I then press 5,6,7,8 or 9 it will change to £1.01 etc.

Hope thats clear

Cheers

Steve

Reply | Quote

I can’t replicate the problem. I have the issue that since you FORMAT the textbox, it is not a number and I get the error…

Could you attach an example file with the code having the problem you indicate?

Steve

Reply | Quote

Hi Steve

Thanks for the offer of having a look, I have had to cull the WB to bring the size down but this stand alone WS exactly shows the problem with the Add Stock Form / the Cost text box.

(I realise that I have a problem with the row count for the Add Country code but I will try and solve that later)

Cheers

Steve

Reply | Quote

Ok, hours later I have

txtCost.Value = Format(txtCost.Value, “£#,##0.00”)

This now puts in a whole £ value up to 9 i.e. if I enter 3 it shows as £3.00 and for some bizare reasons if I press the any number between 5 & 9 it increases the pence each time I push it by 1p, if I change the line to

txtCost.Value = Format(txtCost.Value, “£#,##00000.00”) and in the text box press 1 it shows £0,001.00 and again if I press or hold 5 – 9 it will increase by 1p

I am sure I am getting close!!

Steve

Reply | Quote

Here is possible solution. First, unlink the textbox from the cell on the worksheet. Then remove your textbox change event code and put the code below in the module behind the userform:


Private Sub TxtCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim curCost As Currency
If TxtCost = vbNullString Then Exit Sub
If Not IsNumeric(TxtCost) Then
MsgBox "Sorry, numbers only"
TextCost = vbNullString
Else
curCost = TxtCost
Worksheets("Sheet1").Range("A1").Value = curCost
End If

End Sub


Change “Sheet1” and “A1” in the above code to the sheet and cell where the cost is to be stored. Format the cell to display the value as you desire.

Reply | Quote

Hi Legare

Thanks for this but I cannot get this to work as I presume you expected

1) I need to enter a currency value to 2 decimal places, with this code I can enter a single 1 and it will give 1 or 5.76 or 12.95 whatever is entered, not £1.00 or £5.76 or £12.95 etc. as I require

2) My Cost range starts in B5 so I changed the code to B5, as soon as I tab within the userform B5 is updated before I close the form, If I then enter subsequent entries they all update OK but change B5 to whatever the last entry was in txtCost

3) It still does not show as currency.

It is probably me not quite grasping it but any further help greatfully accepted.

Cheers

Steve

Reply | Quote

Format the appropriate cells as currency.

If you force the userform (as you seem to want to do) to display the textbox with non-numerics then you can not test for non-numerics since you are building them into the cell. And also after the first number is entered, the decimal point and the 2 zeros are added so the next number entered into the textbox goes AFTER the 2 zeroes unless the users deletes them (and continually deletes them until the correct number is entered).

The only way around this would be to use 2 textboxes: one to enter and validate and the other to display what is entered and formatted

Steve

Reply | Quote

Hi Steve

I am probably wording wrong what I am trying to do and am confusing the issue, I think I will settle for the fact that I am able to enter a number and although the cell is formatted as currency it won’t show the £ sign, the more important thing for me was that on another worksheet I could do calculations in currency based on this cell, for example I have a column that adds all of the same stock item and then multiplies it by the price from this cell and that is still showing in currency which it does.

Thanks though to you and all that attempted to help along the way with this one

Cheers

Steve

Reply | Quote

Just as I was about to admit defeat I Googled once more and found this answer from Dave Hawley one of the Admin guys on the Ozgrid forum http://www.ozgrid.com/forum/showthread/?t=21153%5B/url%5D and it works although it is a little over wordy for my requirement I am going to leave well enough alone and use it as is.

Private Sub TxtCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim iReply As Integer
‘Prevents the error message if they choose to Cancel.
If bClosing = True Then Exit Sub

If txtCost = vbNullString Then ‘Left blank
iReply = MsgBox(“This is a mandatory amount field. ” _
& “Please click ‘Retry’, or Cancel to close the form.”, _
vbInformation + vbRetryCancel)
If iReply = vbCancel Then ‘ They want to close the form
bClosing = True
Unload Me ‘Close the UserForm
Else ‘They wish to enter an amount
txtCost.Value = 0
Cancel = True ‘Return focus back to Textbox
End If
ElseIf Not IsNumeric(txtCost) Then ‘Not a number
iReply = MsgBox(“Entry must be numeric ” _
& “Please click ‘Retry’, or Cancel to close the form.”, _
vbInformation + vbRetryCancel)
If iReply = vbCancel Then ‘ They want to close the form
bClosing = True
Unload Me ‘Close the UserForm
Else
txtCost.Value = 0
Cancel = True ‘Return focus back to Textbox
End If
Else ‘ all ok, simple fortmat as curreny
txtCost = Format(txtCost, “£0.00”)
End If

End Sub

Cheers

Steve

Reply | Quote