|
User Forms - getting them to talk to Excel
Hello Again, As some of you already know, I have been working on a quote module for my company. After extensive gnashing of teeth, and pulling of hair as I fought with several coding problems, I have completely ditched the first try, and I am now on round two. I realized most of my problems were stemming from my trying to prevent my users from being able to unintentionally mess things up, while keeping the whole thing friendly to those that are not friendly with computers. Through all my research, it finally dawned on me that I need to be using a series of user forms. User forms seem easy enough, and so far making it look like I want isn't a problem at all. Figuring out how to get it to put the information into Excel the way I want has been. This leads me to the first two questions in what is likely to be a long series of questions in the future. 1. What code do I use to sequentially have the user go through the user forms in the order I want them to. And 2. I have a list of client names with their corresponding contact information in a combo box, so my users are picking the client they want to quote from the drop down list. Because we frequently quote new clients, I wanted a way to give them an option to add a new client. No problem. I found the code to add them to the list, and it works! Hurray The question is, how do I expand the row source to reflect the new addition/s, and how do I keep the users from adding duplicate clients. Any advice will be helpful. Thanks in advance to everyone who reads or posts to this tread. Amber :) -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=505875 |
User Forms - getting them to talk to Excel
Userform1.show
Userform2.show Userform3.show you show the forms. Use a defined name for the rowsource When you add a row, redefine the named range and reset the rowsource. Worksheets("Sheet1").Range("A1").currentRegion.Nam e = "Table1" You don't have to delete the name if it already exists, this redefines it. Dim res as Variant res = application.Match(Textbox1.Text,Range("Sheet1!A:A" ),0) if not iserror(res) then msgbox TextBox1.Text & " already exists" Textbox1.Text = "" exit sub End if Some additional sources of information: http://support.microsoft.com/default.aspx?kbid=161514 XL97: How to Use a UserForm for Entering Data http://support.microsoft.com/default.aspx?kbid=213749 XL2000: How to Use a UserForm for Entering Data http://j-walk.com/ss/excel/tips/tip84.htm See this tutorial here http://www.dicks-blog.com/excel/2004...g_userfor.html http://support.microsoft.com/default...b;en-us;829070 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel XL97: How to Use a UserForm for Entering Data (Q161514) http://support.microsoft.com/?id=161514 XL2000: How to Use a UserForm for Entering Data (Q213749) http://support.microsoft.com/?id=213749 Here are some other sources of information: http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel http://support.microsoft.com/default...b;en-us;829070 http://support.microsoft.com/?id=168067 File Title: Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 File Name: WE1163.EXE File Size: 161742 bytes File Date: 05/08/97 Keywords: kbfile Description: This Application Note is an introduction to manipulating UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual Basic for Applications macros that show you how to take advantage of the capabilities of UserForms and use each of the ActiveX controls that are available for UserForms Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en.../Introductiont... Part II http://msdn.microsoft.com/library/en.../Introductiont... -- Regards, Tom Ogilvy "Amber_D_Laws" wrote in message news:Amber_D_Laws.22bain_1138396207.6586@excelforu m-nospam.com... Hello Again, As some of you already know, I have been working on a quote module for my company. After extensive gnashing of teeth, and pulling of hair as I fought with several coding problems, I have completely ditched the first try, and I am now on round two. I realized most of my problems were stemming from my trying to prevent my users from being able to unintentionally mess things up, while keeping the whole thing friendly to those that are not friendly with computers. Through all my research, it finally dawned on me that I need to be using a series of user forms. User forms seem easy enough, and so far making it look like I want isn't a problem at all. Figuring out how to get it to put the information into Excel the way I want has been. This leads me to the first two questions in what is likely to be a long series of questions in the future. 1. What code do I use to sequentially have the user go through the user forms in the order I want them to. And 2. I have a list of client names with their corresponding contact information in a combo box, so my users are picking the client they want to quote from the drop down list. Because we frequently quote new clients, I wanted a way to give them an option to add a new client. No problem. I found the code to add them to the list, and it works! Hurray The question is, how do I expand the row source to reflect the new addition/s, and how do I keep the users from adding duplicate clients. Any advice will be helpful. Thanks in advance to everyone who reads or posts to this tread. Amber :) -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=505875 |
User Forms - getting them to talk to Excel
A lot to answer, so a brief starting point for you:
1. It sounds like you might benefit from using a MultiPage control on a single form - see VBA help for more info on how they work. If you really do need separate forms, then perhaps use the Deactivate event of one Form to call .Show method of the next. 2. Use something like: lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row ComboBox1.RowSource = "A1:A" & lastrow to set the ComboBox1 range to be the last row with data in column A on Sheet1. Change according to where your actual data is! As for avoiding duplicates, it would be handy if you had a set format for clients' names to check - just in case "James Thompson LTD." gets entered as "J Thompson LTD." in the future. Have a look at Application.Find to help with searching for text etc. |
User Forms - getting them to talk to Excel
WOW! Thanks for the great resources. I am leaving for the weekend, but I will let you know how it goes on Monday. Have a great weekend, Amber Tom Ogilvy Wrote: Userform1.show Userform2.show Userform3.show you show the forms. Use a defined name for the rowsource When you add a row, redefine the named range and reset the rowsource. Worksheets("Sheet1").Range("A1").currentRegion.Nam e = "Table1" You don't have to delete the name if it already exists, this redefines it. Dim res as Variant res = application.Match(Textbox1.Text,Range("Sheet1!A:A" ),0) if not iserror(res) then msgbox TextBox1.Text & " already exists" Textbox1.Text = "" exit sub End if Some additional sources of information: http://support.microsoft.com/default.aspx?kbid=161514 XL97: How to Use a UserForm for Entering Data http://support.microsoft.com/default.aspx?kbid=213749 XL2000: How to Use a UserForm for Entering Data http://j-walk.com/ss/excel/tips/tip84.htm See this tutorial here http://www.dicks-blog.com/excel/2004...g_userfor.html http://support.microsoft.com/default...b;en-us;829070 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel XL97: How to Use a UserForm for Entering Data (Q161514) http://support.microsoft.com/?id=161514 XL2000: How to Use a UserForm for Entering Data (Q213749) http://support.microsoft.com/?id=213749 Here are some other sources of information: http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel http://support.microsoft.com/default...b;en-us;829070 http://support.microsoft.com/?id=168067 File Title: Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 File Name: WE1163.EXE File Size: 161742 bytes File Date: 05/08/97 Keywords: kbfile Description: This Application Note is an introduction to manipulating UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual Basic for Applications macros that show you how to take advantage of the capabilities of UserForms and use each of the ActiveX controls that are available for UserForms Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en.../Introductiont... Part II http://msdn.microsoft.com/library/en.../Introductiont... -- Regards, Tom Ogilvy "Amber_D_Laws" wrote in message news:Amber_D_Laws.22bain_1138396207.6586@excelforu m-nospam.com... Hello Again, As some of you already know, I have been working on a quote module for my company. After extensive gnashing of teeth, and pulling of hair as I fought with several coding problems, I have completely ditched the first try, and I am now on round two. I realized most of my problems were stemming from my trying to prevent my users from being able to unintentionally mess things up, while keeping the whole thing friendly to those that are not friendly with computers. Through all my research, it finally dawned on me that I need to be using a series of user forms. User forms seem easy enough, and so far making it look like I want isn't a problem at all. Figuring out how to get it to put the information into Excel the way I want has been. This leads me to the first two questions in what is likely to be a long series of questions in the future. 1. What code do I use to sequentially have the user go through the user forms in the order I want them to. And 2. I have a list of client names with their corresponding contact information in a combo box, so my users are picking the client they want to quote from the drop down list. Because we frequently quote new clients, I wanted a way to give them an option to add a new client. No problem. I found the code to add them to the list, and it works! Hurray The question is, how do I expand the row source to reflect the new addition/s, and how do I keep the users from adding duplicate clients. Any advice will be helpful. Thanks in advance to everyone who reads or posts to this tread. Amber :) -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=505875 -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=505875 |
User Forms - getting them to talk to Excel
Hey Jakey, Thanks for the ideas! I will keep both you and Tom posted as thing progress. I considered MultiPage Controls, but there are several steps, and want my users to go through them in a precise order. It is m understanding that a multipage has tabs, and the users would be able t shift from one to the other, and in that way they might miss a step. Or am I wronge. Admitidly I don't know a whole lot about user forms, s please if I am off base let me know where my thinking strayed. See ya' around, Amber:) JakeyC Wrote: A lot to answer, so a brief starting point for you: 1. It sounds like you might benefit from using a MultiPage control on a single form - see VBA help for more info on how they work. If you really do need separate forms, then perhaps use the Deactivate even of one Form to call .Show method of the next. 2. Use something like: lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row ComboBox1.RowSource = "A1:A" & lastrow to set the ComboBox1 range to be the last row with data in column A on Sheet1. Change according to where your actual data is! As for avoiding duplicates, it would be handy if you had a set format for clients' names to check - just in case "James Thompson LTD." gets entered as "J Thompson LTD." in the future. Have a look at Application.Find to help with searching for text etc -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
User Forms - getting them to talk to Excel
The two sites in the parred down quote below did not work, and I got 404 error from the microsoft site when I tried to visit them. I just thought you might like to know. All the rest pulled up fine. have printed them, and will be reviewing them throughout the day. will let you know as soon as I have something new to report. Thanks again, and I hope you have a wonderful day, Amber:) Tom Ogilvy Wrote: ...text omitted... http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. ...text omitted... Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en.../Introductiont... Part II http://msdn.microsoft.com/library/en.../Introductiont... -- Regards, Tom Ogilvy ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread http://www.excelforum.com/showthread...hreadid=505875 [/color] -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
User Forms - getting them to talk to Excel
Design your MultiPage with tabs first so that you can easily switch
between pages. Then, when you are ready to give it to your users change the Style propery of the MultiPage to '2 - fmTabStyleNone' which removes the tabs. Make sure of course that you provide a button for them to select the next page! |
User Forms - getting them to talk to Excel
Thanks Jakey! I am still working through the literature that Tom referred me to, an I have just come across some details on MultiPages. I am glad yo mentioned them; because other wise I don't know that I would hav considered them. I like the idea about changing the tab style. Th whole point of this is to keep my users from being able to change wha they should not be allowed to change. Maybe you can give me a little help with something along these lines. Do you know how to add a status bar of some sort so they can see ho close to completion they are? I haven't found anything relating to this so far. I was thinking o something like sequentially called images, but I am sure there is a easier way. Any ideas? Regards, Amber:) JakeyC Wrote: Design your MultiPage with tabs first so that you can easily switch between pages. Then, when you are ready to give it to your user change the Style propery of the MultiPage to '2 - fmTabStyleNone' which removes the tabs. Make sure of course that you provide a button for them to select the next page -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
User Forms - getting them to talk to Excel
Application.Statusbar will change the message in the bottom left of the
window. You could have a very crude one that starts at "0% Completed..." then a quarter of the way through your code becomes "25% Completed..." etc. John Walkenbach has a nice graphical solution at http://www.j-walk.com/ss/excel/tips/tip34.htm which might be of use. |
User Forms - getting them to talk to Excel
Thanks for the tip. I will look into it when I really get to that stage Hopefully you can help with this. Please see the code snipet below: Code ------------------- Dim SourceData As Range Dim found As Object Set SourceData = Range("Companies") Set found = Nothing 'Try to find the value on the worksheet Set found = SourceData.Find(txtCompanyName.Value) 'If item is not found in the list... If found Is Nothing Then 'redefine Companies SourceData.Resize(SourceData.Rows.Count = 1, 1).Name = "Companies" 'add the new item to the end of the list on the worksheet SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = txtCompanyName.Value 'Reset the list displayed in the ComboBox txtCompanyName.RowSource = Range("Companies").Address(external:=True) End I ------------------- I am running into errors at the SourceData.Resize... point of thi code. It says "Application-defined or Object-defined error". Help!!! I got this code straight off of one of the sites Tom refered m to, one of the microsoft sites no less. The only thing I changed was th text for the named range. The one from online used ListRange, and I use Companies. That shouldn't make a difference should it. Thanks a heap in advance! JakeyC Wrote: Application.Statusbar will change the message in the bottom left of the window. You could have a very crude one that starts at "0% Completed..." then a quarter of the way through your code becomes "25% Completed..." etc. John Walkenbach has a nice graphical solution at http://www.j-walk.com/ss/excel/tips/tip34.htm which might be of use -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
User Forms - getting them to talk to Excel
Bump to top of list.. -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
User Forms - getting them to talk to Excel
Problem solved. There is an typo in the line "SourceData.Resize(SourceData.Rows.Count = 1, 1).Name = "Companies"" the = in between Count and 1 should have been a +. However, now I have a new error issue, and it does not seem typo related. Code: -------------------- 'Reset the list displayed in the ComboBox txtCompanyName.RowSource = Range("Companies").Address(external:=True) -------------------- The error message is: Could not set the RowSource property. Member not found. Any ideas? Thanks, Amber Amber_D_Laws Wrote: Thanks for the tip. I will look into it when I really get to that stage. Hopefully you can help with this. Please see the code snipet below: Code: -------------------- Dim SourceData As Range Dim found As Object Set SourceData = Range("Companies") Set found = Nothing 'Try to find the value on the worksheet Set found = SourceData.Find(txtCompanyName.Value) 'If item is not found in the list... If found Is Nothing Then 'redefine Companies SourceData.Resize(SourceData.Rows.Count = 1, 1).Name = "Companies" 'add the new item to the end of the list on the worksheet SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = txtCompanyName.Value 'Reset the list displayed in the ComboBox txtCompanyName.RowSource = Range("Companies").Address(external:=True) End If -------------------- I am running into errors at the SourceData.Resize... point of this code. It says "Application-defined or Object-defined error". Help!!! I got this code straight off of one of the sites Tom refered me to, one of the microsoft sites no less. The only thing I changed was the text for the named range. The one from online used ListRange, and I used Companies. That shouldn't make a difference should it. Thanks a heap in advance! -- Amber_D_Laws ------------------------------------------------------------------------ Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012 View this thread: http://www.excelforum.com/showthread...hreadid=505875 |
User Forms - getting them to talk to Excel
Nevermind. The code works fine now. Must have just been some sort o glitch. We have been having PC issues around the office all morning. I will close this tread now, and so I just want to send a big thank yo to Tom for all the fabulous resources, and to Jakey for several grea ideas and lots of encoragement. See ya' soon, and take care, Amber: -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=50587 |
| All times are GMT +1. The time now is 12:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com