Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
On Fri, 19 Aug 2005 07:22:51 GMT, "Jean-Pierre D via OfficeKB.com"
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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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" 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 __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
On Fri, 19 Aug 2005 13:43:37 GMT, "Jean-Pierre D via OfficeKB.com"
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 __________________________ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
On Fri, 19 Aug 2005 14:31:00 GMT, "Jean-Pierre D via OfficeKB.com"
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 __________________________ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
On Mon, 22 Aug 2005 16:07:25 GMT, "Jean-Pierre D via OfficeKB.com"
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 __________________________ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking input on a textbox in userform to be a %
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" 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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform-Change textbox color and font based on input in other tex | Excel Programming | |||
Checking a userform for blanks | Excel Programming | |||
Input mask for userform textbox | Excel Programming | |||
Checking input for alphabet (i.e. words) | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |