Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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

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
wlwebb
Asked:
wlwebb
  • 17
  • 17
  • 5
7 Solutions
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
wlwebbAuthor Commented:
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
 
wlwebbAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
SiddharthRoutCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
wlwebbAuthor Commented:
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
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
I am on it it :)

Sid
0
 
SiddharthRoutCommented:
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
 
wlwebbAuthor Commented:
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
 
wlwebbAuthor Commented:
Sid,

Looking for a Contract Job?
0
 
SiddharthRoutCommented:
I am almost done. Another question.

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

Sid
0
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
5 mins and it will be ready :)

Sid
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
2 mins. Checking it

Sid
0
 
wlwebbAuthor Commented:
Thought I was understanding what it was doing and why the coding.  That is why I attempted copy, paste and modifying.
0
 
SiddharthRoutCommented:
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
 
wlwebbAuthor Commented:
PS, You have either missed my question or chose to ignore it.

Looking for a Contract Job? ;-)
0
 
SiddharthRoutCommented:
The problem is that X18 and C21 are merged cells and hence you were getting that error ;)

Sid
0
 
SiddharthRoutCommented:
>>>PS, You have either missed my question or chose to ignore it.

Sorry which question?

Sid
0
 
wlwebbAuthor Commented:
Looking for a Contract Job?
0
 
wlwebbAuthor Commented:
Converting all of this to Access
0
 
wlwebbAuthor Commented:
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
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
>>>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
 
SiddharthRoutCommented:
>>>>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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
SiddharthRoutCommented:
>>>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
 
wlwebbAuthor Commented:
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
 
SiddharthRoutCommented:
Fair enough :)

Enjoy your BF.

Sid
0
 
wlwebbAuthor Commented:
Thanks Sid and Qlemo
0
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 17
  • 17
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now