Need VB Script to edit or delete excel record

Excel Excel 2007
November 23, 2011 at 23:33:00
Specs: Windows 7, AMD Phenom II X6/4Gb
I am building a excel spreadsheet to be used as a database where I have multiple tabs that each contain the same basic information but each tab contains unique information, too. I have built a user form to add data to the first empty row on each tab then sort the data, but I cannot figure out how to edit or delete the data. I use the spreadsheet because my work prefers it...

An example of the data would be that on all tabs the following is displayed:
Name
Grade
Unit of Assignment
Company ID

but on one tab, I have tasking information, on another I have training information and having all this data on one sheet is too cluttered and distracting.

What I am looking for is the code to place behind a button that would

1. Ask for a persons company ID number, then be able to go to each tab on the workbook and delete that row on all tabs

2. Allow me to edit the basic information for an individual by using the same form that I originally entered them into the spreadsheet with. This data, too, must update the data across all the tabs.

Any help would be greatly appreciated.


See More: Need VB Script to edit or delete excel record

Report •


#1
November 24, 2011 at 12:11:39
I'm confused.

You said that you are looking for code that would delete a person's information when the ID is entered but that will also allow to you edit the person's information.

Do you want the code to ask you if you want to Edit or Delete the person's information or are these 2 completely sets of code to be accessed by 2 different buttons?

BTW, what you are probably looking to use is the .Find method which will Find whatever you enter in an InputBox.

Look here:

http://msdn.microsoft.com/en-us/lib...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 27, 2011 at 03:59:27
Well, I was looking to actually use two separate buttons, one for each function. Here is the code that I was using for deleting the information, but I can only get it to work on one worksheet / tab at a time.

Sub Delete_Row()
' Set Word As String
Dim word As Variant
' Set static column to search word string from
Col = "B"

word = InputBox("What is the word whose rows you want to delete?")


On Error Resume Next

With Columns(Col)
Sheets("Names").Select
.Replace word, "#N/A", xlWhole
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
'With Columns(Col)
' Sheets("Dep").Select
' Range("A2").Select
' .Replace word, "#N/A", xlWhole
'.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

' End With
End Sub

This sub routine works fine, but only on the first tab named "Names". I tried to duplicate the commands to make it move to a second tab called "Dep" and perform the same actions. It will move to the second tab, but not delete anything.

I thought perhaps the word string was being forgotten so I added the word1 = word. Don't know if that worked or not.

The entire idea came from the default dataform in excel, but I can't see the script behind that form, and as far as I can tell, it only works on the tab that you have active...

In the data entry form that I have built, it allows me to enter a great deal of data on one person then places that data in the appropriate tabs in the respective data columns. Can the find next command be used with the entirerow.delete command when it spans most/all tabs in a workbook?


Report •

#3
November 27, 2011 at 08:06:50
First, you rarely have to Select an object (e.g. a sheet or cell) for VBA to work with it. Selecting objects is very inefficient. It's more efficient to reference the objects directly.

For example, these 3 lines...

Sub DeleteRow_v1()
  Sheets("Names").Select
   Range("A2").Select
    Selection.EntireRow.Delete
End Sub

...can be replaced with a single line:

Sub DeleteRow_v2()
  Sheets("Names").Range("A2").EntireRow.Delete
End Sub

Using that syntax, it doesn't matter which sheet is Active.

Second, you can loop through the sheets without referencing them by Sheet Name:

Sub LoopThroughSheets()
  For shtNum = 1 to Sheets.Count
    Sheets(shtNum).Range("A2").EntireRow.Delete
  Next
End Sub

If you need to skip a sheet (or sheets) you can handle that this way:

Sub LoopThroughSheets()
  For shtNum = 1 To Sheets.Count
   If Sheets(shtNum).Name = "Sheet2" Then GoTo SkipSheet
    Sheets(shtNum).Range("A2").EntireRow.Delete
SkipSheet:
  Next
End Sub

Third, you shouldn't use the With method until you are already working with a given sheet. e.g.

Sub UsingWith()
 col = "B"
   For shtNum = 1 to Sheets.Count
    With Sheets(shtNum).Columns(col) 
        'Do what you want with Column B
    End With
  Next
End Sub

I hope these tips help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 1, 2011 at 21:14:52
Not sure why I couldn't get the suggestions to work...but I was playing this evening and tried this:

Sub Delete_Row()
' Set Word As String
Dim word As Variant

' Set static column to search word string from
Col = "B"

word = InputBox("What is the members SSN to delete?")


On Error Resume Next

With Columns(Col)
Sheets("Names").Select
.Replace word, "#N/A", xlWhole
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
Sheets("Dep").Select

Col = "B"

With Columns(Col)
.Replace word, "#N/A", xlWhole
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

End With
End With
End Sub

and it worked. I might have to make some controls/limits on it... entering a wildcard deleted all rows of data on both sheets... Something for another time!

I appreciate the input and ideas... I have a few more ideas for this project, so I'll probably end up asking for more help.


Report •

Ask Question