macro to include "user select"

Col A is sequential mumber list  1 - 90. (61 currently used - allowed 90 for 
"expansion").
Col B is a list of names, with associated data in 7 cols C - I (truncated 
layout below).
Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose)

I want to macro "delete" function (to user-select any one of the 90 names), 
CLEAR the cells B - I in that row, then "data/sort" the entire range to 
remove the blank.

Got most of it by "recording" but came unstuck with the "user select".

A  B        C  D  E  F  G   H   I
1  alex     1   2   3  4   5    6   paid
2  fred     8   9  10 11 12 14  paid
3  james  3   4    5   6   7   8  paid
4  zoe      7   8   9  10 11 12  paid

(user-select) B2 ..I2, delete. select range A1:I4, data/sort by Col B - 
Question is how to pause for user input to choose name to delete? Relative 
novice, I am!
And - would be nice to have "another deletion? Y/N? to loop if answer "Y". 


0
nospam5510 (78)
10/17/2005 2:42:15 PM
excel 39879 articles. 2 followers. Follow

3 Replies
428 Views

Similar Articles

[PageSpeed] 55

Here is an example

Sub DeleteData()
Dim cell As Range

    Set cell = Application.InputBox("Select a name to delete", Type:=8)
    If Not cell Is Nothing Then
        Rows(cell.Row).Delete
    End If

End Sub

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Silvabod" <nospam@thank.you> wrote in message
news:bLO4f.5804$2z4.5297@newsfe6-win.ntli.net...
> Col A is sequential mumber list  1 - 90. (61 currently used - allowed 90
for
> "expansion").
> Col B is a list of names, with associated data in 7 cols C - I (truncated
> layout below).
> Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose)
>
> I want to macro "delete" function (to user-select any one of the 90
names),
> CLEAR the cells B - I in that row, then "data/sort" the entire range to
> remove the blank.
>
> Got most of it by "recording" but came unstuck with the "user select".
>
> A  B        C  D  E  F  G   H   I
> 1  alex     1   2   3  4   5    6   paid
> 2  fred     8   9  10 11 12 14  paid
> 3  james  3   4    5   6   7   8  paid
> 4  zoe      7   8   9  10 11 12  paid
>
> (user-select) B2 ..I2, delete. select range A1:I4, data/sort by Col B -
> Question is how to pause for user input to choose name to delete? Relative
> novice, I am!
> And - would be nice to have "another deletion? Y/N? to loop if answer "Y".
>
>


0
bob.phillips1 (6510)
10/17/2005 6:04:25 PM
Minor addition: 

If the user cancels from that macro, they'll get a run-time error.

This macro will loop until they cancel so that they can delete multiple 
times.

    Public Sub DeleteData()
        Dim cell As Range
        Do
            On Error Resume Next
            Set cell = Application.InputBox( _
                    Prompt:="Select a name to delete", _
                    Type:=8)
            On Error GoTo 0
            If cell Is Nothing Then Exit Do
            Rows(cell.Row).Delete
            Set cell = Nothing
        Loop
    End Sub


In article <uuO6LV00FHA.268@TK2MSFTNGP09.phx.gbl>,
 "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote:

> Here is an example
> 
> Sub DeleteData()
> Dim cell As Range
> 
>     Set cell = Application.InputBox("Select a name to delete", Type:=8)
>     If Not cell Is Nothing Then
>         Rows(cell.Row).Delete
>     End If
> 
> End Sub
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "Silvabod" <nospam@thank.you> wrote in message
> news:bLO4f.5804$2z4.5297@newsfe6-win.ntli.net...
> > Col A is sequential mumber list  1 - 90. (61 currently used - allowed 90
> for
> > "expansion").
> > Col B is a list of names, with associated data in 7 cols C - I (truncated
> > layout below).
> > Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose)
> >
> > I want to macro "delete" function (to user-select any one of the 90
> names),
> > CLEAR the cells B - I in that row, then "data/sort" the entire range to
> > remove the blank.
> >
> > Got most of it by "recording" but came unstuck with the "user select".
> >
> > A  B        C  D  E  F  G   H   I
> > 1  alex     1   2   3  4   5    6   paid
> > 2  fred     8   9  10 11 12 14  paid
> > 3  james  3   4    5   6   7   8  paid
> > 4  zoe      7   8   9  10 11 12  paid
> >
> > (user-select) B2 ..I2, delete. select range A1:I4, data/sort by Col B -
> > Question is how to pause for user input to choose name to delete? Relative
> > novice, I am!
> > And - would be nice to have "another deletion? Y/N? to loop if answer "Y".
> >
> >
0
jemcgimpsey (6723)
10/17/2005 8:17:14 PM
Thank you both. Now have another problem - can't get into the macro to edit 
it !!
Opened the worksheet, to be presented with the "macros disabled - security" 
message.  Have reduced security down to "lowest" but STILL can't get the 
"edit" function in macros (greyed out).
What's happened? How do I get into it?

Possibly relevant - XP Home. I am sole user/administrator, do not "log in", 
no password.
Oddly - sending an e_mail, just before opening Excel2003 - OE6 required it 
to be digitally signed (and I hadn't set it that way - found the item 
ticked, unticked it, message sent)

Have AV, Spybot - MSAntispyware - never had a virus on this pc, and none now 
(new pc, 1 month old)

What's the solution, please?


"Silvabod" <nospam@thank.you> wrote in message 
news:bLO4f.5804$2z4.5297@newsfe6-win.ntli.net...
> Col A is sequential mumber list  1 - 90. (61 currently used - allowed 90 
> for "expansion").
> Col B is a list of names, with associated data in 7 cols C - I (truncated 
> layout below).
> Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose)
>
> I want to macro "delete" function (to user-select any one of the 90 
> names), CLEAR the cells B - I in that row, then "data/sort" the entire 
> range to remove the blank.
>
> Got most of it by "recording" but came unstuck with the "user select".
>
> A  B        C  D  E  F  G   H   I
> 1  alex     1   2   3  4   5    6   paid
> 2  fred     8   9  10 11 12 14  paid
> 3  james  3   4    5   6   7   8  paid
> 4  zoe      7   8   9  10 11 12  paid
>
> (user-select) B2 ..I2, delete. select range A1:I4, data/sort by Col B - 
> Question is how to pause for user input to choose name to delete? Relative 
> novice, I am!
> And - would be nice to have "another deletion? Y/N? to loop if answer "Y".
> 


0
nospam5510 (78)
10/18/2005 3:46:28 PM
Reply:

Similar Artilces:

Saving User Selections in Access Pivot Table
I am working with an Access 2003 application that contains 6-7 Pivot Tables. The actual database file is in Access 2000 format. I've been tasked with finding a way to save the user's field selections so they can be easily reloaded if and when the database, including the query on which the Pivot Table is built, is refreshed. Can anyone tell me a) where and how Access stores those settings b) how I can access them to save and reload them Much of the reading I have done always begins with creating a form. These queries are not linked to a form. ...

Copy user selected range from all open workbooks
I'm trying to write a macro which will prompt the user to select a range then copy that same range from all open workbooks, (it will always be worksheet 1). At the moment i have this code: Dim MyRange As Range Set MyRange = Application.InputBox(Prompt:="Select any range", Title:="Demo", Type:=8) MyRange.Select MsgBox MyRange For a = 2 To (Workbooks.Count - 1) Workbooks(a).Activate Range("A4:B4").Select Selection.Copy Workbooks(Summary).Activate Range("A65536").End(xlUp).Select ActiveCell.Offset(2, 0).Select Selection.PasteSpecial ...

User Select File on Import External Data
Using Excel 2003. I am importing data into a ws and applying filters to it to use elsewhere. How can I have the query run so the user can select the file to be imported? The file name is always the same, it is the directory & sub directory that will be different each day. What I have so far is: Sub ImportData() ' Sheets("Import").Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;F:\2010\January\Jan31\COOP\dscdc004.rtf", Destination:=Range("A1")) <---------this line is the problem - need to select file ...

Want to get user selections from a message box using option buttons
I have all kinds of books telling me how to create a userform. What I need is the code to show the form with the option buttons and then retrieve the info from it. I want the user to select which option he wants and enter a date. Any help would be greatly appreciated. I have been a programmer for many years and understand many software languages. But Excel VB is a killer. I know it can't be that hard. Thanks Walt, You will usually get better answers if you provide the code that you have already tried. UserForm1.Show...will display the userform. Dim strText as String strText = UserFo...

New messages do not display until user selects a different folder
One user out of 80 is having a problem. They are using Outlook 2000 like the rest, but recently upgraded to a new PC. This user is only able to see new email if the select a currently unselected folder in outlook. Anybody know how to fix this? Thanks, Aaron http://support.microsoft.com/kb/311506/en-us --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Aaron Bellante asked: | One user out of 80 is having a problem. They are ...

Select criteria based on the field name the user selects
Hi all, I am facing with aproblem where in i have a small access db with forms and reports now i want to change or set the where condition of a query based on the value the user selects from the form through a combo box. For ex:- if user is selecting Invoice_date then the where condition should search based on invoice date if the user selects any other field the querys where condition should search based on that particular field.Can any one help me in getting a solution for this. I have a sample database that shows how you can use various controls to set the filter of a query that i...

sort by name when user selects name on another worksheet
Is there any want to select a value on one worksheet, using a pick list, and having that sort data by the name on another sheet? For example in a sheet named “Start Here” Cell B3 has a pick list with names in it, like Joe, bill, bob, henry etc. If the user selects Joe on that worksheet, then another worksheet named “Employees Info 2-25-10” is sorted by that name, i.e. Joe? Is this possible, if so how? Any help would be appreciated! I think that you mean data filtered rather than sorted? In other words, when you select "Joe" on the Start Here sheet, then the Emplo...

Dynamic Charting
I am looking for some ideas around creating a series of user friendly charts where for ony one specific metric the user can select from up to 10 business units, which ones to compare on a single chart. A chart with all 10 lines would be too busy but if a user wanted to look at his/her BU and maybe 2 or 3 others which are close in size then the chart would be useful. I am imagining a box where the user can choose a few BU's and graph just those. If the user wanted to change his/her selection they could by unclicking/clicking on the choices and a new graph would be generated. Any thoug...

User Selectable Series and Number of Series for Line Chart
I have set up a sheet that extracts user selectable data series from a large data set for up to four series max. I want to chart the data so that if four series are chosen there are four lines and associated legends; if the user selects only one series, then only one line and legend should appear. I have set up a dynamic data range name for the plotted data, that uses the selected number of series and the number of data points for each series. I've placed the name in the chart data range on the Chart Tab in Chart/Source Data as =Sheet1!PlotData This works fine until the user select...

Copying data from one workbook to another dependent on user select
I am inexperienced with Visual Basic. I have a table in a workbook (wkbook1) which has a table similar to the following A B C D E 2005 2006 2007 2008 1 North 10 4 34 15 2 South 8 25 2 4 3 East 8 14 18 7 4 West 14 2 11 10 5 Belfast 17 13 20 8 6 NI 12 8 ...

Change Form/Report Query Based on User Selection
I have a form and report based on a query. I need to change the query based on what the user selects on a form. Using VBA in MS Access 2002, can I change a single field in an existing query from GROUP BY to LAST? This process would be initiated by a click event on a form. Thanks in advance for any help. I would say only if you are willing to construct the entire query string in VBA and then apply that string as the source of the report. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Wayne wrote: > I have a form ...

User selects existing sheet to paste to
Very grateful for any help: I am a novice at this. I have a spreadsheet with multiple sheets, already named (lets say Mon, Tues, Wed). A macro visits other Excel files, retrieves data by copying. I want to allow the user to select the paste destination (sheet name only, cells A1 to end). The cells are all in the same format etc, all I need is to let the user specify - either by typing, or by selecting from a list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro continue to paste and do other things. I have tried : Range("A1:L6").Select Selection....

User selection determines field appended?
I have a form called Main that has three different dialog fields displayed, I'll call them Local Dialog, Regional Dialog, and National Dialog. The Main form has an Add Comment button and on click I'm popping up a new form called AddComment. This form has a drop down list pre-populated with Local, Regional, and National and is single select. It also has a text entry field to capture comments. After picking one of the three identifiers the user then enters their comment. My problem - after entering their comments they can hit an Add button that I created but I don't know how to ...

User Selected Query
I have a rather simple table, about 15 fields. Is there a way to build a form and or query that would have the user select the various fields they would want in the query result? The way the show check box works in the design grid, but more in a form type look? Any suggestions would be great! Open the form in form view. Go up to Records, Filter, Filter by Form. Click into a field and type what you are looking for. If you need more than one field, click on the Or tab at the lower left of the form and enter something in another field. Go up to Records, Apply Filter/Sort. When done, Rem...

inserting text lines based on user selection
Hello Guys. Have been searching trying to find a simple answer to this one but my search queries arent getting me to the results I'm setting up a template for business quotes As part of our quotes we supply a technical specification This can change from quote to quote. IE different ceiling types or flooring options I seek a simple way for the person carrying out the quote to select from a number of different line options to insert a tailored specification into the quote Our basic specification generally has about 8 different headings and about 6-7 different selecti...

Filter from user selected drop down list
Hello - I am trying to create a spreadsheet containing all the students in a year group. I need to be able to filter this list by the maths group the student is in. I have created a list box with the groups and I want the user to select their group. Once selected, I would like the list to filter out that set group. I realise this could be done via auto filter but I need it to look more user friendly than that - how do I do this in excel? Thanks!! Maths Set 9X4 First Name Surname Set Jo Bloggs 9X4 Bobb Bloggs 9X5 Here's a way to get there, using a listbox from...

dynamic chart on user selected data range in Excel 2007
I am trying create a chart 'on the fly' based on the user selected data range in Excel 2007. I was trying to use Jon Peltier's 'interactive chart creation' VB scripts. Somehow it does not work on Excel 2007. After Jon's interactiveChartCreation.xls opens, both input boxes does not work, meaning one can not input any data by mouse selection. I tried to click 'ok', but it only selected the input box. I hope Jon or some one else can see this and offer some help. Thank you all very much for your tips. This worked fine in a test I just ran. Did this wor...

deleting random amounts of data from user selected columns..
I need help! I cannot seem to figure out the best way to modify a worksheet in excel. I have a worksheet with 38 col's, and 47 rows of data. I need to allow the user to select one or more columns, then remove a random number cells from the selected columns. Any ideas???? ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** Hi Jimmy This should get you started Sub RandomRemove() Dim iNumber As Integer, iLoop As Integer Dim lRow As Long, iCol As Integer iNumber = Int((Selection.Columns.Count * Rnd) + 1) ...

User selects file location
I have a macro that formats data and saves certain portions of a worksheet into a csv file. The macro prompts the user for a path to save the file to. Instead of the user typing in the path, is there a way to have them select it somehow...similar to what happens when you select File - Save As from the main Excel menu? I appreciate your feedback. Monica a drop down box perhaps. http://www.contextures.com/tiptech.html -- Don Guillett SalesAid Software donaldb@281.com "Monica" <anonymous@discussions.microsoft.com> wrote in message news:941e01c4336e$c3b64ce0$a1012...

Macro to insert rows based on user selection
I have a macro that allows a user to insert one or more rows based on a user input box. This allows me to control which formulae get copied into the new cells. The macro works fine UNLESS the user scrolls around the screen before making their selection. How can I resolve this? This is the key part of the script. Thanks, Mike Sub Row_Insertion() ' ' This macro inserts a user-specified number of rows ' and ensures that the relevant formulae are copied ' into the new rows. Range("I3").Select ' Makes I3 the active cell and Set rn...

Capture user selection of font
If I put a font combo box on a Ribbonbar is there a way for me to capture the user's font choice? I need to capture that choice and use it in a macro. Whate *exactly* are you trying to achieve with your macro? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "dplaut"...

User selection of charts
I need an approach for users to select various charts that may be on different worksheets. The selected charts will be collected into a list for additional processing, such as printing, moving to a common worksheet, etc. I can't seem to find an easy to way have the users select the charts. Suggestions? -- Using Excel for more efficient buildings. This should give you some help with how to identify the charts in a workbook... For i = 1 To Sheets.Count If UCase(TypeName(Sheets(i))) = "CHART" Or _ Sheets(i).ChartObjects.Count > 0 Then Deb...

macro to include "user select"
Col A is sequential mumber list 1 - 90. (61 currently used - allowed 90 for "expansion"). Col B is a list of names, with associated data in 7 cols C - I (truncated layout below). Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose) I want to macro "delete" function (to user-select any one of the 90 names), CLEAR the cells B - I in that row, then "data/sort" the entire range to remove the blank. Got most of it by "recording" but came unstuck with the "user select". A B C D E F G H I 1 ale...

Create INI file; save to user selected location
I want to save an INI file to a user selected location. I need to automate the process with VBA. I have read the data I want from the registry and placed it onto my editing screen. I want the user to navigate to the folder to store the file and save it as 'MyINIFile.ini'. With Dialogs(wdDialogFileSaveAs) .name = "MyINIFile.ini" CloseNum = .Display End With When the above runs, Word forces me into a doc file. When I change to this: With Dialogs(wdDialogFileSaveAs) .name = "XXXXX.ini" .Format = wdFo...

GURU NEEDED!!! Query memo field for user selection in report
I wasn't able to find anyone with a similar problem to this one - so, I'm hoping one of you guru's can give me some direction on this. I have a form that runs off a query and populates a table. The purpose of this form is for general data entry. At the end of the form, the user uses a combo box to select a report. This report is designed to appear like a letter and all of the data entered in the form merges into this report/letter. Here's my first problem. The combo box lists brief reasons for which the letter is being sent: Example: Field One Field Two Part...