Completely Boggled

**vamosj** · 05-03-2005, 11:23 AM

This one has me banging my head against the wall cause I see no point why it does this. The following macro is used in my Copier Repair Center where I keep an inventory of Copiers that I repair. This macro is to remove a copier from the inventory and for some dang

(I really want to use stronger language but I will refrain) whenever it gets to the point where it deletes the entire row it suddenly jumps to a completely different macro. It is the same macro every time but I don't see any reason why it would do this... Someone assist me please before I have a serious concussion. I have tried activating the cell underneath and having it erase the row just above it but that hasn't worked either. Here's the code with brief explanations.

*** Starts on the Copier Inventory Control Page ***
Sub Copier_Remove()
Range("D17").Select
Mdl = ActiveCell.Text *** Obtains Model ***
Range("G17").Select
Srl = ActiveCell.Text *** Obtains Serial Number ***
Chck = MsgBox("WARNING! This will remove the " & Mdl & " copier with serial number " & Srl & " from your inventory, do you wish to continue?", vbOKCancel)
If Chck = vbOK Then
Sheets("Copier Inventory").Select
Application.Range("B1").Select

Else
Exit Sub

End If

Line2:
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then GoTo Line1

Loop Until ActiveCell.Value = Srl

If ActiveCell.Offset(0, -1).Value = Mdl Then *** Double Checks that Model and Serial Match ***
ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***
Sheets("Copier Inventory Main").Select
A = MsgBox("The " & Mdl & " copier with serial number " & Srl & " has been deleted from your inventory.", vbInformation)
Exit Sub

Else
A = MsgBox("The Model and Serial number did not match in my search. I will now double check the results.", vbCritical)
GoTo Line2

End If

Line1:
Sheets("Copier Inventory Main").Select
B = MsgBox("Was unable to locate proper serial number and model match. Please check your inventory to ensure it is correct.", vbCritical)

End Sub

**swatsp0p** · 05-03-2005, 11:47 AM

I am not a master programmer by any means, but I wonder if you have an open IF that is causing your problem:

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then GoTo Line1 ****when does this IF stop?****

Loop Until ActiveCell.Value = Srl

If ActiveCell.Offset(0, -1).Value = Mdl Then *** Double Checks that Model and Serial Match ***
ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***####Does this create a "" case?####
Sheets("Copier Inventory Main").Select
A = MsgBox("The " & Mdl & " copier with serial number " & Srl & " has been deleted from your inventory.", vbInformation)

Just a place to start

Bruce

**vamosj** · 05-03-2005, 12:30 PM

swatsp0p stated

If ActiveCell.Value = "" Then GoTo Line1 ****when does this IF stop?****

The if your refering to stops pretty much right there as it directs itself to Line1:

Line1:
Sheets("Copier Inventory Main").Select
B = MsgBox("Was unable to locate proper serial number and model match. Please check your inventory to ensure it is correct.", vbCritical)

End Sub

This was designed to keep the macro from running endlessly when it came to the end of the inventory. That is why it was contained within this loop

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then GoTo Line1

Loop Until ActiveCell.Value = Srl

This loop would cause 1 of 2 conditions. Either it would find the serial number and move off to check and see if the model matched the serial and then delete it, or it would search until it came to the end of the inventory.

swatsp0p stated

ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***####Does this create a "" case?####

If you follow the path on this, once it deletes the row, it should jump to the next line in the code.

If ActiveCell.Offset(0, -1).Value = Mdl Then *** Double Checks that Model and Serial Match ***
ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***
Sheets("Copier Inventory Main").Select
A = MsgBox("The " & Mdl & " copier with serial number " & Srl & " has been deleted from your inventory.", vbInformation)
Exit Sub

Once it deletes the row, it should jump over to the main control sheet for my inventory, pop up a message stating that it has been deleted, then exit the macro. For some reason it doesn't get to changing over the the Copier Inventory Main sheet. So the row delete doesn't have a chance to cause a "" case.

**Leith Ross** · 05-03-2005, 03:22 PM

Hello vamosj,

The other macro is mostly likely being triggered by a WorkBook or WorkSheet Event. THe Workbook Events would be in the "ThisWorkBook" and the Worksheets under "Sheet1", "Sheet2", etc. in the Excel objects of your project. Check both to see if any code has been placed into an event procedure.

Hope this helps,
Leith Ross

**vamosj** · 05-03-2005, 06:20 PM

Well using the object browser is something I'm not used to. I usally just get in and create the macro. This is the first time i've encountered this problem. I'll dig around when I get the chance to see what you are refering too and post a reply later...

Thanks your inputs...

**Leith Ross** · 05-03-2005, 07:51 PM

Hello Janos,

I wasn't referring to the Object Browser but the Visual Basic Editor. After you have opened the VBE using Alt + F11 from Excel, up in the left corner under the menu bar you should see a window labeld "Project - VBA Project". This is the Project Explorer Window. If it isn't visible then use Ctrl + R to display it. This will show you the objects in your Workbook.

Double Left Click on "ThisWorkbook". This will display the code on the right side. Above the code window are 2 list boxes displaying "(General)" and "(Declarations)" . Click the Down Arrow on the list box with "(General)". In the list you will see "Workbook" . Click "Workbook". The boxes will display "Workbook" and "Open" . This will show you code for the object "Workbook" and it's event "Open". Click the down arrow on "Open" and you will see a complete list of events that the object "Workbook" recognizes. Select one and click it to see if there is any code for the event. Repeat this for the Worksheet.

If I had known you were First Class Fire Control Tech, I would have done this first time. Just kidding. I was Second Class Display DS myself. Did you change rates so you blow stuff up when didn't work right? I served aboard the Midway, Towers, and Reeves. How about you? Let me know.

Hope this answers some questions,
Leith Ross

**vamosj** · 05-03-2005, 08:10 PM

Well I walked through your directions and there isn't a lick of code anywhere in it. Every one of the Open's were blank. I've been fiddling around with this and the same thing happens when I even select A:Z columbs for that row only and ClearContents it still decides to jump. This is very agrevating.

As for the FC1, well I was a former DS but they decided to merge rates. Some DS's went ET and some went FC. I pretty much do the same stuff, just changed my name. I've been on the John Young, California, Carl Vinson, NRD Portland, and currently the John C. Stennis. Glad to see another DS around though