×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hiding an Excel workbook loses focus
3

thread707-1820157

Hiding an Excel workbook loses focus

Hiding an Excel workbook loses focus

(OP)
Hi

I have some VBA code code in a workbook for summarising a CSV file report. Let's call this workbook "Reporter"

One of the first things it does is create a new workbook in to which all the data is imported, filtered, tidied/formatted and so on.

All works well but even with...

CODE --> VBA

Application.ScreenUpdating = False

...it still opens a workbook-less Excel window, whereas I'd rather it would show nothing at all until it creates the final spreadsheet.

After a little research I discovered...

CODE --> VBA

ActiveWindow.Visible = False

But then I found out that after running that line the Active window is now the original "Reporter" file, even if I try to force the new workbook to be the Active workbook:

CODE --> VBA

Set wbNewReport = Workbooks.Add
ActiveWindow.Visible = False
wbNewReport.Activate

What am I missing here?

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

Don't really on activewindow. Be explicit.

wbNewReport.visible = false

RE: Hiding an Excel workbook loses focus

(OP)

Quote (mintjulep)

Don't really on activewindow. Be explicit.

wbNewReport.visible = false

Excellent idea (and I'd like to be similarly more explicit all the way through my code), but in this instance it just gives

CODE --> Basic

Run-time Error '438':

Object doesn't support this property or Method

The variable is defined as

CODE --> Basic

Public wbNewReport As Workbook

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

Your wbNewReport does not have .Visible property:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Hiding an Excel workbook loses focus

Excel workbook may be viewed through one or more windows. Typically people use one window, so:
wbNewReport = Workbooks.Add
wbNewReport.Windows(1).Visible = False
You may set ScreenUpdating to False before, to avoid blinking.

combo

RE: Hiding an Excel workbook loses focus

(OP)

CODE --> Basic

Sub CallsReport_01_Add_AdvancedFilterSheet()
    Set wbNewReport = Workbooks.Add
    wbNewReport.Windows(1).Visible = False

    strNewReportName = wbNewReport.Name
    strNewSheetName = "AdvancedFilter"
    sub_RenameSheet strNewReportName, strNewSheetName

    ' Create the Column headers
    Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select
    i = 1
    For Each Column In arrColumns
        ActiveCell.Value = arrColumns(i)
        ActiveCell.Offset(0, 1).Select
        i = i + 1
    Next
    Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select
End Sub

Sub sub_RenameSheet(strNewReportName, strNewName)
    Dim CSVSheetName As String
    CSVSheetName = Workbooks(strNewReportName).ActiveSheet.Name
    Workbooks(strNewReportName).Sheets(CSVSheetName).Name = strNewName
End Sub

The line

CODE --> Basic

Workbooks(strNewReportName).Worksheets(strNewSheetName).Range("A1").Select
fails with Run-time error 1004 "Select method of Range class failed" though. Still learning how to be explicit about which Workbook/Worksheet to target!

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

There is no need to use, and you should avoid using .select.

All that .select does is manipulate the GUI, which since you have turned off with Application.ScreenUpdating = False has no point.

Just work directly with whatever .range or other object that you need to manipulate.

I've spent about 5 minutes staring at your code. All it seems to try to accomplish is add some headers in row A cells I think. It's far too complex for that. Also, the desired headers seem to be expected in an array named arrColumns, but that is nowhere to be found.

RE: Hiding an Excel workbook loses focus

In the first line of procedure you assign workbook to variable (Set wbNewReport = Workbooks.Add, I guess that somewhere in the module you have Dim wbNewReport As Workbook). Now it is shorter to refer directly to workbook:
wbNewReport.Worksheets(strNewSheetName). ...
instead of:
Workbooks(strNewReportName).Worksheets(strNewSheetName). ...

A new workbook has one or more worksheets, depending on user setting. It is safe to assume that you work with first worksheet, so you may declare Dim wsAdvancedFilter as Worksheet and next assign first sheet of newly created workbook: Set wsAdvancedFilter = wbNewReport.Worksheets(1). Now you can directly rename the worksheet or pass to procedure worksheet and new name:
Sub sub_RenameSheet(wsToRename As Worksheet, strNewName)
    wsToRename.Name = strNewName
End Sub
called by: sub_RenameSheet wsAdvancedFilter, strNewSheetName

I'm with mintjulep, avoid selecting and activating, from wsAdvancedFilter you have direct access to cells using .Range( ) or .Cells( ) for instance.

combo

RE: Hiding an Excel workbook loses focus

(OP)

Quote (mintjulep)


There is no need to use, and you should avoid using .select.

All that .select does is manipulate the GUI, which since you have turned off with Application.ScreenUpdating = False has no point.

Just work directly with whatever .range or other object that you need to manipulate.

Totally agree! It does feel like a very bad habit (born from starting with recorded macros).

Quote (mintjulep)


I've spent about 5 minutes staring at your code. All it seems to try to accomplish is add some headers in row A cells I think. It's far too complex for that. Also, the desired headers seem to be expected in an array named arrColumns, but that is nowhere to be found.

Top quality staring! big smile

...because you're right,, that's exactly what it does. I didn't bother pasting the array code, but for completeness, here it is:

CODE --> Basic

Public arrColumns(1 To 10) As String

CODE --> Basic

arrColumns(1) = "UPN"
    arrColumns(2) = "User Display Name"
    arrColumns(3) = "Caller ID"
    arrColumns(4) = "Call Direction"
    arrColumns(5) = "Number Type"
    arrColumns(6) = "Destination Dialed"
    arrColumns(7) = "Destination Number"
    arrColumns(8) = "Start Time"
    arrColumns(9) = "End Time"
    arrColumns(10) = "Duration Seconds"

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

(OP)

Quote (combo)

In the first line of procedure you assign workbook to variable (Set wbNewReport = Workbooks.Add, I guess that somewhere in the module you have Dim wbNewReport As Workbook). Now it is shorter to refer directly to workbook:
wbNewReport.Worksheets(strNewSheetName). ...
instead of:
Workbooks(strNewReportName).Worksheets(strNewSheetName). ...
Nice!

Thanks, both. I appreciate all your help (as I strive to learn how to VBA properly).

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

Try this.

CODE --> VBA

Public Sub Apply_Headers(Target_ws As Worksheet, Target_cell As String, Headers As Variant)

    Target_ws.Range(Target_cell).Resize(, UBound(Headers)).Value = Headers

End Sub

RE: Hiding an Excel workbook loses focus

Since you have hard-coded values for your header, the simple way would be to just:

CODE

Sub SimpleHeader()

Sheet1.Range("A1:J1").Value = Array("UPN", "User Display Name", "Caller ID", _
    "Call Direction", "Number Type", "Destination Dialed", "Destination Number", _
    "Start Time", "End Time", "Duration Seconds")

End Sub

But I do like mintjulep's approach:

CODE

Option Explicit

Public arrColumns(1 To 10) As String

Sub AddHeaders()

arrColumns(1) = "UPN"
arrColumns(2) = "User Display Name"
arrColumns(3) = "Caller ID"
arrColumns(4) = "Call Direction"
arrColumns(5) = "Number Type"
arrColumns(6) = "Destination Dialed"
arrColumns(7) = "Destination Number"
arrColumns(8) = "Start Time"
arrColumns(9) = "End Time"
arrColumns(10) = "Duration Seconds"

Call Apply_Headers(Sheet1, "A1", arrColumns)

End Sub

Public Sub Apply_Headers(Target_ws As Worksheet, Target_cell As String, aryHeaders() As String)

With Target_ws.Range(Target_cell).Resize(, UBound(aryHeaders))
    .Value = aryHeaders
    .EntireColumn.AutoFit   'just for kicks
End With

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Hiding an Excel workbook loses focus

(OP)

Quote (mintjulep)

There is no need to use, and you should avoid using .select

Is

CODE --> MicrosoftVisualBasic

.Activate
a better choice?

I'm asking because the line

CODE --> MicrosoftVisualBasic

wsAdvancedFilter.Range("A1").Activate
produces that "Run-time error '1004': Activate method of Range class failed" error when I have

CODE --> MicrosoftVisualBasic

wbNewReport.Windows(1).Visible = False
in operation...

Here's the full sub (and yes, I know I've not yet changed the FOR EACH code to write those column headers!):

CODE --> MicrosoftVisualBasic

Sub CallsReport_01_Add_AdvancedFilterSheet()
    Set wbNewReport = Workbooks.Add
    Set wsAdvancedFilter = wbNewReport.Worksheets(1)
    wbNewReport.Windows(1).Visible = False
    
    wsAdvancedFilter.Name = "AdvancedFilter"
    wsAdvancedFilter.Range("A1").Activate
    
    i = 1
    For Each Column In arrColumns
        ActiveCell.Value = arrColumns(i)
        ActiveCell.Offset(0, 1).Select
        i = i + 1
    Next
    wsAdvancedFilter.Range("A1").Activate
End Sub

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

RE: Hiding an Excel workbook loses focus

If you want to populate your Excel sheet, you do not need .Select or .Activate
You can be in Sheet1 and populate Sheet5 if you fully qualify objects in your code.

For example, let's populate any number of Worksheets in your Workbook with some numbers:

CODE

Option Explicit

Sub Andy()
Dim intS As Integer
Dim intR As Integer
Dim intNo As Integer

With ThisWorkbook
    For intS = 1 To .Sheets.Count
        With .Sheets(intS)
            For intR = 1 To 10
                .Cells(intR, 1).Value = intNo
                intNo = intNo + 1
            Next intR
        End With
    Next intS
End With

End Sub

.Select and .Activate are used when you record the Macro, but that happens while you click on anything when recording. Not really needed in 'real' VBA code. smile

IMO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Hiding an Excel workbook loses focus

Except of specific actions there is no need to select/activate cells you work with.
You can for instance directly:
With wsAdvancedFilter
    For i = 1 To 10
        .Cells(1,i) = arrColumns(i)
    Next
End With

or relative to specific address:
With wsAdvancedFilter.Range("A1")
    For i = 1 To 10
        .Offset(0,i - 1) = arrColumns(i)
    Next
End With

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close