CODEWITHSUNDEEP
excelexcel-2016vba
Weston E
Weston E

Reputation: 133

Excel 2016: Active worksheet is not the visible one

I have an Excel macro that creates another workbook for the user to modify. When I try to enter data in a cell in the new workbook, however, I get the error "The cell or chart you're trying to change is on a protected sheet." The sheet in the macro workbook is, in fact, protected, but that sheet is not visible at the time I get the error. When I try to close the visible, newly created workbook, it’s the macro workbook that closes. The last thing my macro does is activate the sheet in the new workbook. What do I have to do to make that work? Having the macro close its own workbook solves the problem, of course, but that’s not what I want to do, since the user needs another macro in the same workbook to process the changes on the new sheet.

The program has over 6000 lines of code (so far), but here is one of the routines that causes the problem.

Private Sub z3_BudgetPrepUpd()
    'Build a new workbook initialized to let the user modify data
    'for an existing fiscal-quarter budget.

    'When this routine executes,
    '     UserForm1 is still active.

    Dim strTracer As String             'error-handling tracer for this subroutine
    Dim strFyrQtr As String

    On Error GoTo abend
    If blnAbort Then Exit Sub
    If blnAbortGlobal Then Exit Sub

    'Find out which ListBox item the user selected:
    If UserForm1.ListBox1.ListCount < 1 Then GoTo aa9000     'ListBox is empty
    If UserForm1.ListBox1.ListIndex < 0 Then                             'No item selected
        strMsgTitle = udtPrm.msgTtl
        strMsgPrompt = "Please select a fiscal quarter to update."
        Call z0_MsgBox
        GoTo aa9000
    End If
    strFyrQtr = UserForm1.ListBox1.Value                                     'Selected item in ListBox

    'Close UserForm1:
    UserForm1.Hide
    ThisWorkbook.Sheets(c_WKS_WELCOME).Activate

    'Build the udtBgt() array with data for the specified quarter:
    lngBgtHiNdx = -1
    Call zz_GetBudgetForQtr(strFyrQtr)
    If blnAbort Then GoTo aa9000

    'Build a new workbook for the user to update budget amounts:
    Workbooks.Add
    Set wkbNewBook = ActiveWorkbook

    'Save the names of the default worksheets
    'so we can delete them later:
    strDfltSheets() = z0_SheetNames(wkbNewBook)

    'Build a worksheet with data from the udtBgt() array:
    Call z3_BuildBudgetUpdSheet
    If blnAbort Then GoTo aa9000

    'Delete the default worksheets:
    Call z0_DeleteSheets(wkbNewBook, strDfltSheets())
    If blnAbort Then GoTo aa9000

    wkbNewBook.Sheets(c_WKS_IPT_BUDGET).Activate

    'Excel 2016 Bug:
    'We need to close ThisWorkbook to allow the user
    'to work with the book we just created:
    Application.DisplayAlerts = False
    ThisWorkbook.Close

aa9000:
    Exit Sub

abend:
    lngErr = Err.Number
    strErr = Err.Description
    blnAbort = True
    Application.Cursor = xlDefault    'no more hourglass
    strMsgTitle = "Program Error"
    strMsgPrompt = "The following error occurred:" & Chr(10) & Chr(10) & _
                                 "Error No. " & CStr(lngErr) & Chr(10) & _
                                 "Error Description: " & strErr & Chr(10) & _
                                 "Subroutine: z3_BudgetPrepUpd" & Chr(10) & _
                                 "Tracer: " & strTracer
    Call z0_MsgBox
    Resume aa9000
End Sub

Upvotes: 1

Views: 1048

Answers (2)

Weston E
Weston E

Reputation: 133

Thanks, everyone, for your interest and suggestions. I have solved the problem by redesigning the application without UserForms or external workbooks. Office 2016 has many issues, and perhaps this is one of them. In any case, I have no more time to research it.

Upvotes: 0

S Meaden
S Meaden

Reputation: 8270

You use ThisWorkbook which I agree with. You use ActiveWorkbook which I hardly ever use.

I'd recommend using a variable to store reference to workbooks other than that which houses your code. So use 

Dim wb As Excel.Workbook
Set wb = Application.Workbooks.Open("c:\test.xlsm") 'for opening

'* or
Set wb = Application.Workbooks.Add 'for creating a new one

'* or
Set wb = Application.Workbooks.Item("AlreadyOpen.xlsm") 'for referencing one already open

'* from hereon use wb instead of ActiveWorkbook
wb.Worksheets.Item(1).Visible = True

Upvotes: 1

Related Questions