Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 17
  • 5
39 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 35175060
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
ID: 35175070
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
ID: 35175080
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 71

Expert Comment

by:Qlemo
ID: 35175128
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 1700 total points
ID: 35175129
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 71

Expert Comment

by:Qlemo
ID: 35175141
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 71

Assisted Solution

by:Qlemo
Qlemo earned 300 total points
ID: 35175154
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
ID: 35175174
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
ID: 35175178
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
ID: 35175185
I am on it it :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35175188
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
ID: 35175198
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
ID: 35175199
Sid,

Looking for a Contract Job?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35175203
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
ID: 35175214
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
ID: 35175219
5 mins and it will be ready :)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1700 total points
ID: 35175222
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 1700 total points
ID: 35175229
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 1700 total points
ID: 35175233
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
 

Author Comment

by:wlwebb
ID: 35175234
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
ID: 35175240
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
ID: 35175246
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
ID: 35175250
2 mins. Checking it

Sid
0
 

Author Comment

by:wlwebb
ID: 35175251
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 1700 total points
ID: 35175255
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
ID: 35175258
PS, You have either missed my question or chose to ignore it.

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

Expert Comment

by:SiddharthRout
ID: 35175262
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
ID: 35175265
>>>PS, You have either missed my question or chose to ignore it.

Sorry which question?

Sid
0
 

Author Comment

by:wlwebb
ID: 35175288
Looking for a Contract Job?
0
 

Author Comment

by:wlwebb
ID: 35175290
Converting all of this to Access
0
 

Author Comment

by:wlwebb
ID: 35175309
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
ID: 35175314
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
ID: 35175315
>>>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 1700 total points
ID: 35175318
>>>>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 71

Expert Comment

by:Qlemo
ID: 35175424
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
ID: 35175432
>>>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
ID: 35175471
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
ID: 35175511
Fair enough :)

Enjoy your BF.

Sid
0
 

Author Closing Comment

by:wlwebb
ID: 35176307
Thanks Sid and Qlemo
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

636 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