Copy data from a Userform to an Excel sheet
simkmil
Posted messages
505
Registration date
Status
Membre
Last intervention
-
simkmil Posted messages 505 Registration date Status Membre Last intervention -
simkmil Posted messages 505 Registration date Status Membre Last intervention -
Hello,
I have prepared a nice Userform and I would like the data to be recorded on an Excel sheet after clicking the 'save' button. But I can't get it to work.
I'm attaching my file - well, its beginning ..
Sub ArchiverBordereau()
'archive the records in the summary and increment the record number
Dim ligne As Integer
Dim numBord As Integer
MsgBox "Have you done the prints you need?", vbOKOnly
ligne = Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1 'I go down one line each time
numBord = TextBox1.Value
Sheets("summary").Range("B" & ligne).Value = numBord
Sheets("summary").Range("C" & ligne).Value = TextBox2.Value
Sheets("summary").Range("D" & ligne).Value = TextBox7.Value
Sheets("summary").Range("E" & ligne).Value = TextBox6.Value
End Sub
on my USF I have a "save" button, but nothing happens ...
sorry, but I click on "Basic" code, I can't get my file properly!
thank you for your help
Configuration: Windows / Chrome 99.0.4844.84
I have prepared a nice Userform and I would like the data to be recorded on an Excel sheet after clicking the 'save' button. But I can't get it to work.
I'm attaching my file - well, its beginning ..
Sub ArchiverBordereau()
'archive the records in the summary and increment the record number
Dim ligne As Integer
Dim numBord As Integer
MsgBox "Have you done the prints you need?", vbOKOnly
ligne = Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1 'I go down one line each time
numBord = TextBox1.Value
Sheets("summary").Range("B" & ligne).Value = numBord
Sheets("summary").Range("C" & ligne).Value = TextBox2.Value
Sheets("summary").Range("D" & ligne).Value = TextBox7.Value
Sheets("summary").Range("E" & ligne).Value = TextBox6.Value
End Sub
on my USF I have a "save" button, but nothing happens ...
sorry, but I click on "Basic" code, I can't get my file properly!
thank you for your help
Configuration: Windows / Chrome 99.0.4844.84
15 réponses
yg_be
Posted messages
23437
Registration date
Status
Contributeur
Last intervention
Ambassadeur
1 588
Hello,
explanations on code tags: https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code
I think you're not looking in the right place. The data is likely not being recorded where you want it to be.
Have you checked the value of
I don't think this is the first time you've encountered this issue.
It is better to write:
explanations on code tags: https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code
I think you're not looking in the right place. The data is likely not being recorded where you want it to be.
Have you checked the value of
line?
I don't think this is the first time you've encountered this issue.
It is better to write:
line = Sheets("summary").Range("A" & Sheets("summary").Rows.Count).End(xlUp).Row + 1
Thank you for your help.
Indeed, I have encountered this problem before; but this time I have a user form whereas before I had an order form to empty at the end and copy to a second summary sheet.
I went to look for the code from this old project to draw inspiration for this one.
Now, I have just corrected my line "ligne" as you advised, and at this level it works.
But it gets stuck at the numBord level. I deleted it and then it gets stuck at TextBox7.
Compared to my old program, I have now put the TextBox numbers in place of the sheet references since I am on a UserForm.
Although I followed the instructions for setting up the code to the letter, I still haven't managed to do it.
My second problem:
I added a button (CommandButton) to my UserForm, but I don't see how to trigger my recording with it.
Thank you once again for your help.
Indeed, I have encountered this problem before; but this time I have a user form whereas before I had an order form to empty at the end and copy to a second summary sheet.
I went to look for the code from this old project to draw inspiration for this one.
Now, I have just corrected my line "ligne" as you advised, and at this level it works.
But it gets stuck at the numBord level. I deleted it and then it gets stuck at TextBox7.
Compared to my old program, I have now put the TextBox numbers in place of the sheet references since I am on a UserForm.
Sub ArchiverBordereau() 'archive the records in the summary and increment the report number Dim ligne As Integer Dim numBord As Integer MsgBox "Have you made the prints you need?", vbOKOnly ligne = Sheets("summary").Range("A" & Sheets("summary").Rows.Count).End(xlUp).Row + 1 ' I go down a line each time 'numBord = TextBox1.Value Sheets("summary").Range("B" & ligne).Value = numBord Sheets("summary").Range("C" & ligne).Value = TextBox2.Value Sheets("summary").Range("D" & ligne).Value = TextBox7.Value Sheets("summary").Range("E" & ligne).Value = TextBox6.Value End Sub Although I followed the instructions for setting up the code to the letter, I still haven't managed to do it.
My second problem:
I added a button (CommandButton) to my UserForm, but I don't see how to trigger my recording with it.
Thank you once again for your help.
Indeed!
Here is the link to access my complete file.
Regarding the code, not everything is finished, but once I learn to enter the first parts, the rest will be easy.
https://www.cjoint.com/c/LCFqkpGDf3I
I hope this will help you assist me.
Thanks again.
Here is the link to access my complete file.
Regarding the code, not everything is finished, but once I learn to enter the first parts, the rest will be easy.
https://www.cjoint.com/c/LCFqkpGDf3I
I hope this will help you assist me.
Thanks again.
Your code is in the wrong place: it is in a module, whereas it should be the code for the UserForm.
Option Explicit Private Sub Enregistrer_Click() ' archive the slips in the summary and increment the Slip number Dim ligne As Integer Dim numBord As Integer MsgBox "Have you made the prints you need?", vbOKOnly ligne = Sheets("summary").Range("b" & Sheets("summary").Rows.Count).End(xlUp).Row + 1 ' I go down one row each time numBord = TextBox1.Value Sheets("summary").Range("B" & ligne).Value = numBord Sheets("summary").Range("C" & ligne).Value = TextBox2.Value Sheets("summary").Range("D" & ligne).Value = TextBox7.Value Sheets("summary").Range("E" & ligne).Value = TextBox6.Value End Sub
Well, THANK YOU! I didn't know that. This is the first time I've set up a UserForm. Previously, I always worked directly on sheets... But I found that using a UserForm was particularly good.
And I still think so. I'm now going to put my code in the USF, test it, and then if everything works properly, I'll complete it.
Thank you for your help.
And I still think so. I'm now going to put my code in the USF, test it, and then if everything works properly, I'll complete it.
Thank you for your help.
Great! It's working wonderfully! I've continued with my code and it works perfectly for the TextBoxes.
However, I'm trying to figure out how to work with the CommandButtons; I would like that when I click on a CommandButton, for example, an X (x) is written in the adhoc column.
While waiting for your solution (I'm sure it will come, I'm looking on my side).
Best regards.
However, I'm trying to figure out how to work with the CommandButtons; I would like that when I click on a CommandButton, for example, an X (x) is written in the adhoc column.
While waiting for your solution (I'm sure it will come, I'm looking on my side).
Best regards.
Which CommandButtons are we talking about?
If they are for the type of equipment to be repaired, wouldn't it be more logical to use option buttons instead of command buttons? By placing these buttons in the same group (by giving them the same group name), only one can be checked at a time.
Then, when saving, the VBA code will retrieve the checked button(s) and put an X in the corresponding column(s).
If they are for the type of equipment to be repaired, wouldn't it be more logical to use option buttons instead of command buttons? By placing these buttons in the same group (by giving them the same group name), only one can be checked at a time.
Then, when saving, the VBA code will retrieve the checked button(s) and put an X in the corresponding column(s).
Great! I now have two distinct groups and I can make a choice in both groups without erasing the second one.
I will continue tomorrow... I still need to change what is written on my sheet according to the button clicked (currently it's true or false).
And then, I would like to make a backup so that I can recall the slip.
Thank you, have a good evening and an excellent weekend.
I will continue tomorrow... I still need to change what is written on my sheet according to the button clicked (currently it's true or false).
And then, I would like to make a backup so that I can recall the slip.
Thank you, have a good evening and an excellent weekend.
Thank you, the Repair Café is next week. We do it every second Saturday of the month.
We started this in June with a first trial, stopped in July and August, and had our first "real" session in September. Every month we have a few more visitors - 36 last month - and we have a great atmosphere.
From an IT perspective, there are three of us: an IT manager at a large company, a freelance technician working with businesses (no shop) for 20 years, and ... me, the apprentice! I do what I know how to do and follow their advice for the rest.
We also have a seamstress (82 years old!), two electronic technicians, a bike repairman, and three "repair-everything" guys who take apart a vacuum cleaner, a portable air conditioner, clean it, and put it back together.
It's really a great organization that has increasingly encouraged me to repair rather than replace. Especially given everything that is rising in price right now.
Have an excellent weekend too. I'm diving back into my program and I'll get back to you if needed.
Indeed, this forum is a wonderful invention!
We started this in June with a first trial, stopped in July and August, and had our first "real" session in September. Every month we have a few more visitors - 36 last month - and we have a great atmosphere.
From an IT perspective, there are three of us: an IT manager at a large company, a freelance technician working with businesses (no shop) for 20 years, and ... me, the apprentice! I do what I know how to do and follow their advice for the rest.
We also have a seamstress (82 years old!), two electronic technicians, a bike repairman, and three "repair-everything" guys who take apart a vacuum cleaner, a portable air conditioner, clean it, and put it back together.
It's really a great organization that has increasingly encouraged me to repair rather than replace. Especially given everything that is rising in price right now.
Have an excellent weekend too. I'm diving back into my program and I'll get back to you if needed.
Indeed, this forum is a wonderful invention!
Here I am again...
I've made good progress on my project.
Now my UserForm opens with my document.
The sheet fills in correctly, but I would like that with the command buttons I can replace FALSE and TRUE with nothing and X (for true). I've looked for it but haven't found anything!
Then, I tried to print my UserForm, but it's more than a page wide! I don't see how to fit it to an A4 sheet unless, following a tip I saw online, I take a screenshot and put it on a sheet and then print that sheet in the right dimensions. I find that a bit ridiculous?
The last thing, not at all necessary, but it would be nice if the time of the receipt of the slip could be indicated automatically. But that's really not important.
Thank you already for the work you're helping me with.
I'm attaching my file.
https://www.cjoint.com/c/LDckuJoMmWI
Thank you
I've made good progress on my project.
Now my UserForm opens with my document.
The sheet fills in correctly, but I would like that with the command buttons I can replace FALSE and TRUE with nothing and X (for true). I've looked for it but haven't found anything!
Then, I tried to print my UserForm, but it's more than a page wide! I don't see how to fit it to an A4 sheet unless, following a tip I saw online, I take a screenshot and put it on a sheet and then print that sheet in the right dimensions. I find that a bit ridiculous?
The last thing, not at all necessary, but it would be nice if the time of the receipt of the slip could be indicated automatically. But that's really not important.
Thank you already for the work you're helping me with.
I'm attaching my file.
https://www.cjoint.com/c/LDckuJoMmWI
Thank you
For the "X", I would do:
I suppose there's nothing else to do since the whole line is empty at the start.
As for the time, I would remove this from the userform, and do:
For printing, I don't have much experience with that, I'm thinking of two options:
1) reduce the size of the userform (so also displaying it smaller)
2) create an Excel sheet that looks like what you want to print, and at the time of printing, transfer the data to it, then print that sheet.
if OptionButton1.Value then Sheets("récapitulatif").Range("I" & ligne).Value = "X" end if I suppose there's nothing else to do since the whole line is empty at the start.
As for the time, I would remove this from the userform, and do:
Sheets("récapitulatif").Range("C" & ligne).Value = Time ' hour For printing, I don't have much experience with that, I'm thinking of two options:
1) reduce the size of the userform (so also displaying it smaller)
2) create an Excel sheet that looks like what you want to print, and at the time of printing, transfer the data to it, then print that sheet.
Thank you for the OptionButtons, I will get this started right away.
If you opened my file, you will see that I have already prepared an Excel sheet with the same information as on the USR. I think I will also send my data to this sheet and then print it out, giving it the desired dimensions, and while I'm at it, why not print it in black and white (faster and more economical)
Thank you.
If you opened my file, you will see that I have already prepared an Excel sheet with the same information as on the USR. I think I will also send my data to this sheet and then print it out, giving it the desired dimensions, and while I'm at it, why not print it in black and white (faster and more economical)
Thank you.
I hadn't noticed the second tab, "print," otherwise I would have explained more simply!
This also allows you, if useful, to print from a line of the summary, without going through the userform.
I wonder how all this works, because part of the data is encoded before repair, and another part after, right?
This also allows you, if useful, to print from a line of the summary, without going through the userform.
I wonder how all this works, because part of the data is encoded before repair, and another part after, right?
Yes, that's my problem too. I still don't know how we're going to do it? I was thinking about saving the forms so that we could refer to them after the repairs and thus complete the "after repair" section. Otherwise, we would use printed sheets like the USF but blank, and when they come back, they would be encoded. But I think that would take more time.
I could also limit the USF to the first part and encode the second part directly in the summary.
The summary will allow us to carry out statistics based on all the criteria we want.
So I will start preparing the printout by copying the data from the USF into the Print sheet.
Thank you and have a great evening.
I could also limit the USF to the first part and encode the second part directly in the summary.
The summary will allow us to carry out statistics based on all the criteria we want.
So I will start preparing the printout by copying the data from the USF into the Print sheet.
Thank you and have a great evening.
maybe like this:
- a userform to create a new bill, with the creation of a new line in "summary" (all of this already exists)
- if we position ourselves in the "summary" tab on one of the lines, and we do a key combination or a double click, it starts another userform that allows us to correct/complete the data already entered.
- a userform to create a new bill, with the creation of a new line in "summary" (all of this already exists)
- if we position ourselves in the "summary" tab on one of the lines, and we do a key combination or a double click, it starts another userform that allows us to correct/complete the data already entered.
Great idea, I'll try that. But I also had the idea that I used in an invoicing program (where you actually helped me a lot) where I save the documents by their order number and I can thus look up the pdf, but here I would save it in xlsx so that I can correct it.
I'll try both.
Thank you and have an excellent Sunday. It's cold here... very cold!
I'll try both.
Thank you and have an excellent Sunday. It's cold here... very cold!