Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CODE to select range based on User Input or Value of Input Field
ok...first things first..i'm new to all of this...
i need help!...looking for some code that would export certain info to a new worksheet, based on USER INPUT... my db is setup as so - i have a SOURCE WORKBOOK and a SUMMARY WORKBOOK... SOURCE WORKBOOK week / sales pro / revenue req'd / revenue achieved / account name / product .... 1 j. doe 1000.00 2000.00 abc company widgets 2 j. doe 2000.00 1000.00 xyz company hammers 3 4 5 to 13 i've NAMED each ROW to reflect the week (ie...Week1, Week2, Week3, Week4, Week5, Week6, etc...to Week13) SUMMARY WORKBOOK i have some code (that i'm going to use from a previous project i worked on) that enables the user, at the click of a button to EXPORT the data range that "I" defined into a new workbook... now, however, i want the user to be able to export info for the WEEK SPECIFIED BY THEM...for example export info for Week1 only onto new workbook... not quite sure how to go about this... i've already NAMED the ranges so i was thinking perhaps of adding a "prompt" that would ask the user "Which Week Do You Want to Export?"...then perhaps have a dropdown list (containing the names of my predefined weekly ranges ie..Week1, Week2, etc..) then i'd have to write code to say something like: if answer is Week1 then Range(Week1).Select, if Week2 then Range(Week2).Select i THINK i'm on the right track? appreciate your assistance! sandi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CODE to select range based on User Input or Value of Input Field
Sandi
You have some options. You can use an inputbox to get the week number like this Dim WeekNo As Variant Do WeekNo = Application.InputBox _ ("Enter week number", , , , , , , 1) If WeekNo = "False" Then Exit Do End If Loop Until WeekNo = 1 And WeekNo <= 13 If WeekNo < "False" Then Range("Week" & WeekNo).Copy _ Workbooks("Summary.xls").Sheets(1).Range("a1") End If This will loop until they enter a value from 1 to 13. You can also use a userform with a listbox that lists all the weeks for the user to select. Let me know if that's a better option for you and I'll walk you through how to set it up. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Sandi Gauthier" wrote in message ... ok...first things first..i'm new to all of this... i need help!...looking for some code that would export certain info to a new worksheet, based on USER INPUT... my db is setup as so - i have a SOURCE WORKBOOK and a SUMMARY WORKBOOK... SOURCE WORKBOOK week / sales pro / revenue req'd / revenue achieved / account name / product .... 1 j. doe 1000.00 2000.00 abc company widgets 2 j. doe 2000.00 1000.00 xyz company hammers 3 4 5 to 13 i've NAMED each ROW to reflect the week (ie...Week1, Week2, Week3, Week4, Week5, Week6, etc...to Week13) SUMMARY WORKBOOK i have some code (that i'm going to use from a previous project i worked on) that enables the user, at the click of a button to EXPORT the data range that "I" defined into a new workbook... now, however, i want the user to be able to export info for the WEEK SPECIFIED BY THEM...for example export info for Week1 only onto new workbook... not quite sure how to go about this... i've already NAMED the ranges so i was thinking perhaps of adding a "prompt" that would ask the user "Which Week Do You Want to Export?"...then perhaps have a dropdown list (containing the names of my predefined weekly ranges ie..Week1, Week2, etc..) then i'd have to write code to say something like: if answer is Week1 then Range(Week1).Select, if Week2 then Range(Week2).Select i THINK i'm on the right track? appreciate your assistance! sandi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CODE to select range based on User Input or Value of Input Field
Dick, thanks so much...could i bother u to walk me thru (explain) the
code? if i understand it, this takes me to the point where the range is selected, and copied? now all i have to do is continue w/ my code to "paste" to the new workbook.. the input box...i suppose i have to create an "input" box in the source workbook or will the user be prompted to enter this in once the macro runs? would u mind terribly walking me thru how to set this up using a dropdown list as u mentioned for option 2... sorry for all the questions! sandi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CODE to select range based on User Input or Value of Input Field
Sandi
Here's what this code does 'Set up a variable to hold the users entry Dim WeekNo As Variant 'Start a loop Do 'Application.InputBox creates an input box at run time where the 'user will enter a week number between 1 and 13. Check help for 'the arguments to the InputBox method. WeekNo = Application.InputBox _ ("Enter week number", , , , , , , 1) 'If the user selects Cancel on the input box, this stops the loop If WeekNo = "False" Then Exit Do End If 'the loop keeps going until the user selects a proper week number Loop Until WeekNo = 1 And WeekNo <= 13 'If the user doesn't click cancel If WeekNo < "False" Then 'Copy the range that's called Week? and paste to a Summary 'workbook on the first sheet cell A1 Range("Week" & WeekNo).Copy _ Workbooks("Summary.xls").Sheets(1).Range("a1") End If If you want to use a userform, the advantage would be that you provide a list of week numbers from which the user would select. I don't think that's much advantage because you can use the Prompt argument of the InputBox method to explain to the user the acceptable values. But here's how you would do it. In the VBE, select your workbook and choose UserForm from the Insert menu. From the control toolbox drag a listbox and a command button on to the userform. Double click the userform to open the code pane and from the drop down boxes at the top, select UserForm and Initialize. The Initialize event runs when the userform is shown and might look like this Private Sub UserForm_Initialize() Dim i As Long 'Populate the listbox with Week1, Week2, etc. For i = 1 To 13 Me.ListBox1.AddItem "Week" & i Next i End Sub Then select CommandButton1 and Click from the drop downs to create the Click event of the CommandButton. This will fire when the commandbutton is clicked and might look like this Private Sub CommandButton1_Click() 'Make sure something is selected If Me.ListBox1.ListIndex < -1 Then 'copy the range named Week? to the summary workbook ThisWorkbook.Sheets(1).Range(Me.ListBox1.Value).Co py _ Workbooks("Summary.xls").Sheets(1).Range("a1") 'Close the userform Unload Me Else 'If nothing selected, display a message MsgBox "Select a week from the listbox" End If End Sub Finally, you'll need a sub in a standard module to show the userform Sub ShowForm() UserForm1.Show End Sub Let me know if you need more clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Sandi Gauthier" wrote in message ... Dick, thanks so much...could i bother u to walk me thru (explain) the code? if i understand it, this takes me to the point where the range is selected, and copied? now all i have to do is continue w/ my code to "paste" to the new workbook.. the input box...i suppose i have to create an "input" box in the source workbook or will the user be prompted to enter this in once the macro runs? would u mind terribly walking me thru how to set this up using a dropdown list as u mentioned for option 2... sorry for all the questions! sandi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CODE to select range based on User Input or Value of Input Field
Thank you so much for taking the time to explain the various functions each line represents...! Sounds like i have what i need! Sandi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to have User input data for cell | Excel Discussion (Misc queries) | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Select cell from range based on input in excel xp | Excel Discussion (Misc queries) | |||
select data based on user input | Excel Programming |