Solved

VB in Excel skips or doesn't execute section where values of User Input form set back to 0

Posted on 2011-03-20
39
219 Views
Last Modified: 2012-05-11
Hello Experts.

This question relates to resetting the fields of a user input form back to zero values after the clerk has input amounts and clicked on an object that saves their input.  

The input form and temp data is in one workbook and then the permanent data is being saved to a different workbook before returning to the input form's workbook to clear the fields

Below is the code.  If I use F8 to step through the VB code it works.  But if I click on the object that executes that same code it doesn't .  I don't understand why.

I have highlighted where the section that isn't working starts with
 ' *******RESET USER INPUT FIELDS TO VALUE OF ZERO ************

As an aside, Is there a better way to do the clearing than how I am attempting to do it?

Sub PriorDayDropAddBackPrintandSave()
'
' PrintandSave Macro
' Print Form and Save
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1_1 As Worksheet
    Dim ws2_1 As Worksheet
    Dim strCopyFromFile As String, strSaveToFile As String
    Dim inputRange_ws1_1 As Range
    Dim outputRange_ws2_1 As Range
    Dim LastRowInput_ws1_1 As Long
    Dim intResponse As Integer
    Dim intRespPriorDayDropPrintandSave As Integer, int2RespPriorDayDropPrintandSave As Integer
    
    intRespPriorDayDropPrintandSave = MsgBox("By Clicking Ok you hereby certify that the form is true and correct", vbOKCancel)
    If intRespPriorDayDropPrintandSave = vbOK Then
        '/user clicked OK
        
            ' Add This Prior Day Drop Add Back Data to Shift Data workbook on worksheet Prior Day Add Back Data
            '
            intResponse = MsgBox("Are You sure your form Prior Day Drop Added Back report is complete and correct and you want to transfer Data?", vbOKCancel + vbInformation)
            If intResponse = vbOK Then
                'Resume, the user pressed ok
                ' Start of Final Prior Day Drop Add Back Print Report
'                    Application.ScreenUpdating = False

                ' Copy Range to Ensure NO "Draft Only" is Visible and Paste to area so it prints
                    Range("X18").Select
                    Application.CutCopyMode = False
                    Selection.Copy
                    Range("C21").Select
                    ActiveSheet.Paste
                   
                ' Print function
                    Application.ScreenUpdating = True
' *                    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
                '    ActiveWindow.SelectedSheets.PrintOut Copies:=1
' *                    Workbooks("Machine Money Pull.xlsm").Worksheets("Prior Day Drop Add Back").Activate
' *                    Range("PriorDayDropAddBackStartInputCell").Select
            
                ' End Print of Final Prior Day Drop Add Back
                    
'                    Application.ScreenUpdating = False
                        
                    strCopyFromFileInMachineMoneyPull = "Machine Money Pull.xlsm"
                    strSaveToFileInMachineMoneyPull = "C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\BCJ Master Forms\Workpapers\Shift Details Data.xlsx"
                        
                     Set wb1 = ActiveWorkbook
                     Set ws1_1 = wb1.Sheets("Prior Day Add Back Export Temp")
                    
                         
                  '~~> Test if save to workbook is open
                     On Error Resume Next
                     Set wb2 = Workbooks(Dir(strSaveToFileInMachineMoneyPull))
                     Set ws2_1 = wb2.Sheets("Prior Day Add Back Data")
                            
                        
                     On Error GoTo 0
                        
                       If wb2 Is Nothing Then
                            Set wb2 = Workbooks.Open(strSaveToFileInMachineMoneyPull)
                            Set ws2_1 = wb2.Sheets("Prior Day Add Back Data")
                        
                        End If
                    
                  '~~> Begin Save info
                     LastRowInput_ws1_1 = ws1_1.Range("A" & Rows.Count).End(xlUp).Row
                     LastRowOutput_ws2_1 = ws2_1.Range("A" & Rows.Count).End(xlUp).Row + 1
                        
                     Set inputRange_ws1_1 = ws1_1.Range("A5:AC" & LastRowInput_ws1_1)
                     Set outputRange_ws2_1 = ws2_1.Range("A" & LastRowOutput_ws2_1)
                        
                     inputRange_ws1_1.Copy
                       outputRange_ws2_1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                       :=False, Transpose:=False
                     Application.CutCopyMode = False
                   '    Application.ScreenUpdating = True
                        
                   ' Save updated data file then close
                        ActiveWorkbook.Save
                        ActiveWorkbook.Close
                   '    Application.Visible = True
  '                      Application.ScreenUpdating = True
          
          Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Activate
          Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Visible = True
          Range("H23").Select
          
 '         Application.ScreenUpdating = False
 
 ' *******RESET USER INPUT FIELDS TO VALUE OF ZERO ************
                Range("G25").Select
                ActiveCell.FormulaR1C1 = "0"
                Range("G25").Select
                Selection.Copy
                Range(Selection, Selection.End(xlDown)).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
                Range("H23").Select
                ActiveCell.FormulaR1C1 = "0"
                Range("H23").Select
 ' *******END RESET USER INPUT FIELDS TO VALUE OF ZERO ************
            
                     
'          Application.ScreenUpdating = True
          
          Workbooks("Machine Money Pull.xlsm").Sheets("Login").Visible = True
          
          Workbooks("Machine Money Pull.xlsm").Sheets("Login").Activate
          Range("I39").Select
          Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Visible = False
                    
                        
                    Else
                      'The user pressed cancel
                      'Cancel event
                End If
        
    End If
          
             
End Sub

Open in new window

0
Comment
Question by:wlwebb
  • 17
  • 17
  • 5
39 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Differences between step-by-step and full execution are often a missing focus (ActiveSheet, Selection and the like), or errors ignored by the RESUME NEXT statement.

Using Active* is dangerous. You should try to refrain from using them. For example
   Range("H23").Select
   ActiveCell.FormulaR1C1 = "0"
can be written as
   [H23].FormulaR1C1 = "0"
or
   Range("H23").FormulaR1C1 = "0"

If you tell us more details on what does not work, we might be able to provide better help.
0
 

Author Comment

by:wlwebb
Comment Utility
Qlemo
Thanks for responding so quickly!
Thinking about your answer, I can see where using ActiveCell could be dangerous.

The problem, or what is not working is the resetting of the input fields back to a value of zero. That is the section you were referring to.  

What it appears to me that is happening, is that after the code Copies the temp data out to the Permanent workbook Excel isn't returning to this workbook or worksheet as it's active workbook.  Thus the fields in H23 and G23:Gxx are left at whatever value the clerk input in this input form.  I am trying to reset those fields to zero so that when the next user opens this form, the values in the form will be zero.
0
 

Author Comment

by:wlwebb
Comment Utility
Qlemo
Also I don't understand (maybe it's because I am misinterpreting how this works but,) why I can execute the code step by step using F8 and it will step through the code and work correctly but when I use the Form Control on the Excel sheet and assign the same macro to that Form Control that it doesn't work.

Am I misinterpreting how F8 works in VB?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I agree there should be no difference - but there is. F8 introduces delays, allowing asynchronous calls to finish. Sometimes that makes a difference, but it is always a failure in the logic causing this behaviour.
I will look at your code now, and try to find out what the reason might be.
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 425 total points
Comment Utility
wlwebb: There are many places where the code could fail.

1)
Range("X18").Select

You have not told the code where the range is. I mean in which Sheet. Define a sheet first and then address the rage with the right sheet. For example

Sheets("Sheet1").Range("X18").Select

2) Avoid .Select as Qlemo suggested. This

            Range("X18").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("C21").Select
            ActiveSheet.Paste

can be written as

            Sheets("Sheet1").Range("X18").Copy Sheets("Sheet1").Range("C21")

3) I remember writing most of the code and I see that you have made lot of changes to it. Two major things that I notice that you have not declared the variables and missing the references.

Can you upload your latest file so that I can rework on then as per your latest requirements?

Sid
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Just a suggestion: If you have many commands to apply to the same object (in particular the sheet), you should use the with statement:

with Sheets("Sheet1")
  .Range("X18").Copy .Range("C21")
 ' ...
end with
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 75 total points
Comment Utility
Your "reset" part (lines 84 to 103) can be compressed to this lines:

With Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back")
   Range("G25",[G25].End(xlDown)).Value = "0"
   Range("H23").Value = "0"
End With

Further you won't need to change the "visible" attribute of the worksheet.
0
 

Author Comment

by:wlwebb
Comment Utility
Thanks guys.  Attached is the latest file.  

Sid, as a comment since you saw the original file.  The code you reworked is still used on the Sheet "Machine Pull".  I copied that code to attempt (miserably it seems LOL) to adapt to this sheet "Prior Day Drop Add Back".

The object on that sheet that is the problem is the one labeled "Finished - Print Form Save and Logout

 PS, I know most of this would be better in Access, but that isn't an option at this moment.  The names used here are fictitious so no need to worry about that stuff.
Machine-Money-Pull.xlsm
0
 

Author Comment

by:wlwebb
Comment Utility
PS
I am going to go into the code on this end and try writing and testing code that Qlemo suggests so that I can learn some stuff.

Thanks Qlemo
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I am on it it :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Quick question.

I believe that Range("X18") is from  Sheets("Prior Day Drop Add Back"). What about Range("C21") where you paste it?

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
X18 and C18 are both on Sheets("Prior Day Drop Add Back")

I originally had all of these ranges written as Sheets("Sheet1").Range("X18").Select etc... but thought maybe that was causing my problems so I went in and deleted all those codings and replaced with just Range......

When you really don't know what you're doing you try just about anything!  LOL ;-)

0
 

Author Comment

by:wlwebb
Comment Utility
Sid,

Looking for a Contract Job?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I am almost done. Another question.

When you say Activeworkbook.close, are you trying to close Shift Details Data.xlsx?

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
Yes, Essentially this workbook is being used as a temp where the clerk inputs the data but all data is temp and saved out to the permanent data file that is just an xlsx file instead of xlsm

So after it copies the info out to Shift Details Data then it closes that workbook.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
5 mins and it will be ready :)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 425 total points
Comment Utility
Is this what you are trying?

Sub PriorDayDropAddBackPrintandSave()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1_1 As Worksheet, ws2_1 As Worksheet
    Dim strCopyFromFile As String, strSaveToFile As String
    Dim inputRange_ws1_1 As Range, outputRange_ws2_1 As Range
    Dim LastRowInput_ws1_1 As Long, intResponse As Long
    Dim intRespPriorDayDropPrintandSave As Long, int2RespPriorDayDropPrintandSave As Long
    Dim strCopyFromFileInMachineMoneyPull As String, strSaveToFileInMachineMoneyPull As String
    Application.ScreenUpdating = False
    
    intRespPriorDayDropPrintandSave = MsgBox("By Clicking Ok you hereby certify that the form is true and correct", vbOKCancel)
    If intRespPriorDayDropPrintandSave = vbOK Then
        intResponse = MsgBox("Are You sure your form Prior Day Drop Added Back report is complete and correct and you want to transfer Data?", vbOKCancel + vbInformation)
        If intResponse = vbOK Then
            ' Copy Range to Ensure NO "Draft Only" is Visible and Paste to area so it prints
            With Sheets("Prior Day Drop Add Back")
                .Range("X18").Copy .Range("C21")
            End With
                   
            strCopyFromFileInMachineMoneyPull = "Machine Money Pull.xlsm"
            strSaveToFileInMachineMoneyPull = "C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\BCJ Master Forms\Workpapers\Shift Details Data.xlsx"
                        
            Set wb1 = ActiveWorkbook
            Set ws1_1 = wb1.Sheets("Prior Day Add Back Export Temp")
                    
            '~~> Test if save to workbook is open
            On Error Resume Next
            Set wb2 = Workbooks(Dir(strSaveToFileInMachineMoneyPull))
            Set ws2_1 = wb2.Sheets("Prior Day Add Back Data")
            On Error GoTo 0
      
            If wb2 Is Nothing Then
                Set wb2 = Workbooks.Open(strSaveToFileInMachineMoneyPull)
                Set ws2_1 = wb2.Sheets("Prior Day Add Back Data")
            End If
  
            '~~> Begin Save Permanent Data File info
            LastRowInput_ws1_1 = ws1_1.Range("A" & Rows.Count).End(xlUp).Row
            LastRowOutput_ws2_1 = ws2_1.Range("A" & Rows.Count).End(xlUp).Row + 1
               
            Set inputRange_ws1_1 = ws1_1.Range("A5:AC" & LastRowInput_ws1_1)
            Set outputRange_ws2_1 = ws2_1.Range("A" & LastRowOutput_ws2_1)
               
            inputRange_ws1_1.Copy
            outputRange_ws2_1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
            wb2.Close savechanges:=True
            
            Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Activate
            Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Visible = True

            ' *******RESET USER INPUT FIELDS TO VALUE OF ZERO ************
            With wb1.Sheets("Prior Day Drop Add Back")
                Range("G25:G30,H23").ClearContents
            End With
            ' *******END RESET USER INPUT FIELDS TO VALUE OF ZERO ************
   
            Workbooks("Machine Money Pull.xlsm").Sheets("Login").Visible = True
            Workbooks("Machine Money Pull.xlsm").Sheets("Prior Day Drop Add Back").Visible = False
        Else
            'The user pressed cancel
            'Cancel event
        End If
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Open in new window


Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 425 total points
Comment Utility
Also note that to reset the data, you don't need to use FormulaR1C1 and then copy paste :)

You can directly do this

            ' *******RESET USER INPUT FIELDS TO VALUE OF ZERO ************
            With wb1.Sheets("Prior Day Drop Add Back")
                Range("G25:G30,H23").ClearContents
            End With
            ' *******END RESET USER INPUT FIELDS TO VALUE OF ZERO ************
   

Open in new window


Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 425 total points
Comment Utility
Oops, I missed a "DOT" before Range("G25:G30,H23").ClearContents.

Please change it to

            With wb1.Sheets("Prior Day Drop Add Back")
                .Range("G25:G30,H23").ClearContents
            End With

Open in new window


Sid
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:wlwebb
Comment Utility
Your 5 minutes to get it to work is equivalent to me spending the past 1000 hours trying all kinds of different things trying to get it to work.

Only one difference.

Yours works!

Har har LOL ;-))))

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Glad it works :)

I would only request you to not just copy the codes but actually understand it how it works as it will help you in the long run ;)

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
Errors out on
      .Range("X18").Copy .Range("C21")


about line 17
in the section
    ' Copy Range to Ensure NO "Draft Only" is Visible and Paste to area so it prints
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
2 mins. Checking it

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
Thought I was understanding what it was doing and why the coding.  That is why I attempted copy, paste and modifying.
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 425 total points
Comment Utility
Ok Replace

            With Sheets("Prior Day Drop Add Back")
                .Range("X18").Copy .Range("C21")
            End With

by

With Sheets("Prior Day Drop Add Back")
    .Range("X18").Copy
    .Range("C21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End With

Open in new window


Sid
0
 

Author Comment

by:wlwebb
Comment Utility
PS, You have either missed my question or chose to ignore it.

Looking for a Contract Job? ;-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
The problem is that X18 and C21 are merged cells and hence you were getting that error ;)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>PS, You have either missed my question or chose to ignore it.

Sorry which question?

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
Looking for a Contract Job?
0
 

Author Comment

by:wlwebb
Comment Utility
Converting all of this to Access
0
 

Author Comment

by:wlwebb
Comment Utility
WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

One question though.  The "Clearcontents" command does just that, it clears the contents.  Is there a way to reset them to zero.  Just thinking this way - " If eventually this converts to an Access db, then those fields are null and if the clerk doesn't type a zero, then wouldn't the field in the final permanent data sheet have a blank or null field instead of a zero?
0
 

Author Comment

by:wlwebb
Comment Utility
PS Sid and Qlemo.  What do each of you think is a fair allocation of the points awarding?  I would think that some points should be awarded to both.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>Converting all of this to Access

Ah! I did miss that. Sorry about that :)

Yes, it can be done in Access as well. It all depends the kind of data that you would be storing. Personally, I always prefer Excel over Access but then that is me :)

>>> Looking for a Contract Job?

I do contract jobs. For that you may click the "Hire Me" button in my profile :)

Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 425 total points
Comment Utility
>>>>One question though.  The "Clearcontents" command does just that, it clears the contents.  Is there a way to reset them to zero.

Change

.Range("G25:G30,H23").ClearContents

to

.Range("G25:G30,H23").Value = 0

>>>PS Sid and Qlemo.  What do each of you think is a fair allocation of the points awarding?  I would think that some points should be awarded to both.

I agree :)

Sid
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Sid,
I first thought
    Range("G25:G30,H23").Value = 0
would be dangerous, as it won't work on my side of the ocean  - comma is decimal separator, I need to use semicolon instead.
But Range does not care for funny little national things.

Regarding the points - I prefer to get "some" while Sid should get "most". Just accept answers as you think they are valuable.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>But Range does not care for funny little national things.

Yeah you are right Range is beyond Range ;)

>>>Regarding the points - I prefer to get "some" while Sid should get "most". Just accept answers as you think they are valuable.

No. I suggest an equal split :)

Sid
0
 

Author Comment

by:wlwebb
Comment Utility
The value = 0 worked to reset the cells to zero. I will look back over thread to indicate solutions and award the points when I get back to office in a while.  Family wanted to go to breakfast.  
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Fair enough :)

Enjoy your BF.

Sid
0
 

Author Closing Comment

by:wlwebb
Comment Utility
Thanks Sid and Qlemo
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now