Excel Macro is not resetting after routine is done. It remains at end of sheet.

Hi Chris,

is that all you need?

seems to me that the protection on the sheet is not set up properly ... input cells should be unLOCKED too.

Chris, Yeah if it's generating error before that line..it will skip those lines where you are selecting the cell..I will use it like this.. so that even in terms of error it does that for me...

Option Explicit

Sub SendToDB()
'151116 modified by strive4peace

   On Error GoTo Proc_Err
   
   Dim wsDB As Worksheet
   Dim wbDB As Workbook
   Dim wsConsolidate As Worksheet
   Dim wbConsolidate As Workbook
   
   Dim lngNextRowDB As Long
   Dim lngNextRowConsolidate As Long
   Dim lngRowDB1 As Long
   Dim lngRowDB2 As Long
   Dim lngLastRowData As Long
   Dim lngRow As Long
   Dim sPathFile As String
   
   sPathFile = "C:\Users\pike\Desktop\114Database\Consolidate.xlsm" '--- customize this
   '                           this workbook has a sheet called: ConsolidatedDB
   
    If MsgBox("Transfer data to Main, clear all input and start again with Skid #1" _
      , vbYesNo + vbDefaultButton2, "Warning") <> vbYes Then
      Exit Sub
   End If
   
   Application.EnableEvents = False
   ActiveSheet.Unprotect Password:="Scan"
   
   lngNextRowDB = Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Row + 1
   lngRowDB1 = lngNextRowDB
   
   Set wbDB = ActiveWorkbook
   Set wsDB = Sheets("DB")
   
   With Sheets("InputData")
        lngLastRowData = .Range("C1048576").End(xlUp).Row
       ' .Range("C4:J" & lngLastRowData).Copy Destination:=wsDB.Cells(lngNextRowDB, "A")
       For lngRow = 4 To lngLastRowData
           If .Cells(lngRow, "C") <> "" Then
               wsDB.Cells(lngNextRowDB, "A") = .Cells(lngRow, "C")
               wsDB.Cells(lngNextRowDB, "B") = .Cells(lngRow, "D")
               wsDB.Cells(lngNextRowDB, "C") = .Cells(lngRow, "E")
               wsDB.Cells(lngNextRowDB, "D") = .Cells(lngRow, "F")
               wsDB.Cells(lngNextRowDB, "E") = .Cells(lngRow, "G")
               wsDB.Cells(lngNextRowDB, "F") = .Cells(lngRow, "H")
               wsDB.Cells(lngNextRowDB, "G") = .Cells(lngRow, "J")
               lngNextRowDB = lngNextRowDB + 1
           End If
       Next
   End With
  lngRowDB2 = lngNextRowDB - 1
  
   'open external workbook
   Set wbConsolidate = Workbooks.Open(sPathFile)
   Set wsConsolidate = wbConsolidate.Sheets("ConsolidatedDB")
   wsConsolidate.Select
   lngNextRowConsolidate = wsConsolidate.Cells(Rows.Count, 1).End(xlUp).Row + 1
   
   'copy data from the DB sheet that was just added
   wbDB.Activate
   wsDB.Select
    With wsDB
       Application.CutCopyMode = False
      .Range("A" & lngRowDB1 & ":G" & lngRowDB2).Copy
    End With
   
   'switch to the consolidated workbook and paste
   wbConsolidate.Activate
   wsConsolidate.Cells(lngNextRowConsolidate, 1).Select
   ActiveSheet.Paste
   
   'close the other workbook and save the changes
   wbConsolidate.Close True
   
  
      
   MsgBox "Done consolidating data", , "Done"
   
      
      
Proc_Exit:
   On Error Resume Next
   'release object variables
   
   
    'delete data on the input sheet
   With Sheets("InputData")
      .Select
      .Range(.Cells(4, 1), .Cells(4, 1).SpecialCells(xlLastCell)).ClearContents
      .Cells(4, 1).Select
   End With
   
   Set wsConsolidate = Nothing
   Set wsDB = Nothing
   Set wbDB = Nothing
   Set wbConsolidate = Nothing
   Application.EnableEvents = True
   ActiveSheet.Protect Password:="Scan", DrawingObjects:=True, UserInterfaceOnly:=True, Contents:=True, Scenarios:=True
   ActiveSheet.EnableSelection = xlUnlockedCells
   Exit Sub
  
Proc_Err:
   Application.EnableEvents = True
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SendToDB"

   Resume Proc_Exit
   Resume
   
End Sub

Select allOpen in new window

the routine continues through the proc_exit and when it protects the sheet the cursor gets moved to the first unlocked cell. C4 is locked so the cursor can't be left there; you can either unlock that cell or set the protection options to allow locked cells to be selected

what about columns A and B? Will the user enter stuff here too?

if not, when does it get used?

nevermind, I see ... adding code to fill it out for the next entry row ~

Hi Chris,

see if this does what you need -- workbook attached:
114-Warehouse-Scan-151118_8p.xlsm

warm regards,
crystal

~ have an awesome day ~
114-Warehouse-Scan-151118_8p.xlsm

Hi Crystal,
Please see this file.
This is a working program that takes two different barcode scans and puts them into the sheet.

How it works is, The first scan (24-32 digits)) goes into C4. If it is in fact 24-32ish digits, it splits the code (which has spaces between each series of numbers)

First scan looks like this.  As soon as the scan is made is automatically splits the code across the row, and at the same time looks at the data sheet.
11048527 20OCT2015 J5525 005           (up to 29 digits) last number could be 005 or 05 or 5
11048527 20OCT2015 J5525 005 087     (up to 32 digits) last number could be 087 or 87 or 7

The difference between these two codes is the whether it has that last 3 digit or 2 digit or 1 digit number. This number is the Case count (how many cases on a skid). If the Case count is blank, as in the first code, it means the skid is full. Then the program looks at the data tab, pulls the item number from the list, and pulls the Flavor name as well, and fills it in.

The cursor then moves to the J column and waits for a short code.

DD-01-A   The short code is the warehouse racking location number. It is a barcode on the rack.
If the operator tries to enter a long code into this cell it does not accept it and if waits for a short code. The same thing happens in the C column if you try to scan a short code, it will also wait for a long code before proceeding.
A finished row looks like this :
Once the operator finishes a series of scans, could be 10 or 20 or 30 or 40, then he goes to the office dumps the data into the second  sheet in the operators main workbook, and at the same time sends the data to a second desktop computer. ( this is the code that you wrote that works great on its own).

I need to join these two working programs together. I hope this fills in some blanks for you.
Your updated code did not spit the code apart. The Barcode scanner only allows you to have a space between a string of characters, or a carriage return, as I need all the data, including the  rack location, on one row, and I need to filter searches later, I need all the data in separate cells. The spaces afterwards I do not need between the rows, which you have already taken out.

Let me know what you think of the explanation.
Thanks so much.
Chris
114-Warehouse-Scan-1-.xlsm

Hi Chris,

I downloaded the file and will come back to you ~

warm regards,
crystal

~ have an awesome day ~

thanks for the further explanation.  I will LOCK the cells that the scan also provides.  

Is your scanner programmed to enter a TAB or ENTER character after the scan? If not, it should be ...

Hi Chris,

the last file you attached does not have any of the work I did yesterday -- ok if I go back to that one?

warm regards,
crystal

~ have an awesome day ~

and actually, the code to parse is still there -- but perhaps not working.  I didn't understand what the scan looked like and now that you have told me, I will fix it.  Very important though for the scan to have a termination character like TAB or ENTER

and TAB would be best to terminate since the normal behavior of ENTER is to go to the next row ...

OK i am back ,
Sorry for the delay.
In meetings....
THe nature of the scanner is that it has a carriage return after the scan dumps the full string of digits.
There is no way to change that.
Chris

ok ... thanks, Chris.  I'll do some more testing ~

warm regards,
crystal

~ have an awesome day ~

The sheet I attacheched is the First part only. The part that accepts the code, and splits it then references the data tab to insert Flavor name and case count of its a full skid.


This sheet here is a step by step INSTRUCTIONS of what the code does, just look at the tab. There is NO VB it is just for reference and understanding of the process.
Does this help?
114-Warehouse-Step-1234.xlsx

Hi Chris,

yes, thank you ~ this is what I thought by looking at the code, especially after you gave examples of the scans, but it helps to verify my understanding by seeing the steps :)

warm regards,
crystal

~ have an awesome day ~

Your part works awesome, his part works awesome.

But making them work together is the hard part.

(mind you I think it's all because of cell protection issues from his sheet).
What do you need from me???
Thanks so much.
Chris

Hi Chris,

I am making the protection open just one cell so the scan can't go in the wrong place.  Question: if they accidentally scan another part in the Location cell, or vice-verse, what do you want to happen? A message box?

warm regards,
crystal

~ have an awesome day ~

Thanks Crystal,
The first scan HAS to go into the Cell D4 to start, that is the first Skid, the second skid has to go in the same row, right now it is J4 , but if we are re-writing the code, it doesn't have to skid a column, it can go into  "i4" if we want.

We don't even have to skip rows. It was the way it was made, no I realize skipped rows or a skipped column make things more of a pain. So we can do with out if need be.

As for errors, the way it is programmed right now, you cannot scan into any other cell then the one that is selected, however, if you try to scan a long code (skid) into a short code (location) the cursor does not move. A pop up would work. There is no  mechanism to warn the operator if they are trying to scan the wrong code in the wrong place, they will be checking the screen after each scan. If the cursor has moved and is waiting in an empty cell, the operator knows he did it right and its time to scan the next scan.

Hope that helps
Salutations
Christopher :)

thanks, Christopher

skipping rows is not problem, and keeping location in J is no problem either unless you WANT it to go into I, in which case I will change it.

I went ahead and put messages in telling them what is wrong if they scan the wrong information for the cell that is light yellow, which indicates the active cell.  Also changing the colors.

It should be ready to test soon ... just crossing the t's and dotting the i's :)

I am also adding comments so you can understand what is going on better ... since you are learning, I thought you'd like this ~

Also renaming variables so, again, the code is easier to understand

warm regards,
crystal

~ have an awesome day ~

Hi Chris,

hitting an error and I am not sure why so it is taking a bit longer ...

just wanted to let you know ~

warm regards,
crystal

~ have an awesome day ~

Hi Chris,

I figured out the problem ... putting the code in to do the lookup for flavour ~

warm regards,
crystal

~ have an awesome day ~

You are awesome.
Thanks
Chris

thank you, Chris ... nice to be appreciated :)

still testing ~

warm regards,
crystal

~ have an awesome day ~

Hi Chris,

I think this is it! Hopefully the Transfer to DB stuff still works too ~ I didn't test it because I don't have your path

attached:
114-Warehouse-Scan-151119_7p.xlsm

Press Alt-F11 to go to Visual Basic.  I changed code in:

Ark 1 (Input Data)
         -- code behind the sheet in the Worksheet_Change event, commented unused code below
This Workbook
        -- Workbook_Open event
and added ProtectMe and UnprotectMe to the end of mod_SendToDB

read through the code.  If you don't understand what is happening and want to, I'll be happy to explain  :)

warm regards,
crystal

~ have an awesome day ~
114-Warehouse-Scan-151119_7p.xlsm

oh! I should have removed Sheet1 ... was using it to test colors.

to remove it:
1. press Alt-F11 to go to the VBE (Visual Basic Editor, that has VBA -- Visual Basic for Applications)
2. double-click mod_SendToDB to switch to the code
3. press Ctrl-End to go to the bottom
4. click in the UnprotectMe procedure
5. press F5 to run! it
6. Right-click on the Sheet1 tab and choose: Delete
7. click on the InputData sheet to make it the active one
8. save and close workbook

We are soon close,
I noticed that if a code doesn't have the case count built in already from the barcode, it doesn't reference to the database for the full case count off the list.

To test it, I took codes in excel and pasted them in one by one to test.

504208 20OCT2015 J5572 5 16     (this skid has 16 boxes)
504208 20OCT2015 J5572 5           (this skid - no case count which means we need to pull from DB)
504208 20OCT2015 J5572 5 16   (this skid has 16 boxes)
504208 20OCT2015 J5572 5 87   (this skid has 87 boxes)
504208 20OCT2015 J5572 150   (this skid has 150 boxes)
504208 20OCT2015 J5572 5       (this skid - no case count which means we need to pull from DB)

This is the rack code again      DD-01-A  ( is there a way if any barcodes has a A or B or C at the end to change to A)  Just curious. I can do another question for that one after. Its not that important right now. But will need a fix eventually.

Awesome work :)
Chris
I will continue to test the Clear and send now.

Hi Chris,

> "reference to the database for the full case count off the list"

so, if case count is not included, do you want it to do a lookup to see if the case count was previously entered?

> "is there a way if any barcodes has a A or B or C at the end to change to A"

of course ;)  -- automatically change for B or C on end?

if all else is working ok, another question would be good for these though  as I have spent a lot of time getting it this far

thanks, Chris

warm regards,
crystal

~ have an awesome day ~

Oh an I found one more thing, The data sheet becomes UNHIDDEN, (not a problem, but if it is visible I would like it protected against accidental change. Or just keep it hidden.

I can't test the send fully until tomorrow when I get to work.


Hey Crystal.
I have another Project, it is much much easier. Maybe next week I will have a question ready,

oh! easy fix:

add this statement to ProtectMe (in mod_SendToDB)

Worksheets("Data").Visible = xlSheetHidden

Select allOpen in new window

 then Debug, Compile and Save

I am on a weird version of Excel Office for Mac, I am at home.  Can't get into the code. :(

I will start another question right now for the last couple adjustments.

by the way, I only made ProtectMe and UnprotectMe be logical names for YOU.  If you want to obscure the names, you can press Ctrl-H to Search and Replace.  Be sure to set the scope to the whole project, as opposed to just a procedure or module.

 then Debug, Compile and Save

If you run Parallels on the Mac, you can load Office for Windows ~

Don't have an Office Key or Office installed on my VMWARE, and haven't re-installed it yet, I had my machine re-built after a bad hard drive failure. :)

I am surprised, though, that the code won't run on a Mac -- I connect to people and help them build applications as my business ... and have helped several Mac users.  The shortcut keys are different but, for the most part, the code is the same (which is why I can help)

You may have to compile and save in that environment ... but of course, you can't test the consolidation stuff because the path is different.

In your next question, perhaps you want to request that to be variable too :)

VMWare has a Windows mode ... you just need to install Windows and Office ~

LOL, won't happen while bathing and feeding my 3 year old, and cook dinner for mom, and make lunches for tomorrow. Very very limited time while mom is still at work. Otherwise I pay the WRATH lol

I have mac 2011 build 14, THIS code seems to work , just the VB I can't get into.
Not much time left :(
I have to re-install vmware one of these days.
I have only one program i need windows for, and hunt needed that program since my new hard drive was installed 2 months ago

try
Fn-squigglythingButton-F11

OK next question is asked, its vague and no attachments.

f11 is sound only
Hmm

not F11 all by itself ... combination of 3 keys:

first press Fn and the squiggly picture button

then press F11

The developer tab was not enabled, I got that enabled, but wow, it opens each element of VB in 6 floating windows,all over the desktop, LOL.

I have to go to bed. Up 4:30 Am tomorrow for work.
Chat soon.
Thanks
Chris
Tomorrow I will try to figure where we left off
Night

Awesome Job. Thanks so much for your help.

Consider yourself Knocked Over...... LOL

thank you, Chris, I am now a "Master" and get an EE shield :) I am glad it was you