Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interactive Data entry
Here is an example using inputboxes:
Dim res as Variant, rng as long Dim rng1 as Long, res1 as variant res = Inputbox("Enter id") if res = "" then ' user clicked cancel so quit exit sub End if With Worksheets("Data") set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) End with if isnumeric(res) then res = cdbl(res) res1 = application.Match(res,rng,0) if iserror(res1) then msgbox "Id was not found") else set rng1 = rng(res) msgbox "found at row " & rng1.row End if Harald Staff just posted this link as a tutorial on using Userforms: .. See this tutorial here http://www.dicks-blog.com/excel/2004...g_userfor.html Here are some more resources for userforms: http://j-walk.com/ss/excel/tips/tip84.htm http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp 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 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel http://support.microsoft.com/default...b;en-us;829070 A 30 or 40 page reference in a word doc: 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 -- Regards, Tom Ogilvy "KG" wrote in message ... Can one program Excel to lead the user interactively through a set of data entry screens? For example: "Do royalties apply?" If the user clicks "NO," he is led to the next quesetion. If the user clicks "YES" a dialog box opens for him to enter the royalty rate, and so forth Does this reauir VB.NET programming? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interactive Data entry
Thanks for the references. There are a few write-ups to read yet, but I'm
still struggling with this. What is the purpose of this line code in your example: With Worksheets("Data") set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) What does .End(xlup) do? I want to be able to insert a single piece of data from each page of a multipage UserForm to one specific cell in a specific worksheet, in a workbook that consists of several worksheets. For example, I want to be able to define that "this piece of data goes to Cell A2 of Worksheet titled "Financials," and "this piece of data goes to cell C8 of worksheet titled "Hurdle Rates." In other words, it is not a data list arrangement where data will be added to consecutive, contiguous rows of a worksheet. Do I still have to go through the steps of defining ranges? "Tom Ogilvy" wrote: Here is an example using inputboxes: Dim res as Variant, rng as long Dim rng1 as Long, res1 as variant res = Inputbox("Enter id") if res = "" then ' user clicked cancel so quit exit sub End if With Worksheets("Data") set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) End with if isnumeric(res) then res = cdbl(res) res1 = application.Match(res,rng,0) if iserror(res1) then msgbox "Id was not found") else set rng1 = rng(res) msgbox "found at row " & rng1.row End if Harald Staff just posted this link as a tutorial on using Userforms: .. See this tutorial here http://www.dicks-blog.com/excel/2004...g_userfor.html Here are some more resources for userforms: http://j-walk.com/ss/excel/tips/tip84.htm http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp 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 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel http://support.microsoft.com/default...b;en-us;829070 A 30 or 40 page reference in a word doc: 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 -- Regards, Tom Ogilvy "KG" wrote in message ... Can one program Excel to lead the user interactively through a set of data entry screens? For example: "Do royalties apply?" If the user clicks "NO," he is led to the next quesetion. If the user clicks "YES" a dialog box opens for him to enter the royalty rate, and so forth Does this reauir VB.NET programming? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interactive Data entry
Do I still have to go through the steps of defining ranges?
Not if you don't need them and you don't indicate that you do. The code shows you how to put up an input box to solicit input. -- Regards, Tom Ogilvy "KG" wrote in message ... Thanks for the references. There are a few write-ups to read yet, but I'm still struggling with this. What is the purpose of this line code in your example: With Worksheets("Data") set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) What does .End(xlup) do? I want to be able to insert a single piece of data from each page of a multipage UserForm to one specific cell in a specific worksheet, in a workbook that consists of several worksheets. For example, I want to be able to define that "this piece of data goes to Cell A2 of Worksheet titled "Financials," and "this piece of data goes to cell C8 of worksheet titled "Hurdle Rates." In other words, it is not a data list arrangement where data will be added to consecutive, contiguous rows of a worksheet. Do I still have to go through the steps of defining ranges? "Tom Ogilvy" wrote: Here is an example using inputboxes: Dim res as Variant, rng as long Dim rng1 as Long, res1 as variant res = Inputbox("Enter id") if res = "" then ' user clicked cancel so quit exit sub End if With Worksheets("Data") set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) End with if isnumeric(res) then res = cdbl(res) res1 = application.Match(res,rng,0) if iserror(res1) then msgbox "Id was not found") else set rng1 = rng(res) msgbox "found at row " & rng1.row End if Harald Staff just posted this link as a tutorial on using Userforms: .. See this tutorial here http://www.dicks-blog.com/excel/2004...g_userfor.html Here are some more resources for userforms: http://j-walk.com/ss/excel/tips/tip84.htm http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp 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 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel http://support.microsoft.com/default...b;en-us;829070 A 30 or 40 page reference in a word doc: 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 -- Regards, Tom Ogilvy "KG" wrote in message ... Can one program Excel to lead the user interactively through a set of data entry screens? For example: "Do royalties apply?" If the user clicks "NO," he is led to the next quesetion. If the user clicks "YES" a dialog box opens for him to enter the royalty rate, and so forth Does this reauir VB.NET programming? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interactive Data Input Boxes | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
Interactive Chart Data? | Excel Discussion (Misc queries) | |||
iNTERACTIVE EXCEL FILE NOT INTERACTIVE ON THE WEB | Excel Discussion (Misc queries) |