Question for Aladin or Dave or any other VB experts,

Posted by Dominic on May 30, 2001 10:31 AM

Firstly, Aladin and Dave thanks for all the help.

Now another question .
I have my pricing calculation, it collects info in a UserForm, compares certain data with colums in Excel(using Vlookup) returns the correct factor and then continues the process. I use it at home on excel 2000, where my Excel is formatted for numbers with a period ie 2.3, it works no prob. I use substitute(tried both ways in VB and in Worksheet)to replace the comma with a period(assuming that some people will enter numbers with a comma rather than a period. My machine at work is formatted for the comma as the decimal place in a number, and then my system does not work.

How do I either force a specific format on my UserForm, or Multiformat my code?

Thanks

Posted by Dominic on May 30, 2001 10:59 AM

OK.. Question for anybody

Posted by Aladin Akyurek on May 30, 2001 11:48 AM

Re: OK.. Question for anybody

This is devil's work (pardon me for the expression):

How about:

=IF(ISNUMBER(A3),A3,IF(ISNUMBER(SUBSTITUTE(A3,",",".")+0),SUBSTITUTE(A3,",",".")+0,SUBSTITUTE(A3,".",",")+0))

PS. Mark W is back; you might get something more devilish.

Aladin

Posted by Dominic on May 30, 2001 2:03 PM

Re: OK.. Question for anybody

Posted by Dominic on May 30, 2001 2:05 PM

Thanks will test tomorrow at work

Posted by Dave Hawley on May 30, 2001 9:48 PM

Hi Dominic

Prevention is always better that cure! When using UserForms you should always validate the data they collect from users. I would strongly suggest you do not blow out you file size with unnecessary Formulas.

There are many ways this can be achieved, but as a starting point try this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
TextBox1.Value = WorksheetFunction.Substitute(TextBox1, ",", ".")
End Sub

Dave

OzGrid Business Applications