Solved

Excel 2010: Macro-enabled workbook error

Posted on 2015-02-19
10
64 Views
Last Modified: 2016-02-12
I have an Excel workbook with a worksheet that runs VBA code when a button is clicked. On one computer, when I open the worksheet, I get the following error message:

Can't exit design mode because Control 'btnImportEarnings' can not be created

This error occurs even if I'm logged into the computer as an administrator. I don't get this error on other computers.

I'm aware of the Office update in December 2014 that broke macros in office. I've run the "FixIt" on the computer in question, but that did not fix the issue.
0
Comment
Question by:Perfishent
[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
  • 5
  • 5
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40619239
Does it work if you replace the button with either a Forms button, rather than ActiveX, or a Shape and assign a macro to it? If so, it's almost certainly related to the December updates.
0
 

Author Comment

by:Perfishent
ID: 40619262
There's not a macro to assign, because the button runs Visual Basic code. I think I have to use an ActiveX button to run VBA code.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40619305
Macros are written in VBA code. You might need to amend the code to make it run in a normal module - what is the code?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Perfishent
ID: 40619326
I'm connecting to a Pervasive SQL database to get data for some calculations. Here's the code:

Option Explicit

Dim beginDate as String
Dim endDate as String
Dim sql As String
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim theCompany As Integer
Dim strConn As String
Dim payDate As String
Dim apexCo As Integer
Dim caseNum As String
Dim caseSSN As Long
Dim caseNo As String
Dim strSQL As String
Dim beginDt As String
Dim endDt As String
Dim lastRow As Integer
Dim lastCol As Integer
Dim curYr As Integer
Dim i As Integer

Private Function getGross()

    sql = ""
    curYr = Year(Date)

    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    lastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
   
    For i = 1 To lastRow
   
        If UCase(Cells(i, 1)) = "Y" And Cells(i, 2) <> "" And Cells(i, 3) <> "" And Cells(i, 4) <> "" And Cells(i, 4) > 10 And Cells(i, 4) < 20 Then
   
            apexCo = Cells(i, 4)
            caseSSN = Cells(i, 7)
            caseNum = Cells(i, 8)
            beginDt = Format(Cells(i, 2), "yyyy-mm-dd")
            endDt = Format(Cells(i, 3), "yyyy-mm-dd")
            Cells(i, 1) = "N"
       
            ' Get gross pay that does not include non-taxable items (tax_type 4)
            strSQL = "SELECT m.ss_no, SUM(e.pay_amt) AS pay_amt" & _
                " FROM APEX" & apexCo & curYr & ".pr_earn e INNER JOIN APEX" & apexCo & curYr & ".cl_ptype p ON e.pay_code = p.code and e.loc_no = p.loc_no" & _
                " INNER JOIN APEX" & apexCo & curYr & ".pr_mast m ON m.loc_no = e.loc_no and m.emp_no = e.emp_no and m.ss_no = " & caseSSN & _
                " INNER JOIN APEX" & apexCo & curYr & ".pr_ptype pt on pt.code = e.pay_code" & _
                " WHERE pt.yn_6 <> 'N' and e.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "'" & _
                " GROUP BY m.ss_no"
               
                Debug.Print strSQL
                       
            strConn = "Driver={Pervasive ODBC Client Interface};ServerName=xxxxxx.1583;ServerDSN=APEX" & _
                        CStr(apexCo) & CStr(curYr) & ";UID=xxxxx;PWD=xxxxxx;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;"
            conn.Open strConn
            Set cmd.ActiveConnection = conn
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
   
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
                If rs!pay_amt > 0 Then
                    Cells(i, 31) = Round(rs!pay_amt, 2)
                End If
            End If
           
            'TAXES
            strSQL = "SELECT m.emp_no, m.loc_no, " & _
            " sum(i.amt_12) as Fed_Tax, sum(i.fica_5) as Soc_Sec, sum(i.medc_5) as Medc, ifnull(sum(i.amt_13),0) + ifnull(sum(i.amt_20),0) as State_Tax," & _
            " MAX(i.per_start) as per_start, MAX(i.per_end) as per_end" & _
            " FROM APEX" & apexCo & curYr & ".pr_mast m" & _
            " INNER JOIN APEX" & apexCo & curYr & ".pr_inp i ON i.emp_no = m.emp_no and i.loc_no = m.loc_no" & _
            " LEFT JOIN APEX" & apexCo & curYr & ".pr_cded cd ON cd.item_no = i.item_no and cd.ref_no = i.ref_no and cd.summ_no = i.summ_no" & _
            " WHERE m.ss_no = " & caseSSN & _
            " AND i.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "'" & _
            " GROUP BY m.emp_no, m.loc_no"
           
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
   
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
'                Cells(i, 27) = Round(rs!per_start, 2)
'                Cells(i, 28) = Round(rs!per_end, 2)
'                Cells(i, 30) = Round(rs!fed_tax, 2)
'                Cells(i, 31) = Round(rs!soc_sec, 2)
'                Cells(i, 32) = Round(rs!medc, 2)
'                Cells(i, 33) = Round(rs!state_tax, 2)
               
                Cells(i, 29) = Round(rs!per_start, 2)
                Cells(i, 30) = Round(rs!per_end, 2)
                Cells(i, 32) = Round(rs!fed_tax, 2)
                Cells(i, 33) = Round(rs!soc_sec, 2)
                Cells(i, 34) = Round(rs!medc, 2)
                Cells(i, 35) = Round(rs!state_tax, 2)
            End If
           
            'OTHER DEDUCTIONS
            strSQL = "SELECT SUM(cd.ee_amt) as other_ded, MAX(md.descrip) as Descrip FROM APEX" & apexCo & curYr & ".pr_cded cd INNER JOIN APEX" & apexCo & curYr & ".pr_mast m on m.loc_no = cd.loc_no and m.emp_no = cd.emp_no" & _
            " and m.ss_no = " & caseSSN & _
            " INNER JOIN pr_mded md ON md.code = cd.code and md.plan = cd.plan WHERE cd.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "' and cd.code BETWEEN 100 AND 144"
       
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
           
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
'                Cells(i, 39) = Round(rs!other_ded, 2)
'                Cells(i, 40) = Trim(rs!descrip)
               
                Cells(i, 41) = Round(rs!other_ded, 2)
                Cells(i, 42) = Trim(rs!descrip)
            End If
            rs.Close
            conn.Close
               
        End If

    Next i
   
    MsgBox "Update complete."

End Function

Private Sub btnImportEarnings_Click()
    Call getGross
End Sub
0
 

Author Comment

by:Perfishent
ID: 40619884
The bottom line is that there's something different about this one computer that keeps the program from running. And the difference is not specific to any one user profile. Strangely, other macro-enabled (vba coded) Excel spreadsheets will run on this computer.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40620754
You should be able to just move all that code to a normal module, make the btnImportEarnings_Click() routine public and assign it to a forms button or shape.
0
 

Author Comment

by:Perfishent
ID: 40628524
My point is that I shouldn't have to change the code in any way, because the code runs just fine on most computers. It's just this one computer where it won't run. I'm looking for a setting or a permission somewhere that is keeping the code from running on the one computer. Maybe I should just re-install Office and see if that fixes it.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40628586
I agree, but I think you've run into this issue: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/

Until there's a proper patch for the problem (and in fact even then), I think it is better to avoid ActiveX controls on worksheets if at all possible.
0
 

Author Closing Comment

by:Perfishent
ID: 40644735
This is more of a workaround than a true solution. The Active X control works on all of the computers but one.  Fixing Active X on the one computer would be a true solution.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40644806
How exactly would you expect me to repair a buggy Microsoft patch for you??
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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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