Fill cells with a TextBox
dudulleray
-
dudulleray -
dudulleray -
Hello everyone, Forum hello
Configuration: Windows 7 / Excel 2007
I have a sheet1 that serves as my "database" and a UserForm1 with a TextBox1
I would like to use the TextBox1 to enter only numbers and a comma into one of the cells located in my Sheet1 in the range E21:P21
How can I select ONE of the cells in the range E21:P21 beforehand where what I will write in TextBox1 will be copied
For example: I select G21, I write 125 in TextBox1 and it transfers to cell G21
P.S: I was thinking of making the selection with a combobox but I don't know how to write the code
Thank you all for your time, open to any ideas
Have a good afternoon everyone
Best regards
Configuration: Windows 7 / Excel 2007
I have a sheet1 that serves as my "database" and a UserForm1 with a TextBox1
I would like to use the TextBox1 to enter only numbers and a comma into one of the cells located in my Sheet1 in the range E21:P21
How can I select ONE of the cells in the range E21:P21 beforehand where what I will write in TextBox1 will be copied
For example: I select G21, I write 125 in TextBox1 and it transfers to cell G21
P.S: I was thinking of making the selection with a combobox but I don't know how to write the code
Thank you all for your time, open to any ideas
Have a good afternoon everyone
Best regards
10 answers
Hello
Several solutions:
-Select the cell before activating the Userform: the easiest to implement
-A dialog box (inputbox) where the user will enter the cell: disadvantage is that the format and validity of the entered references must be handled
-As you mentioned, a combo box containing the selection of cells: simple (relatively) and safe.
- And maybe other options I haven't thought of ....;
Depending on the choice, all that's left to do is!
See you soon
Several solutions:
-Select the cell before activating the Userform: the easiest to implement
-A dialog box (inputbox) where the user will enter the cell: disadvantage is that the format and validity of the entered references must be handled
-As you mentioned, a combo box containing the selection of cells: simple (relatively) and safe.
- And maybe other options I haven't thought of ....;
Depending on the choice, all that's left to do is!
See you soon
re,
in the Sub Initialize() of the Userform:
to avoid adding cell references one by one, we use a loop:
then create a CommandButton and in its Sub Click():
to be adapted to the name of the ComboBox.
Good continuation or A+
in the Sub Initialize() of the Userform:
to avoid adding cell references one by one, we use a loop:
For i = 1 To 12 ComboBox1.AddItem Chr(68 + i) & 21 ' creates from E21 to P21 Next
then create a CommandButton and in its Sub Click():
If ComboBox1.Value <> "" Then Range(ComboBox1.Value) = TextBox1.Value Else Msgbox "You must choose a cell reference ...." End If
to be adapted to the name of the ComboBox.
Good continuation or A+
Hello Paf, forum
Thank you for your response and for the little code, it's very kind of you. I tried it and I can see the references of the cells in ComboBox2, which I can select OK
After that, I can't place the value entered in TextBox2 into the
chosen cell.
Thank you for your help, have a nice day while waiting for the sun.
Best regards, Ray
Please, if you have the time to correct me, not me (LOL) the code hihihihi, thank you
Thank you for your response and for the little code, it's very kind of you. I tried it and I can see the references of the cells in ComboBox2, which I can select OK
After that, I can't place the value entered in TextBox2 into the
chosen cell.
Thank you for your help, have a nice day while waiting for the sun.
Best regards, Ray
Please, if you have the time to correct me, not me (LOL) the code hihihihi, thank you
Private Sub CommandButton5_Click() If ComboBox2.Value <> "" Then Range(ComboBox2.Value) = TextBox2.Value Else MsgBox "Choose a cell reference." End If End Sub '*** Set Ws = Sheets("Compte") is the name of my Sheet1 Private Sub TextBox2_Change() Ws.Cells(21, ComboBox2.Value) = TextBox2.Value ' Error on this line End Sub
Re,
there are surely details that you did not specify at the beginning. What comes out of the first post:
a sheet, a userform, a combobox.
we choose the cell reference in the combobox and we write in the same sheet in the chosen cell what we just entered in the textbox.
If that's the case, the proposed code works, when used in a command button, as you seem to have done.
Trying to start writing in the chosen cell from Sub TextBox2_Change() is not a good idea since it triggers as soon as the first character is entered. (hence the idea of triggering the code by a button after entering the textbox.)
Moreover, in Ws.Cells(21, ComboBox2.Value), ComboBox2.Value contains E21 or F21 or G21 or ....., it's as if you were writing: Ws.Cells(21, E21). The syntax is not correct.
the principle is therefore given in my previous post with the modifications:
See you later
there are surely details that you did not specify at the beginning. What comes out of the first post:
a sheet, a userform, a combobox.
we choose the cell reference in the combobox and we write in the same sheet in the chosen cell what we just entered in the textbox.
If that's the case, the proposed code works, when used in a command button, as you seem to have done.
Trying to start writing in the chosen cell from Sub TextBox2_Change() is not a good idea since it triggers as soon as the first character is entered. (hence the idea of triggering the code by a button after entering the textbox.)
Moreover, in Ws.Cells(21, ComboBox2.Value), ComboBox2.Value contains E21 or F21 or G21 or ....., it's as if you were writing: Ws.Cells(21, E21). The syntax is not correct.
the principle is therefore given in my previous post with the modifications:
Private Sub Commandbutton5_Click() If ComboBox2.Value <> "" Then Sheets("Compte").Range(ComboBox2.Value) = TextBox2.Value Else MsgBox "Choose a cell reference." End If End Sub See you later
Hi Paf,
Thank you for the modification and for your equally enriching explanations, so the tests were conclusive and everything works well.
Another small favor please to refine the code if you don’t mind
I currently have several Textboxes from 1 to 5.
I would like to control the inputs in these Textboxes so that they can only be numbers with a decimal point or a comma.
Would it be possible for you (if you have some time available) to create a loop for the 5 Textboxes to ensure that only numbers are entered?
To avoid repetitive code in each Textbox
For example: a subroutine that would be called when a test is needed
I thank you very much for the help provided; plus I’ve learned a few things, including this code that I hadn't seen before on the internet
this will be quite useful for me in the future
Have a good afternoon
See you later Ray
Thank you for the modification and for your equally enriching explanations, so the tests were conclusive and everything works well.
Another small favor please to refine the code if you don’t mind
I currently have several Textboxes from 1 to 5.
I would like to control the inputs in these Textboxes so that they can only be numbers with a decimal point or a comma.
Would it be possible for you (if you have some time available) to create a loop for the 5 Textboxes to ensure that only numbers are entered?
To avoid repetitive code in each Textbox
For example: a subroutine that would be called when a test is needed
I thank you very much for the help provided; plus I’ve learned a few things, including this code that I hadn't seen before on the internet
ComboBox1.AddItem Chr(68 + i) & 21 ' creates from E21 to P21
this will be quite useful for me in the future
Have a good afternoon
See you later Ray
Would it be possible for you, please (if you have some free time), to create a loop for the 5 TextBoxes to ensure that only numbers are entered? This would avoid repetitive code in each TextBox.
to avoid entering the same code for each textbox, it's much less straightforward.
I suggest you check the tips from lermite222 on the following links:
http://www.commentcamarche.net/faq/12862-vba-ecrire-lire-une-serie-de-textbox-en-une-seule-sub
http://www.commentcamarche.net/faq/31279-vba-ajouter-des-controles-dynamiquement-dans-un-userform
have a good continuation and good progress
RE
I looked at the links you sent me, indeed it's not too simple
I was thinking of a small macro Sub to call when we're in a Textbox that
controls if we're only entering numbers.
Oh well
I thank you for taking an interest in my little issues, that's very nice
and thanks to you, my file is slowly progressing.
See you later, no doubt, and thank you again
Best regards, Raymond
I looked at the links you sent me, indeed it's not too simple
I was thinking of a small macro Sub to call when we're in a Textbox that
controls if we're only entering numbers.
Oh well
I thank you for taking an interest in my little issues, that's very nice
and thanks to you, my file is slowly progressing.
See you later, no doubt, and thank you again
Best regards, Raymond
Re,
More simply, you can control the input on the fly and if it is numeric, retain it in the tag to restore it if a non-numeric input occurs, after an alert message.
You can also implement finer control: maximum 2 decimals, minimum and maximum limits, etc.
eric
More simply, you can control the input on the fly and if it is numeric, retain it in the tag to restore it if a non-numeric input occurs, after an alert message.
Private Sub TextBox1_Change() If IsNumeric(TextBox1) Or TextBox1 = "" Then Tag = TextBox1 Else MsgBox ("numeric value only") TextBox1 = Tag End If End Sub You can also implement finer control: maximum 2 decimals, minimum and maximum limits, etc.
eric
Hi Eric
Thank you for your reply and for the little code, it works but I need to see how to refine it
You told me this
"You can also make a finer control: a maximum of 2 decimals, minimum and maximum limits, etc."
Could you please give me an example of these possibilities? I'm interested because I'm in
the middle of inputting data, two decimals are fine for me as well as the point or the comma
when you have the time, of course. I'm going to have lunch now, enjoy your meal.
See you later and thanks again for your help
Best regards, Ray
Thank you for your reply and for the little code, it works but I need to see how to refine it
You told me this
"You can also make a finer control: a maximum of 2 decimals, minimum and maximum limits, etc."
Could you please give me an example of these possibilities? I'm interested because I'm in
the middle of inputting data, two decimals are fine for me as well as the point or the comma
when you have the time, of course. I'm going to have lunch now, enjoy your meal.
See you later and thanks again for your help
Best regards, Ray
Hello Eric
This is for my personal accounting, so two decimals maximum is more than enough for me
However, comma or point, it's not easy because I don't know why sometimes it's the comma or the point that works
For your information, I currently have 5 TextBoxes where I only enter numbers
Thank you for your help, have a good day
Best regards, Ray
This is for my personal accounting, so two decimals maximum is more than enough for me
However, comma or point, it's not easy because I don't know why sometimes it's the comma or the point that works
For your information, I currently have 5 TextBoxes where I only enter numbers
Thank you for your help, have a good day
Best regards, Ray
Hello,
This is for my personal accounting
Do you really think it's worth it? I don't think so...
You always check your entry before validating, and the cases where you will have 3 decimals should happen once every 30 years...
It's worth it for checking a reference like 3 letters-2 digits-1 letter, but that would just add unnecessary code.
On the other hand, using a comma or a point is not easy because I don't know why sometimes it's the comma or the point that works
It depends on the machine, on its default setting for the decimal separator in the control panel.
eric
This is for my personal accounting
Do you really think it's worth it? I don't think so...
You always check your entry before validating, and the cases where you will have 3 decimals should happen once every 30 years...
It's worth it for checking a reference like 3 letters-2 digits-1 letter, but that would just add unnecessary code.
On the other hand, using a comma or a point is not easy because I don't know why sometimes it's the comma or the point that works
It depends on the machine, on its default setting for the decimal separator in the control panel.
eric
Thank you for your response, indeed I thought about (inputbox) but not too much.
If you could help me please with the code and if you have time the ComboBox
seems good to me.
I was thinking of putting the values from cells E21 to C21 in the combo
then selecting the destination cell, after I thought through a
TextBox to enter the value, e.g. 125.75 € and transfer it via the preselected cell.
I see it like that, but I'm probably off track LOL.
I'll talk to you later and thank you.
Best regards.