checking input on a textbox in userform to be a %

[Jean-Pierre D via OfficeKB.com]

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

 

[Richard Buttrey]

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
__________________________

 

[Jean-Pierre D via OfficeKB.com]

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

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]

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....
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

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
__________________________

 

[Jean-Pierre D via OfficeKB.com]

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

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]
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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
__________________________

 

[Jean-Pierre D via OfficeKB.com]

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

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
__________________________

 

[Richard Buttrey]

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
__________________________

 

[Jean-Pierre D via OfficeKB.com]

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

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
__________________________

 

[Richard Buttrey]

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
__________________________

 

[Jean-Pierre D via OfficeKB.com]

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?

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]

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

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?

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
__________________________