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
__________________________

Hi richard,

No, that is not what i mean.

In sheet 1, i have a cell that is filled throug the userform (a multipage).
That works fine now.
However in the vba code for sheet 1, i have a few codes that calculate other
cells in sheet one, depending on the user driven cell. Those fields do not
update when i change something in my userform.
The problem here is that the VBA code for sheet one is only executed when a
SelectionChange takes place.
This does not take place because everything is doen in the userform and the
user will not see sheet1.
any suggestions?


Thanks,
JP

Richard Buttrey wrote:
>>hi richard,
>>
>[quoted text clipped - 7 lines]
>>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
>__________________________


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

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

>Hi richard,
>
>No, that is not what i mean.
>
>In sheet 1, i have a cell that is filled throug the userform (a multipage).
>That works fine now.
>However in the vba code for sheet 1, i have a few codes that calculate other
>cells in sheet one, depending on the user driven cell. Those fields do not
>update when i change something in my userform.
>The problem here is that the VBA code for sheet one is only executed when a
>SelectionChange takes place.
>This does not take place because everything is doen in the userform and the
>user will not see sheet1.
>any suggestions?
>
>
>Thanks,
>JP


I've tried to mimic what I think you mean. I have a Sheet1 in which A1
is the cell linked via the ControlSource property to the Textbox.

I have the Userform showing on Sheet2 and with the code modified as
below to include a reference to Sheet1

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

This works for me, and changes A1 and all dependent cells on Sheet1 as
I'd expect.

Am I missing something....???

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi richard,

I had a togh weekend, so no excel for me...
Now it's a new mondya and we start again...
Your idea works fine if you terminate the userform, but i don't terminate the
userform. The application is run entirely through the userform. The worksheet
(1) should be recalculated when a change is made on one of the multipage
userform !

I would be much obliged if you have some more suggestions...
Thanks,
Jean-Pierre

Richard Buttrey wrote:
>>Hi richard,
>>
>[quoted text clipped - 13 lines]
>>Thanks,
>>JP
>
>I've tried to mimic what I think you mean. I have a Sheet1 in which A1
>is the cell linked via the ControlSource property to the Textbox.
>
>I have the Userform showing on Sheet2 and with the code modified as
>below to include a reference to Sheet1
>
>Private Sub UserForm_Terminate()
> If Worksheets("Sheet1").Range("a1") > 1 Then
>Worksheets("Sheet1").Range("a1") = Worksheets("Sheet1").Range("a1") /
>100
>End Sub
>
>This works for me, and changes A1 and all dependent cells on Sheet1 as
>I'd expect.
>
>Am I missing something....???
>
>Rgds
>
>__
>Richard Buttrey
>Grappenhall, Cheshire, UK
>__________________________


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

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

>Hi richard,
>
>I had a togh weekend, so no excel for me...
>Now it's a new mondya and we start again...
>Your idea works fine if you terminate the userform, but i don't terminate the
>userform. The application is run entirely through the userform. The worksheet
>(1) should be recalculated when a change is made on one of the multipage
>userform !

Have you tried a

worksheets("sheet1").calculate

command somewhere in your coding?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard,

still struggling....
Yes i tried that but it does not work because the recalculation code is in
the worksheet selection_change() sub....
If i use sheets("sheet1"). it will not execute the code entered in the
worksheet selection_change() part of the code because in sheet1 there is no
selection change at that point.

Any ideas?

Richard Buttrey wrote:
>>Hi richard,
>>
>[quoted text clipped - 4 lines]
>>(1) should be recalculated when a change is made on one of the multipage
>>userform !
>
>Have you tried a
>
>worksheets("sheet1").calculate
>
>command somewhere in your coding?
>
>Rgds
>
>__
>Richard Buttrey
>Grappenhall, Cheshire, UK
>__________________________


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

What I meant was that the

worksheets("sheet1").calculate

line of code should be somewhere else in your code, and outside the ws
selection change event sub.
Where have you tried including this bit of code?

Without knowing all the details of your process, userforms and any
associated module coding, it's a little difficult, but I would have
thought that you could introduce the line somewhere and force a
recalculation before the user is next presented with either a view of
the worksheet or userform.


Rgds



On Thu, 25 Aug 2005 19:15:46 GMT, "Jean-Pierre D via OfficeKB.com"
<[email protected]> wrote:

>Hi Richard,
>
>still struggling....
>Yes i tried that but it does not work because the recalculation code is in
>the worksheet selection_change() sub....
>If i use sheets("sheet1"). it will not execute the code entered in the
>worksheet selection_change() part of the code because in sheet1 there is no
>selection change at that point.
>
>Any ideas?
>
>Richard Buttrey wrote:
>>>Hi richard,
>>>
>>[quoted text clipped - 4 lines]
>>>(1) should be recalculated when a change is made on one of the multipage
>>>userform !
>>
>>Have you tried a
>>
>>worksheets("sheet1").calculate
>>
>>command somewhere in your coding?
>>
>>Rgds
>>
>>__
>>Richard Buttrey
>>Grappenhall, Cheshire, UK
>>__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________