Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
Info from User Form to Data sheet and back: I have never built a User Form. It seems like the functions I nee are straight forward and simple, but they may not be as I am no finding a tutorial on how to do it. Just this past year I learned ho to use =IF statements in my calculations in a standard worksheet. S it's obvious I am still very new to this and only know what I know b deconstructing other sheets to see how they work. That being sai please don't assume I know coding or anything beyoind the most basic. I have almost completed a workbook for calculating data for a qualit assurance we run for our computerized tomography unit. It has 3 pages User Input which will be visable, and calculations page and Print Ou page which will both be invisible. The User Input page is raw an rough looking in its table form and I am afraid it will scare off othe who don't feel comfortable in using it. I want to set up a user form s they have a nice little window with input boxes and "next" and "back" buttons to walk them through it all. I don't know how to link input boxes to specific cells in my workshee so that the calculations can be performed. Also there will be som calculations performed in my worksheet which I need to show up in m User Form so my co worrker will know what values to set to perform th next step in the test. I am planning quite a few windows with next an back buttons so that I can take them through step by step. Once al calculations have been completed I need them to print out using all th info that was input throughough the entire process. I have not completed my workbook yet, but if needed I could post a lin to my workbook so that anyone wiling to help me could get an idea o what I am working towards and what exactly I need. Also after the en of it all and the page is printed out I do not need to save any of th info, since next week it will be all new info and calculations. If this post in in the wrong spot feel free to move it. I tried t read around to see where it fit best, but wasn't sure. Thanks for an help -- Baf ----------------------------------------------------------------------- Bafa's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57334 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
here's a simple explanation, to get you started. i am guessing you can insert a
userform and add a textbox and a command button. in a blank workbook: right click the userform in the editor and choose view code the dropdown in the upper left will display userform select activate in the dropdown on the right where it says click in between the sub and end sub it creates copy this Me.TextBox1.Value = Worksheets("sheet1").Range("a1").Value view the userform again and double click the command button enter this code between the sub and end sub Worksheets("sheet1").Range("a1").Value = Me.TextBox1.Value now run the form , type something in the text box and click save, it will appear in A1 on sheet1 close the form and reopen it, the textbox will show the value of sheet1 a1 -- Gary "Bafa" wrote in message ... Info from User Form to Data sheet and back: I have never built a User Form. It seems like the functions I need are straight forward and simple, but they may not be as I am not finding a tutorial on how to do it. Just this past year I learned how to use =IF statements in my calculations in a standard worksheet. So it's obvious I am still very new to this and only know what I know by deconstructing other sheets to see how they work. That being said please don't assume I know coding or anything beyoind the most basic. I have almost completed a workbook for calculating data for a quality assurance we run for our computerized tomography unit. It has 3 pages: User Input which will be visable, and calculations page and Print Out page which will both be invisible. The User Input page is raw and rough looking in its table form and I am afraid it will scare off other who don't feel comfortable in using it. I want to set up a user form so they have a nice little window with input boxes and "next" and "back" buttons to walk them through it all. I don't know how to link input boxes to specific cells in my worksheet so that the calculations can be performed. Also there will be some calculations performed in my worksheet which I need to show up in my User Form so my co worrker will know what values to set to perform the next step in the test. I am planning quite a few windows with next and back buttons so that I can take them through step by step. Once all calculations have been completed I need them to print out using all the info that was input throughough the entire process. I have not completed my workbook yet, but if needed I could post a link to my workbook so that anyone wiling to help me could get an idea of what I am working towards and what exactly I need. Also after the end of it all and the page is printed out I do not need to save any of the info, since next week it will be all new info and calculations. If this post in in the wrong spot feel free to move it. I tried to read around to see where it fit best, but wasn't sure. Thanks for any help. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
hi Bafa,
Ok, just post your book ... and I will try to see and help! example in event of textbox : Private Sub textBox1_Change() range("A1").value = textbox1.value End Sub that will change range A1 value too while textbox1 value changed or you're using commandbutton to react the value Private Sub CommandButton1_Click() range("A1").value = textbox1.value End Sub it will change range A1 value after commandbutton1 clicked Rgds, Halim Gary Keramidas menuliskan: here's a simple explanation, to get you started. i am guessing you can insert a userform and add a textbox and a command button. in a blank workbook: right click the userform in the editor and choose view code the dropdown in the upper left will display userform select activate in the dropdown on the right where it says click in between the sub and end sub it creates copy this Me.TextBox1.Value = Worksheets("sheet1").Range("a1").Value view the userform again and double click the command button enter this code between the sub and end sub Worksheets("sheet1").Range("a1").Value = Me.TextBox1.Value now run the form , type something in the text box and click save, it will appear in A1 on sheet1 close the form and reopen it, the textbox will show the value of sheet1 a1 -- Gary "Bafa" wrote in message ... Info from User Form to Data sheet and back: I have never built a User Form. It seems like the functions I need are straight forward and simple, but they may not be as I am not finding a tutorial on how to do it. Just this past year I learned how to use =IF statements in my calculations in a standard worksheet. So it's obvious I am still very new to this and only know what I know by deconstructing other sheets to see how they work. That being said please don't assume I know coding or anything beyoind the most basic. I have almost completed a workbook for calculating data for a quality assurance we run for our computerized tomography unit. It has 3 pages: User Input which will be visable, and calculations page and Print Out page which will both be invisible. The User Input page is raw and rough looking in its table form and I am afraid it will scare off other who don't feel comfortable in using it. I want to set up a user form so they have a nice little window with input boxes and "next" and "back" buttons to walk them through it all. I don't know how to link input boxes to specific cells in my worksheet so that the calculations can be performed. Also there will be some calculations performed in my worksheet which I need to show up in my User Form so my co worrker will know what values to set to perform the next step in the test. I am planning quite a few windows with next and back buttons so that I can take them through step by step. Once all calculations have been completed I need them to print out using all the info that was input throughough the entire process. I have not completed my workbook yet, but if needed I could post a link to my workbook so that anyone wiling to help me could get an idea of what I am working towards and what exactly I need. Also after the end of it all and the page is printed out I do not need to save any of the info, since next week it will be all new info and calculations. If this post in in the wrong spot feel free to move it. I tried to read around to see where it fit best, but wasn't sure. Thanks for any help. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
You guys are my heros! I never thought I would understand any of this code, but this simple example you gave me helped me understand the basics so I can apply it to my workbook. So now I have the ability to enter data from my User Form into my worsheet like I want. My work sheet will take this data and run them through an equation and I need the results to appear as text in a label on the next page of my user form. For example I will need the cell from Sheet1 E38 to show up like this in a label: "Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and measure the diameter of the center dot." In addition to that can you also please give me the command button code that will close UserForm1 and open UserForm2?: This is what I have now for my first suer form and it works great. Private Sub CommandButton1_Click() Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value End Sub Private Sub Label1_Click() End Sub Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub Soon I will post a link to my workbook, but I still have basic equation editing to do, so there is no point yet. Again thanks so much. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
you can use:
unload userform1 userform2.show -- Gary "Bafa" wrote in message ... You guys are my heros! I never thought I would understand any of this code, but this simple example you gave me helped me understand the basics so I can apply it to my workbook. So now I have the ability to enter data from my User Form into my worsheet like I want. My work sheet will take this data and run them through an equation and I need the results to appear as text in a label on the next page of my user form. For example I will need the cell from Sheet1 E38 to show up like this in a label: "Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and measure the diameter of the center dot." In addition to that can you also please give me the command button code that will close UserForm1 and open UserForm2?: This is what I have now for my first suer form and it works great. Private Sub CommandButton1_Click() Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value End Sub Private Sub Label1_Click() End Sub Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub Soon I will post a link to my workbook, but I still have basic equation editing to do, so there is no point yet. Again thanks so much. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
Hi Bafa,
May be you can replace your : Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub 'to: Private Sub UserForm_Activate() TextBox1.Value = sheets("User Input").Range("d11").Value TextBox2.Value = sheets("User Input").Range("d12").Value TextBox3.Value = sheets("User Input").Range("d13").Value TextBox4.Value = sheets("User Input").Range("d14").Value TextBox5.Value = sheets("User Input").Range("d15").Value End Sub So you will get the value while Userform1 loaded and showed up Gary's right he shows you to open a Userform like: 'place in a standard module : Sub ShowForm() UserForm1.Show vbModeless End sub Vbmodeless will allow you to interact with main application "Excel", remove it if you won't ! To change Label1 Caption: Private sub CommandButton2_Click() Label1.Caption = Sheets(1).Range("E38").Text End Sub Or: Private sub CommandButton2_Click() Label1.Caption = "Now set the Window Width to 1 _ and the Window Level to _ [Sheet1 E38] and measure the diameter of the center dot." End Sub Rgds, Halim Bafa menuliskan: You guys are my heros! I never thought I would understand any of this code, but this simple example you gave me helped me understand the basics so I can apply it to my workbook. So now I have the ability to enter data from my User Form into my worsheet like I want. My work sheet will take this data and run them through an equation and I need the results to appear as text in a label on the next page of my user form. For example I will need the cell from Sheet1 E38 to show up like this in a label: "Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and measure the diameter of the center dot." In addition to that can you also please give me the command button code that will close UserForm1 and open UserForm2?: This is what I have now for my first suer form and it works great. Private Sub CommandButton1_Click() Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value End Sub Private Sub Label1_Click() End Sub Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub Soon I will post a link to my workbook, but I still have basic equation editing to do, so there is no point yet. Again thanks so much. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
To close a userform:
Private Sub CommandButton3_Click() Unload Me End Sub Or : Private Sub CommandButton2_Click() Unload UserForm1 End Sub Or: Private Sub CommandButton2_Click() Me.Hide End Sub Rgds, Halim menuliskan: Hi Bafa, May be you can replace your : Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub 'to: Private Sub UserForm_Activate() TextBox1.Value = sheets("User Input").Range("d11").Value TextBox2.Value = sheets("User Input").Range("d12").Value TextBox3.Value = sheets("User Input").Range("d13").Value TextBox4.Value = sheets("User Input").Range("d14").Value TextBox5.Value = sheets("User Input").Range("d15").Value End Sub So you will get the value while Userform1 loaded and showed up Gary's right he shows you to open a Userform like: 'place in a standard module : Sub ShowForm() UserForm1.Show vbModeless End sub Vbmodeless will allow you to interact with main application "Excel", remove it if you won't ! To change Label1 Caption: Private sub CommandButton2_Click() Label1.Caption = Sheets(1).Range("E38").Text End Sub Or: Private sub CommandButton2_Click() Label1.Caption = "Now set the Window Width to 1 _ and the Window Level to _ [Sheet1 E38] and measure the diameter of the center dot." End Sub Rgds, Halim Bafa menuliskan: You guys are my heros! I never thought I would understand any of this code, but this simple example you gave me helped me understand the basics so I can apply it to my workbook. So now I have the ability to enter data from my User Form into my worsheet like I want. My work sheet will take this data and run them through an equation and I need the results to appear as text in a label on the next page of my user form. For example I will need the cell from Sheet1 E38 to show up like this in a label: "Now set the Window Width to 1 and the Window Level to [Sheet1 E38] and measure the diameter of the center dot." In addition to that can you also please give me the command button code that will close UserForm1 and open UserForm2?: This is what I have now for my first suer form and it works great. Private Sub CommandButton1_Click() Worksheets("User Input").Range("d11").Value = Me.TextBox1.Value Worksheets("User Input").Range("d12").Value = Me.TextBox2.Value Worksheets("User Input").Range("d13").Value = Me.TextBox3.Value Worksheets("User Input").Range("d14").Value = Me.TextBox4.Value Worksheets("User Input").Range("d15").Value = Me.TextBox5.Value End Sub Private Sub Label1_Click() End Sub Private Sub UserForm_Click() Me.TextBox1.Value = Worksheets("User Input").Range("d11").Value Me.TextBox2.Value = Worksheets("User Input").Range("d12").Value Me.TextBox3.Value = Worksheets("User Input").Range("d13").Value Me.TextBox4.Value = Worksheets("User Input").Range("d14").Value Me.TextBox5.Value = Worksheets("User Input").Range("d15").Value End Sub Soon I will post a link to my workbook, but I still have basic equation editing to do, so there is no point yet. Again thanks so much. -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
Thanks again! hehe With that I can even get rid of my enter button and just have "Next" and "Back" buttons. Solved: 1. User form sending info to worksheet 2. Navigating userforms by command button Still need: 1. Retrieve cell info from worksheet so it will show that current info in a label box 2. Is there a way to have an icon on desk top that will allow you to run only the User Form window and not have to get them to deal with the data sheets and choose "Run" from excel to activate the User Form? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
Hi,
May be this is my last post today, Still need answer : 1. We can retrieve anything about cells info, just determine what! like : Sub CommandButton4_Click() Label1.Caption = ActiveCell.Interior.Colorindex End sub That will retrieve the colorindex of an activecell. 2. We can also run a Userform iconic, but Excel Application still get open too but we can make it unvisible but that's not a nice decision! It will be userform only viewed without Excel Applcation or Worksheet visible ... just Application.Visible = false so Excel will unvisible, only a userform is visible if we show the userform first ! because if we make Application.Visible = False so it will be unsafe for other Workbook that currently opened too! Unless we make : Dim Appl as Excel.Application Sub NewApp() Set Appl = CreateObject("excel.application") with Appl .Workbooks.add .Visible = false end with end sub But unfortunately Appl will Visible = True if we open a Workbook after Sub NewApp because Excel Application is knowing Application last index window that will be the parent of a new opened Workbook. They still react each other ... Rgds, <smily Halim Bafa menuliskan: Thanks again! hehe With that I can even get rid of my enter button and just have "Next" and "Back" buttons. Solved: 1. User form sending info to worksheet 2. Navigating userforms by command button Still need: 1. Retrieve cell info from worksheet so it will show that current info in a label box 2. Is there a way to have an icon on desk top that will allow you to run only the User Form window and not have to get them to deal with the data sheets and choose "Run" from excel to activate the User Form? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573341 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Info from User Form to Data sheet and back
Okay I have been going over the answers given to me and I am having hard time. Like I said this is all new stuff for me. I have zer experience with VB. I am grateful to all who have replied. I am posting a link to a very raw beginings of my worksheet. I am b no means asking for anyone to complete or do my work for me. But have listed a few things in the VB User Forms that I want to have sho up and can't seem to do correctly even with your instruction. I d okay breaking down stuff I see in action. If anyone could fill ou just a field or two so I can see how it differs from the code withou it would help me to be able to further complete my project. I really do wish I knew someone in real life that I could sit down wit and have them show me a few things. I was even asking my co-workers t refer me to someone who knows Excel, to which they replied that i would be me. lol http://www.smugglersrift.com/pctools/ctqav1.xls I know you guys are giving me the right codes and such, I am just dolt and insderting it properly. Thanks for the help -- Baf ----------------------------------------------------------------------- Bafa's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57334 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write data back from a form to sheet | Excel Discussion (Misc queries) | |||
Retrieving data from work sheet to USER FORM | Excel Programming | |||
Reading data from a user form into a sheet | Excel Programming | |||
How to set focus back to sheet after showing a user form | Excel Programming | |||
How to set focus back to sheet after showing a user form | Excel Programming |