VBA Code, If, End if, Block errors

C

caseyc17

New Member
Joined
Oct 26, 2010
Messages
31
Hello everyone,

I have a code that takes Access form info, transfers it to an excel template. works well, until now.

I had to create a new template and depending on data in the population determine which form to open.

So if there is a flag open one template, otherwise open the other.

My code is below. Im stuck on how to enter the End If's I keep getting errors. I've moved the End Ifs around a little to trouble shoot... going to pound head against wall now!!!

Any help would be appreciated.

CODE: ​
Private Sub Value_Calculation_Click()
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template"
End If
 With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c12").Value = Me.[June2010_Principle].Value
.Range("e12").Value = Me.[June2010_Interest].Value
.Range("f12").Value = Me.[Total_2010_Pmt].Value
.Range("c13").Value = Me.[June2011_Principle].Value
.Range("e13").Value = Me.[June2011_Interest].Value
.Range("f13").Value = Me.[Total_2011_Pmt].Value
.Range("c14").Value = Me.[June2012_Principle].Value
.Range("e14").Value = Me.[June2012_Interest].Value
.Range("f14").Value = Me.[Total_2012_Pmt].Value

.Range("c17").Value = Me.[2010 Award Plan.TOTAL_DEFERRED_AWARD].Value
.Range("c19").Value = Me.[June2010_Vesting_Principle].Value
.Range("e19").Value = Me.[June2010_Vesting_Interest].Value
.Range("h19").Value = Me.[June2010_Payment_Type].Value
.Range("i20").Value = Me.[June2011_Payment].Value
.Range("h20").Value = Me.[June2011_Payment_Type].Value
.Range("i21").Value = Me.[June2012_Payment].Value
.Range("h21").Value = Me.[June2012_Payment_Type].Value

.Range("c24").Value = Me.[2010 Top Slice Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c26").Value = Me.[January2011_Vesting_Principle].Value
.Range("h26").Value = Me.[January2011_Payment_Type].Value
.Range("i26").Value = Me.[January2011_Shares].Value
.Range("c27").Value = Me.[January2012_Vesting_Principle].Value
.Range("h27").Value = Me.[January2012_Payment_Type].Value
.Range("i27").Value = Me.[January2012_Shares].Value
.Range("c28").Value = Me.[January2013_Vesting_Principle].Value
.Range("h28").Value = Me.[January2013_Payment_Type].Value
.Range("i28").Value = Me.[January2013_Shares].Value


If Value.Me.EMPLOYEE_CATEGORY_NAME Is Null Then

Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template"
End If
 With oXL
.Visible = True
.workbooks.Open (sFullPath)
.Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
.Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c12").Value = Me.[June2010_Principle].Value
.Range("e12").Value = Me.[June2010_Interest].Value
.Range("f12").Value = Me.[Total_2010_Pmt].Value
.Range("c13").Value = Me.[June2011_Principle].Value
.Range("e13").Value = Me.[June2011_Interest].Value
.Range("f13").Value = Me.[Total_2011_Pmt].Value
.Range("c14").Value = Me.[June2012_Principle].Value
.Range("e14").Value = Me.[June2012_Interest].Value
.Range("f14").Value = Me.[Total_2012_Pmt].Value

.Range("c17").Value = Me.[2010 Award Plan.TOTAL_DEFERRED_AWARD].Value
.Range("c19").Value = Me.[June2010_Vesting_Principle].Value
.Range("e19").Value = Me.[June2010_Vesting_Interest].Value
.Range("h19").Value = Me.[June2010_Payment_Type].Value
.Range("i20").Value = Me.[June2011_Payment].Value
.Range("h20").Value = Me.[June2011_Payment_Type].Value
.Range("i21").Value = Me.[June2012_Payment].Value
.Range("h21").Value = Me.[June2012_Payment_Type].Value

.Range("c24").Value = Me.[2010 Top Slice Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
.Range("c26").Value = Me.[January2011_Vesting_Principle].Value
.Range("h26").Value = Me.[January2011_Payment_Type].Value
.Range("i26").Value = Me.[January2011_Shares].Value
.Range("c27").Value = Me.[January2012_Vesting_Principle].Value
.Range("h27").Value = Me.[January2012_Payment_Type].Value
.Range("i27").Value = Me.[January2012_Shares].Value
.Range("c28").Value = Me.[January2013_Vesting_Principle].Value
.Range("h28").Value = Me.[January2013_Payment_Type].Value
.Range("i28").Value = Me.[January2013_Shares].Value

End With

Exit_Value_Calculation_Click:
Exit Sub
Err_Value_Calculation_Click:
MsgBox Err.Description
Resume Exit_Value_Calculation_Click

End Sub
 
Sort by date Sort by votes
caseyc17 said:
Thanks James. I don't want to close or save excel. I'm basically updating an excel template that the user would then save to their directory. I feel like I'm missing something silly. I dumbed down the code and it still isn't working.

I think it has to do with the "if" statement. I've tried several different variations of ""'s, )'s and ]'s.
Click to expand...
well I hope you noticed I did change your IF statements

specifically ...
you dim an oXL object

then you do this ...
If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y"

and if it is Y then you create the excel object

then you close your IF condition and use the oXL object

but what if it's NOT Y ?

then the oXL never gets created, but you go ahead and use it anyway

that's a big failure,

so I moved some things around
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
james_lankford said:
then you do this ...
If Value.Me.EMPLOYEE_CATEGORY_NAME = "Y"
Click to expand...

And I had changed that in mine because Value.Me.EMPLOYEE_CATEGORY_NAME is completely wrong syntax anyway. Nothing would be before the Me. part to being with. So I had changed it to this:

Me.EMPLOYEE_CATEGORY_NAME.Value = "Y"

or simply

Me.EMPLOYEE_CATEGORY_NAME = "Y"

If that is even what we're checking. So, that's why I want to see the entire new code that was revised because I'm sure that something got overlooked when trying to fix everything that James and I were saying.
 
Upvote 0
Thanks guys. Still plugging away here. Very frustrated, but will keep working. I've twisted and turned this aroud quite a bit. I really appreciate your help.​
 
Last edited:
Upvote 0
Here's my modifications directly (I pasted into my VBA window to do them so you should be able to copy and paste to see if it works):
Rich (BB code): 
Private Sub Value_Calculation_Click()
 
    On Error GoTo Err_Value_Calculation_Click
 
    Dim oXL As Object
    Dim sFullPath As String
    Dim sPath As String
    Dim xlWB As Object
 
    Set oXL = CreateObject("Excel.Application")
 
    sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template.xls"
 
   ' use the workbook object
   Set xlWB = oXL.Workbooks.Open(sFullPath)
 
    oXL.Visible = True
 
    With xlWB.ActiveSheet
 
        If Me.[EMPLOYEE_CATEGORY_NAME] = "Y" Then
            .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
            .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
            .Range("c12").Value = Me.[June2010_Principle].Value
            .Range("e12").Value = Me.[June2010_Interest].Value
 
        Else
 
            .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
 
        End If
 
    End With
 
   ' use this to save the workbook
   xlWB.Close True
   'Need to close and quit and set to nothing in order for other issues to be avoided.
   oXL.Quit
   Set oXL = Nothing
 
Exit_Value_Calculation_Click:
    Exit Sub
 
Err_Value_Calculation_Click:
    MsgBox Err.Description
    Resume Exit_Value_Calculation_Click
 
End Sub
 
' Code that works perfectly:  <<<<<<<< No it doesn't as it is affecting other things
 
Private Sub Value_Calculation_Click()
    On Error GoTo Err_Value_Calculation_Click
 
    Dim oXL As Object
    Dim sFullPath As String
    Dim sPath As String
    Dim xlWB As Object
 
    Set oXL = CreateObject("Excel.Application")
 
    sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template.xls"
 
    Set xlWB = oXL.Workbooks.Open(sFullPath)
 
    oXL.Visible = True
 
    With xlWB.ActiveSheet
 
        .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
        .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
        .Range("c12").Value = Me.[June2010_Principle].Value
        .Range("e12").Value = Me.[June2010_Interest].Value
    End With
 
   ' You need this too or else it will be hanging open and you not able to do anything with it.
   xlWB.Close True
 
   oXL.Quit
   Set oXL = Nothing
 
Exit_Value_Calculation_Click:
    Exit Sub
 
Err_Value_Calculation_Click:
    MsgBox Err.Description
    Resume Exit_Value_Calculation_Click
 
End Sub

HOWEVER - in your code you have this part but without any kind of header. But based on the error handler it looks like it is the same procedure as the one just above it. So what is up with that?
Rich (BB code): 
On Error GoTo Err_Value_Calculation_Click
Dim oXL As Object
Dim sFullPath As String
Dim sPath As String
Dim xlWB As Object
Set oXL = CreateObject("Excel.Application")
If Me.EMPLOYEE_CATEGORY_NAME = "Y" Then
    sFullPath = CurrentProject.Path & "\Participants\Templates\Code Staff Stock Outstanding Template.xls"
    Set xlWB = oXL.Workbooks.Open(sFullPath)
    oXL.Visible = True
    With xlWB.ActiveSheet
        .Workbooks.Open (sFullPath)
        .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
        .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
        .Range("c12").Value = Me.[June2010_Principle].Value
        .Range("e12").Value = Me.[June2010_Interest].Value
    End With
    ' you don't use a colon after the Else just use ELSE
Else
 
    sFullPath = CurrentProject.Path & "\Participants\Templates\Stock Outstanding Template.xls"
    Set xlWB = oXL.Workbooks.Open(sFullPath)
    oXL.Visible = True
    With xlWB.ActiveSheet
        .Workbooks.Open (sFullPath)
        .Range("A8").Value = Me.[Active_Terms_Sept_End.Name].Value
        .Range("c10").Value = Me.[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD].Value
    End With
End If
' close and save
xlWB.Close True
oXL.Quit
Set oXL = Nothing
Exit_Value_Calculation_Click:
Exit Sub
Err_Value_Calculation_Click:
MsgBox Err.Description
Resume Exit_Value_Calculation_Click
End Sub

Also, what is up with this:

[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD]

Is that the actual FIELD NAME of your field or name of your text box? Why is there a period in there?
 
Upvote 0
boblarson said:
what is up with this:

[2009 Award Plan.TOTAL_INITIAL_DEFERRED_AWARD]

Is that the actual FIELD NAME of your field or name of your text box? Why is there a period in there?
Click to expand...

Yes, that's a field name. it is the title from the data in the table.


Also, I want the excel to hang open. I want to dump the contents of the form into excel and be able to work with it.

Finally, There's got to be an issue with the If statement.

If Me.[EMPLOYEE_CATEGORY_NAME] = "Y" Then

I tried "Y" and ("Y"). I'm testing a record that has a Y flag and it's not entering the info like it should, but follows the else instructions.
 
Upvote 0
Well, for one - having the period there in the field name can totally honk Access up. It doesn't like those, especially in VBA. You really should get rid of those.

Second - what datatype is Employee_Category_Name? The title sounds like it should be a name of a category not just a letter. So what is it and what is stored in that field?
 
Upvote 0
ok! I reworked some of my data, and got it to work. Thank you gentlemen for your help!

I determined that the Text "Y" was the issue, once I changed the data to 1's and 0's it worked like a charm. I'm assuming that the formula wasn't recognizing the text? (the field was a text field, now it's a number field)

Now on to the fun stuff. My query has data, which I can now dowload onto my excel template as a one off. However... I've now been asked to run a group of the data and produce them as files in templates.

Other than running one individually while recording a macro and then setting the "save as" to autopopulate the file name - not a clue how to start. any additional help would be appreciated. I'm basically visualizing printing statements from acess into excel templates and autosaving them. Didn't find any great examples in the search.
 
Upvote 0
Working on the following code:

Sub SampleSave()
ChDir _
"G:\human resources\...\...\...Summaries"
ActiveWorkbook.SaveAs Filename:= _
"G:\human resources\...\...\...Summaries\Want this to be a dynamic, based on a cell in the workbook.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

any ideas on how to make this a dynamic value, maybe a last name or something?
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
245
mumps
mumps
C
  • Question Question
Print from database
Replies
0
Views
282
Charlreena
C
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
S
  • Solved
Can you help me to shorten this code? Thanks a lot
Replies
7
Views
587
saftawy1
S
J
  • Question Question
open view
Replies
4
Views
371
rollis13
rollis13

Forum statistics

Threads
1,225,155
Messages
6,183,211
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top