Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Excel 2010: Macro-enabled workbook error

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
Perfishent
Asked:
Perfishent
  • 5
  • 5
1 Solution
 
Rory ArchibaldCommented:
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
 
PerfishentAuthor Commented:
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
 
Rory ArchibaldCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PerfishentAuthor Commented:
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
 
PerfishentAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
PerfishentAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
PerfishentAuthor Commented:
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
 
Rory ArchibaldCommented:
How exactly would you expect me to repair a buggy Microsoft patch for you??
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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