Failing To Reveal A Hidden Workbook From VBA From A Second Workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Consider this code:
Rich (BB code):
Sub start_2()
    Dim mergeRange As Range
    Dim rng_src As Range, rng_dest As Range
    Dim wb_rmr As Workbook
    Dim ws_sand As Worksheet
    
    declaration
    'Stop
    With Workbooks("permit_info.xlsm").Worksheets("sheet1")
        .Range("I1, K1").Value = ""
    End With
    mbevents = False
    With ws_form
        If .ProtectContents = True Then .Unprotect
        .Range("I2") = ""
        If recall = 0 Then
            With .Range("E2")
                .Value = ""
                .Interior.Color = RGB(221, 235, 247)
                '.Borders.Color = RGB(48, 84, 150)
            End With
        End If
        'recall = 0
        With .Range("E3")
            .Value = ""
            .Interior.Color = RGB(189, 215, 238)
            '.Borders.Color = RGB(48, 84, 150)
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("W3, K15, V15")
            .Value = ""
            .Interior.Color = RGB(189, 215, 238)
            '.Borders.Color = RGB(48, 84, 150)
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("C6,F6,K6,R6,V6")
            .Value = ""
            .Interior.Color = RGB(189, 215, 238)
            '.Borders.Color = RGB(48, 84, 150)
        End With
        With .Range("C6")
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("F6")
            .Validation.Delete
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("K6")
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("R6")
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("V6")
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("K15")
            .Font.Color = vbBlack
            .Font.Bold = False
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        With .Range("V15")
            .Font.Color = vbBlack
            .Font.Bold = False
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = True
            End If
        End With
        '.Range("E2").Select
        
        .Shapes("b_sh1u").Visible = False  'enable edit
        .Shapes("g_sh1u").Visible = True 'disable edit
        .Shapes("b_sh2u").Visible = False
        .Shapes("g_sh2u").Visible = True
        .Shapes("gn_sh2_submit").Visible = False
        .Shapes("g_sh2_submit").Visible = True
        
        If recall = 0 Then
            Set rng_src = wb_permit.Worksheets("Blocks").Range("A25:T29")
            Set rng_dest = ws_form.Range("G8")
            rng_src.Copy _
                Destination:=rng_dest
        Else
            Set rng_src = wb_permit.Worksheets("Blocks").Range("A25:T29")
            Set rng_dest = wb_permit.ws_form.Range("G8")
            rng_src.Copy _
                Destination:=rng_dest
            .Protect
            recall = 0
            .Protect
            Exit Sub
        End If
        .Protect
    End With
'Stop
    wb_permit.Windows(1).Visible = False
    mbevents = True
    If wb_permit.Worksheets("sheet1").Range("K1") = "dp" Then
        pn = wb_permit.Worksheets("sheet1").Range("I1")
        ws_form.Activate
        Call Worksheets("FORM").ReadingView
        MsgBox pn
        wb_permit.Windows(1).Visible = True
        ws_form.Range("E2").Select
    End If
End Sub

When this routine is launched, it basically resets the contents of a worksheet to it's default state in terms of values and formats and then hides it.
This worksheet and code resides in workbook 2 (wb_permit), the second of two open workbooks (wb_rmr).
It is user activity in workbook 1 (wb_rmr) that assigns the value of "dp" to range wb_permit.Worksheets("sheet1").Range("K1") and a value to wb_permit.Worksheets("sheet1").Range("I1") to refer to variablepn. . User initiated code in wb_rmr also launches this code. 
This code, when launched by the user from workbook 1 (wb_rmr) reaches the content in blue. A value of "dp" residing in range wb_permit.Worksheets("sheet1").Range("K1") triggers the remainder of the code.
The code in blue is intended to produce the following results ...
     - activate worksheet "form" in wb_permit (hidden)
     - call a procedure residing in wb_permit worksheets "FORM" which modifies the window - wb_permit worksheets("FORM") -  resizing it, removing static features like toolbars, tabs, ribbons, scrolllbars etc)
     - reveal (unhide) the modified worksheets("FORM") on top of wb_rmr behind it

I think I have the ordering of events wrong. When this code executes, the wb_permit.worksheet("FORM") window is not revealed.

Is anyone able to suggest a solution? If I have failed to provide adequate information, or the right information, to help support a solution, please ask for clarification. I'm unsure what information is relevant.
 
You can access data in a closed book.
It's a little more awkward with VBA than with Excel.

This sort of way.
Yout code needs to put a formula into a cell and then copy the data returned by the formula from the cell back into itself.
VBA Code:
a = "='c:\My Documents\My test stuff\[Book1.xlsm]Sheet1'!"
Range("A1").Formula = a & Cells(1, 1).Address
Range("A1").Value = Range("A1").Value
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Thats a great tidbit of learning you provided there. Thanks. I wasn't aware I could do that, but surprise to me!

Anyway, I have reworked my code to avoid having to call macros of one workbook from another. I think what was happening was I was duplicating a lot of the code which wasn't necessary. It's streamlined now. I put the workbook unhiding and workbook formatting (ReadingView) in a module in wb1 and referenced wb2 in that code for which to apply the formatting to. No need to have to have that code in workbook.
 
Upvote 0
Sometimes, just discussing a problem, clears the mind and shows the way.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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