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

Need some help here getting this to run.

I need the cursor to go back up to C4 after it sends and clears the sheet.
Thanks
114-Warehouse-Scan-151118-1-.xlsm
Consolidate.xlsm
chris pikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

is that all you need?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
seems to me that the protection on the sheet is not set up properly ... input cells should be unLOCKED too.
0
Saurabh Singh TeotiaCommented:
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

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

regmigrantCommented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
what about columns A and B? Will the user enter stuff here too?

if not, when does it get used?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
nevermind, I see ... adding code to fill it out for the next entry row ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
chris pikeAuthor Commented:
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 :
Sample of finished row,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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

I downloaded the file and will come back to you ~

warm regards,
crystal

~ have an awesome day ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
and TAB would be best to terminate since the normal behavior of ENTER is to go to the next row ...
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
ok ... thanks, Chris.  I'll do some more testing ~

warm regards,
crystal

~ have an awesome day ~
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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 :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

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

warm regards,
crystal

~ have an awesome day ~
0
chris pikeAuthor Commented:
You are awesome.
Thanks
Chris
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Chris ... nice to be appreciated :)

still testing ~

warm regards,
crystal

~ have an awesome day ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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,
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oh! easy fix:

add this statement to ProtectMe (in mod_SendToDB)
Worksheets("Data").Visible = xlSheetHidden

Open in new window


 then Debug, Compile and Save
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
If you run Parallels on the Mac, you can load Office for Windows ~
0
chris pikeAuthor Commented:
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. :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
VMWare has a Windows mode ... you just need to install Windows and Office ~
0
chris pikeAuthor Commented:
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
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
try
Fn-squigglythingButton-F11
0
chris pikeAuthor Commented:
OK next question is asked, its vague and no attachments.
0
chris pikeAuthor Commented:
f11 is sound only
Hmm
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
not F11 all by itself ... combination of 3 keys:

first press Fn and the squiggly picture button

then press F11
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

almost done! it compiles, ready to test

sweet dreams!  When you wake up, I will have it posted :)

btw, it you mark this last Q as answered, you will knock me over 50 K :)

warm regards,
crystal

~ have an awesome day ~
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor Commented:
Awesome Job. Thanks so much for your help.
1
chris pikeAuthor Commented:
Consider yourself Knocked Over...... LOL
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Chris, I am now a "Master" and get an EE shield :) I am glad it was you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.