checking input on a textbox in userform to be a %

Hi,

i have a textbox in a userform that is linked tot cell A1 of the spreadsheet.
Cell A1 is defined as %.
When the user starts the userform the textbox will show 0,04 corresponding to
4% in cell A1.
However, if the userform is empty and the user types in 4 in the textbox,
cell A1 will show 400%.

how can i make sure that the input in a textbox will be seen as a percentage
so that if the input is 4, cell A1 will show 4% ?

thanks,
Jean-Pierre


--
Message posted via http://www.officekb.com

On Fri, 19 Aug 2005 07:22:51 GMT, "Jean-Pierre D via OfficeKB.com"
<[email protected]> wrote:

>Hi,
>
>i have a textbox in a userform that is linked tot cell A1 of the spreadsheet.
>Cell A1 is defined as %.
>When the user starts the userform the textbox will show 0,04 corresponding to
>4% in cell A1.
>However, if the userform is empty and the user types in 4 in the textbox,
>cell A1 will show 400%.
>
>how can i make sure that the input in a textbox will be seen as a percentage
>so that if the input is 4, cell A1 will show 4% ?
>
>thanks,
>Jean-Pierre


You could try something like

If Range("a1") > 1 Then Range("a1") = Range("a1") / 100

in the UserForm Terminate event, or some other suitable event.

Rgds

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi richard,
your input does not work for me....
on another userform i lat vba code check is an input is numeric with this
code:

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan"
.Value = vbNullString
.SetFocus
End If
End With
End Sub

and then with each field

Private Sub nw_franchise_change()
OnlyNumbers nw_franchise
End Sub

Now i would like to have something similar for input of percentages !
can you plese help me?
JP

Richard Buttrey wrote:
>>Hi,
>>
>[quoted text clipped - 10 lines]
>>thanks,
>>Jean-Pierre
>
>You could try something like
>
>If Range("a1") > 1 Then Range("a1") = Range("a1") / 100
>
>in the UserForm Terminate event, or some other suitable event.
>
>Rgds
>
>Rgds
>__
>Richard Buttrey
>Grappenhall, Cheshire, UK
>__________________________


--
Message posted via http://www.officekb.com

Maybe I'm missing something, but a percentage is just another number.
Your original question was primarily about the format or appearance of
that number.

Did you try my suggested code in the UserForm_terminate() event ?

i.e.

Private Sub UserForm_Terminate()
If Range("a1") > 1 Then Range("a1") = Range("a1") / 100
End Sub


It's not immediately apparent why, if the other code that you mention
below works, why it doesn't also work for a percentage number.

Rgds


On Fri, 19 Aug 2005 11:47:51 GMT, "Jean-Pierre D via OfficeKB.com"
<[email protected]> wrote:

>Hi richard,
>your input does not work for me....
>on another userform i lat vba code check is an input is numeric with this
>code:
>
>Private Sub OnlyNumbers(ctl As Object)
> With ctl
> If Not IsNumeric(.Value) And .Value <> vbNullString Then
> MsgBox "Sorry, alleen getallen toegestaan"
> .Value = vbNullString
> .SetFocus
> End If
> End With
>End Sub
>
>and then with each field
>
>Private Sub nw_franchise_change()
> OnlyNumbers nw_franchise
>End Sub
>
>Now i would like to have something similar for input of percentages !
>can you plese help me?
>JP
>
>Richard Buttrey wrote:
>>>Hi,
>>>
>>[quoted text clipped - 10 lines]
>>>thanks,
>>>Jean-Pierre
>>
>>You could try something like
>>
>>If Range("a1") > 1 Then Range("a1") = Range("a1") / 100
>>
>>in the UserForm Terminate event, or some other suitable event.
>>
>>Rgds
>>
>>Rgds
>>__
>>Richard Buttrey
>>Grappenhall, Cheshire, UK
>>__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

hi richard,

You were right, it works !

However, i do have another problem.
When i update a textbox in the userform, the sheet is also update but not
recalculated....
do you have a solution for that ?

Thanks,
JP

Richard Buttrey wrote:
>Maybe I'm missing something, but a percentage is just another number.
>Your original question was primarily about the format or appearance of
>that number.
>
>Did you try my suggested code in the UserForm_terminate() event ?
>
>i.e.
>
>Private Sub UserForm_Terminate()
> If Range("a1") > 1 Then Range("a1") = Range("a1") / 100
>End Sub
>
>It's not immediately apparent why, if the other code that you mention
>below works, why it doesn't also work for a percentage number.
>
>Rgds
>
>>Hi richard,
>>your input does not work for me....
>[quoted text clipped - 40 lines]
>>>Grappenhall, Cheshire, UK
>>>__________________________
>
>__
>Richard Buttrey
>Grappenhall, Cheshire, UK
>__________________________


--
Message posted via http://www.officekb.com

On Fri, 19 Aug 2005 13:43:37 GMT, "Jean-Pierre D via OfficeKB.com"
<[email protected]> wrote:

>hi richard,
>
>You were right, it works !
>
>However, i do have another problem.
>When i update a textbox in the userform, the sheet is also update but not
>recalculated....
>do you have a solution for that ?
>
>Thanks,
>JP

Do you mean that although A1 is updated (via the textbox entry), other
cells which are dependent on A1 don't calculate?

If so, first obvious question is, is Tools Options Calulation set to
Automatic? If not check the 'Automatic' option.

Alternatively if you need the worksheet to be set to manual
calculation, and only want it updated when an entry is made via the
text box, include the line

ActiveSheet.Calculate
or
Application.Calculate

in your code.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________