Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hiding an Excel workbook loses focus 3

Status
Not open for further replies.
JPJeffery

JPJeffery

Technical User
May 26, 2006
600
GB
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: 
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: 
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: 
Set wbNewReport = Workbooks.Add
ActiveWindow.Visible = False
wbNewReport.Activate

What am I missing here?

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Sort by date Sort by votes
Don't really on activewindow. Be explicit.

wbNewReport.visible = false
 
Upvote 0 Downvote
mintjulep said:
Don't really on activewindow. Be explicit.

wbNewReport.visible = false
Click to expand...

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: 
Run-time Error '438':

Object doesn't support this property or Method

The variable is defined as
Code: 
Public wbNewReport As Workbook

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Upvote 0 Downvote
Your [tt]wbNewReport[/tt] does not have [tt].Visible[/tt] property:

visible_rupyz9.png


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Excel workbook may be viewed through one or more windows. Typically people use one window, so:
[pre]wbNewReport = Workbooks.Add
wbNewReport.Windows(1).Visible = False[/pre]
You may set ScreenUpdating to False before, to avoid blinking.

combo
 
Upvote 0 Downvote
Code: 
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: 
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
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
In the first line of procedure you assign workbook to variable ([tt]Set wbNewReport = Workbooks.Add[/tt], I guess that somewhere in the module you have [tt]Dim wbNewReport As Workbook[/tt]). Now it is shorter to refer directly to workbook:
[tt]wbNewReport.Worksheets(strNewSheetName). ...[/tt]
instead of:
[tt]Workbooks(strNewReportName).Worksheets(strNewSheetName). ...[/tt]

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 [tt]Dim wsAdvancedFilter as Worksheet[/tt] and next assign first sheet of newly created workbook: [tt]Set wsAdvancedFilter = wbNewReport.Worksheets(1[/tt]). Now you can directly rename the worksheet or pass to procedure worksheet and new name:
[pre]Sub sub_RenameSheet(wsToRename As Worksheet, strNewName)
wsToRename.Name = strNewName
End Sub[/pre]
called by: sub_RenameSheet wsAdvancedFilter, strNewSheetName

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


combo
 
Upvote 0 Downvote
mintjulep said:
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.
Click to expand...

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

mintjulep said:
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.
Click to expand...

Top quality staring! :-D

...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: 
Public arrColumns(1 To 10) As String
Code: 
    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
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Upvote 0 Downvote
combo said:
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). ...
Click to expand...
Nice!

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

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Upvote 0 Downvote
Try this.

Code: 
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
 
Upvote 0 Downvote
Since you have hard-coded values for your header, the simple way would be to just:

Code: 
Sub SimpleHeader()

Sheet1.Range("[red]A1:J1[/red]").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   [green]'just for kicks[/green]
End With

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
mintjulep said:
There is no need to use, and you should avoid using .select
Click to expand...

Is
Code: 
.Activate
a better choice?

I'm asking because the line
Code: 
wsAdvancedFilter.Range("A1").Activate
produces that "Run-time error '1004': Activate method of Range class failed" error when I have
Code: 
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: 
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
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]
 
Upvote 0 Downvote
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. :)

IMO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Except of specific actions there is no need to select/activate cells you work with.
You can for instance directly:
[pre]With wsAdvancedFilter
For i = 1 To 10
.Cells(1,i) = arrColumns(i)
Next
End With[/pre]

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

combo
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
1K
huiettcm
huiettcm
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
897
SkipVought
S
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
remeng
  • Locked
  • Question
Multiple passwords for an excel workbook
Replies
2
Views
324
SkipVought
S
JAB_123
  • Locked
  • Question
I want to use an excel file for inp
Replies
3
Views
531
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top